Read Prerequisites for this tutorial and practices if you haven't done so.

What you can do with SELECT statement

For rows, you can:

1. limit the number of rows returned in the result set.

2. retrieve the data that meets the criteria you have specified.

3. retrieve distinctive data.

4. sort the table data in the result set in descending or ascending order.

5. group the table data in the result set in a way that meets your application requirements.

6. summarize the table data in the result set to get statistical information.

7. retrieve data from two or more tables for related information.

For columns, you can:

1. specify which column(s) to retrieve.

2. rename a table column to a different name.

3. perform arithmetic operations or conversion operations on a column data.

4. retrieve data in a column based on conditions in one or more other columns in the same table.

5. retrieve data in a column based on conditions in one or more other columns in another table.

Using a simple SELECT statement

Here is what a simple SELECT statement looks like:

SELECT column1, column2, column3... FROM tablename

1. SELECT clause identifies which column's data to retrieve in the table.

2. FROM clause identifies which table to retrieve data from.

Note that SELECT and FROM can be called keyword or clause. In other words, keyword and clause can be used interchangeably. Putting two or more clauses together in a SQL forms a SQL statement.

Column names must not use MySQL reserved words. If you do, escape them by using backticks ``.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

Practice #1: select all columns, all rows

SELECT * 
FROM categories;

SELECT * 
FROM `categories`;

SELECT CategoryID, CategoryName, Description, Picture 
FROM categories;

SELECT CategoryID, CategoryName, Description, Picture 
FROM `categories`;

Four select statements are listed above. They retrieve all rows and all columns from categories table. Asterisk (*) means all columns. We can also explicitly list all column names (CategoryID, CategoryName, Description, Picture) in the statement. It's equivalent to using Asterisk. Back quotes are used to enclose a table name. It's not necessary to use back quotes around a table name unless the table name has space(s) in it.

Query result set - 8 rows returned:

Practice #2: select specific columns, all rows and add comments to the SQL query

-- Only select data from two columns
SELECT CategoryID, CategoryName
FROM categories;
 
-- Add comment after double dashes. Single line comment only.
 
# Or add comment after the hash character. Single line comment only.
 
/*
If you need to add comments in multiple lines or paragraphs, 
do it like this. 
 
Here comes another line for this comment block.
*/

This example selects data for all rows but only CategoryID and CategoryName column. Columns are displayed in the same order you list them in the SQL statement.

Query result set - 8 rows returned:

You can add comments to your MySQL code. There are three ways to add comments:

1. -- Here goes for single line comment.

2. # Here goes for single line comment

3. /* Here goes the comment for multiple lines or paragraphs. */

Some rules to follow when writing SELECT statement:

1. Keywords are not case-sensitive.

2. Keywords can't be splitted into different lines in the statement.

3. Table names and column names are not case-sensitive.

4. Space(s) is allowed in a table name but not allowed in column name.

5. To use a table name which has one or more spaces in it, enclose the table name in back quotes (e.g. `order details`). Using Table name with space(s) is a bad practice. Use underscore to separate words instead.

6. You can place a semi-colon at the end of the statement to indicate the end of current statement. This is useful in batch SQL processing. To execute a single SQL statement, semi-colon can be omitted when the entire SQL statement is highlighted in SQLyog.

7. You can use indentation and line breaks to make your SQL more readable. Normally, different clauses are put to different lines. In examples above, SELECT and FROM are put to two different lines.

Other tutorials in this category

1. Using Column Alias in SELECT Statement

2. Using Literal Character Strings

3. Perform Arithmetic Operations

4. Use WHERE Clause to Conditionally Select Rows

5. Sorting Data

6. Using LIMIT Clause to Constrain the Number of Rows Retrieved

7. Using Comparison Operators, Part I

8. Using Comparison Operators, Part II

9. Using LIKE Comparison Operator

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page