What SQL statement is used to retrieve data from two or more tables?

Índice Show

Parameters or Arguments
SELECT statements
Learning more about SQL

This SQL tutorial explains how to use the SQL SELECT statement with syntax, examples and practice exercises.

The SQL SELECT statement is used to retrieve records from one or more tables in your SQL database. The records retrieved are known as a result set.

The syntax for the SELECT statement in SQL is:

SELECT expressions FROM tables [WHERE conditions] [ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

expressions The columns or calculations that you wish to retrieve. Use * if you wish to select all columns. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that must be met for the records to be selected. If no conditions are provided, then all records will be selected. ORDER BY expression Optional. The expression used to sort the records in the result set. If more than one expression is provided, the values should be comma separated. ASC Optional. ASC sorts the result set in ascending order by expression. This is the default behavior, if no modifier is provider. DESC Optional. DESC sorts the result set in descending order by expression.

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Let's look at an example that shows how to use the SQL SELECT statement to select all fields from a table.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

Now let's demonstrate how the SELECT statement works by selecting all columns from the customers table. Enter the following SELECT statement:

Try It SELECT * FROM customers WHERE favorite_website = 'techonthenet.com' ORDER BY last_name ASC;

There will be 2 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
9000	Johnson	Derek	techonthenet.com

In this example, we've used * to signify that we wish to view all fields from the customers table where the favorite_website is 'techonthenet.com'. The result set is sorted by last_name in ascending order.

You can also use the SQL SELECT statement to select individual fields from the table, as opposed to all fields from the table.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Now let's demonstrate how to use the SELECT statement to select individual columns from a table. Enter the following SELECT statement:

Try It SELECT supplier_name, city FROM suppliers WHERE supplier_id > 500 ORDER BY supplier_name ASC, city DESC;

4 records should be selected. These are the results that you should see:

supplier_name	city
Dole Food Company	Westlake Village
Electronic Arts	Redwood City
Flowers Foods	Thomasville
SC Johnson	Racine

This example would return only the supplier_name and city fields from the suppliers table where the supplier_id value is greater than 500. The results are sorted by supplier_name in ascending order and then city in descending order.

You can also use the SQL SELECT statement to retrieve fields from multiple tables.

In this example, we have a table called orders with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20
5	NULL	2016/05/01

And a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

Now let's select columns from both the orders and customers tables. Enter the following SELECT statement:

Try It SELECT orders.order_id, customers.last_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.order_id <> 1 ORDER BY orders.order_id;

There will be 3 records selected. These are the results that you should see:

order_id	last_name
2	Smith
3	Anderson
4	Jackson

This SELECT example joins two tables to gives us a result set that displays the order_id from the orders table and the last_name from the customers table. Each time we use a column in the SELECT statement, we prefix the column with the table name (for example, orders.order_id) in case there is any ambiguity about which table the column belongs to.

If you wanted to select all fields from the orders table and then the last_name field from the customers table, you enter the following SELECT statement:

Try It SELECT orders.*, customers.last_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.order_id <> 1 ORDER BY orders.order_id;

There will be 3 records selected. These are the results that you should see:

order_id	customer_id	order_date	last_name
2	5000	2016/04/18	Smith
3	8000	2016/04/19	Anderson
4	4000	2016/04/20	Jackson

In this example, we use orders.* to signify that we want to select all fields from the orders table and then we select the last_name field from the customers table.

If you want to test your skills using the SQL SELECT statement, try some of our practice exercises.

These exercises allow you to try out your skills with the SELECT statement. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer. Give it a try!

Go to Practice Exercises

Overview
SELECT statements
Examples
Learning more about SQL

Structured Query Language (SQL) is a specialized language for updating, deleting, and requesting information from databases. SQL is an ANSI and ISO standard, and is the de facto standard database query language. A variety of established database products support SQL, including products from Oracle and Microsoft SQL Server. It is widely used in both industry and academia, often for enormous, complex databases.

In a distributed database system, a program often referred to as the database's "back end" runs constantly on a server, interpreting data files on the server as a standard relational database. Programs on client computers allow users to manipulate that data, using tables, columns, rows, and fields. To do this, client programs send SQL statements to the server. The server then processes these statements and returns result sets to the client program.

SELECT statements

An SQL SELECT statement retrieves records from a database table according to clauses (for example, FROM and WHERE) that specify criteria. The syntax is:

SELECT column1, column2 FROM table1, table2 WHERE column2='value';

In the above SQL statement:

The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. To retrieve all columns, use the wild card * (an asterisk).
The FROM clause specifies one or more tables to be queried. Use a comma and space between table names when specifying multiple tables.
The WHERE clause selects only the rows in which the specified column contains the specified value. The value is enclosed in single quotes (for example, WHERE last_name='Vader').
The semicolon (;) is the statement terminator. Technically, if you're sending only one statement to the back end, you don't need the statement terminator; if you're sending more than one, you need it. It's best practice to include it.

Examples

Following are examples of SQL SELECT statements:

To select all columns from a table (Customers) for rows where the Last_Name column has Smith for its value, you would send this SELECT statement to the server back end: SELECT * FROM Customers WHERE Last_Name='Smith';
The server back end would reply with a result set similar to this:
+---------+-----------+------------+ | Cust_No | Last_Name | First_Name | +---------+-----------+------------+ | 1001 | Smith | John | | 2039 | Smith | David | | 2098 | Smith | Matthew | +---------+-----------+------------+ 3 rows in set (0.05 sec)
To return only the Cust_No and First_Name columns, based on the same criteria as above, use this statement: SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';
The subsequent result set might look like:
+---------+------------+ | Cust_No | First_Name | +---------+------------+ | 1001 | John | | 2039 | David | | 2098 | Matthew | +---------+------------+ 3 rows in set (0.05 sec)

To make a WHERE clause find inexact matches, add the pattern-matching operator LIKE. The LIKE operator uses the % (percent symbol) wild card to match zero or more characters, and the underscore ( _) wild card to match exactly one character. For example:

To select the First_Name and Nickname columns from the Friends table for rows in which the Nickname column contains the string "brain", use this statement: SELECT First_Name, Nickname FROM Friends WHERE Nickname LIKE '%brain%';
The subsequent result set might look like:
+------------+------------+ | First_Name | Nickname | +------------+------------+ | Ben | Brainiac | | Glen | Peabrain | | Steven | Nobrainer | +------------+------------+ 3 rows in set (0.03 sec)
To query the same table, retrieving all columns for rows in which the First_Name column's value begins with any letter and ends with "en", use this statement: SELECT * FROM Friends WHERE First_Name LIKE '_en';
The result set might look like:
+------------+------------+-----------+ | First_Name | Last_Name | Nickname | +------------+------------+-----------+ | Ben | Smith | Brainiac | | Jen | Peters | Sweetpea | +------------+------------+-----------+ 2 rows in set (0.03 sec)
If you used the % wild card instead (for example, '%en') in the example above, the result set might look like: +------------+------------+-----------+ | First_Name | Last_Name | Nickname | +------------+------------+-----------+ | Ben | Smith | Brainiac | | Glen | Jones | Peabrain | | Jen | Peters | Sweetpea | | Steven | Griffin | Nobrainer | +------------+------------+-----------+ 4 rows in set (0.05 sec)