totn SQL

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.

subscribe button Subscribe

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!

Get DDL/DML

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 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.

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 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.

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 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.

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!

Go to Practice Exercises