Show This SQL tutorial explains how to use the SQL SELECT statement with syntax, examples and practice exercises. DescriptionThe 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. SubscribeSyntaxThe syntax for the SELECT statement in SQL is: SELECT expressions FROM tables [WHERE conditions] [ORDER BY expression [ ASC | DESC ]]; Parameters or ArgumentsexpressionsThe columns or calculations that you wish to retrieve. Use * if you wish to select all columns. tablesThe tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.WHERE conditionsOptional. 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 expressionOptional. 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. DESCOptional. DESC sorts the result set in descending order by expression.DDL/DML for ExamplesIf 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 TableLet'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:
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:
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 TableYou 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:
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:
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 TablesYou 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:
And a table called customers with the following data:
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:
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:
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 ExercisesIf 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 |