SQL: SELECT Statement
This SQL tutorial explains how to use the SQL SELECT statement with syntax, examples and practice exercises.
Description
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.
Syntax
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.
DDL/DML for Examples
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!
Example - Select All Fields from a Table
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 ItSELECT * 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.
Example - Select Individual Fields from a Table
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 | 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 ItSELECT 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.
Example - Select Individual Fields From Multiple Tables
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 ItSELECT 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 ItSELECT 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.
Practice Exercises
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!
Advertisements