Home / SQL Statements / Select Statement / Question No: 214

The SELECT statement retrieves information from one or more tables. Retrievals tend to be the most common database operation, so it's important to understand how SELECT works and what you can do with it.

Here you will find general instructions on how to write SELECT statements and how to use the various parts of its syntax to get the results you want. A representative syntax for the SELECT statement is as follows:

SELECT values_to_display

    FROM table_name

    WHERE expression

    GROUP BY how_to_group

    HAVING expression

    ORDER BY how_to_sort

    LIMIT row_count;

The syntax shown above is simplified from the full SELECT syntax. The full SELECT syntax includes additional clauses like below:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

All clauses following the output column list (values_to_display) are optional. For example, you don't need to include a LIMIT clause when writing a SELECT statement. However, any clauses that you do include must be specified in the order shown.

Using SELECT statements for retrievals involving no more than a single table, but it's possible to retrieve records from more than one table in a single query. One way is by selecting records from one table after the other with multiple SELECT statements and concatenating the results using the UNION keyword.

Specifying Which Columns to Retrieve

To indicate what values to retrieve, name them following the SELECT keyword. In the simplest case, you specify an expression or list of expressions. MySQL evaluates each expression and returns its value. Expressions may return numbers, strings, temporal values, or NULL. The following SELECT statement retrieves a value of each of those types:

mysql> SELECT 2+2, REPEAT('x',5), DATE_ADD('2001-01-01',INTERVAL 7 DAY), 1/0;

+-----+---------------+---------------------------------------+------+

| 2+2 | REPEAT('x',5) | DATE_ADD('2001-01-01',INTERVAL 7 DAY) | 1/0  |

+-----+---------------+---------------------------------------+------+

|   4 | xxxxx         | 2001-01-08                            | NULL |

+-----+---------------+---------------------------------------+------+

SELECT can retrieve the values of expressions, as just shown, but it's more commonly used to retrieve columns from tables. To select information from a table, it's necessary to identify the table by adding a FROM table_name clause following the list of columns to retrieve.

SELECT productId, productName, productPirce FROM products;

MySQL returns a result set consisting of one row of output for each row in the table. (The term "result set" refers to the set of rows resulting from a SELECT statement.) If the table is empty, the result will be empty, too. An empty result set is perfectly legal. A syntactically valid SELECT that returns no rows is not considered erroneous.

For a SELECT operation that retrieves every column from a table, the shortcut * can be used to specify the output columns. The * stands for "all columns in the table," so for the City table, the following statements are equivalent:

SELECT productId, productName, productPirce FROM products;
SELECT * FROM products;

The * shorthand notation is clearly more convenient to type than a list of column names. However, you should understand when it is useful and when it isn't:

• If you want to retrieve all columns from a table and you don't care about the order in which they appear from left to right, * is appropriate. If you want to ensure that the columns appear left to right in a particular order, * cannot be used because it gives you no control over the order in which columns will appear. You should name the columns explicitly in the order you want to see them.
• If you don't want to retrieve all the columns from the table, you cannot use *. Instead, name the columns in the order they should appear.

You should not issue a SELECT * query to find out the current left-to-right display order for the columns in a table and then assume that they will always be displayed in that same order for future queries. The left-to-right column order produced by SELECT * retrievals depends implicitly on the internal structure of the table, which is determined by the order of the columns in the table definition. However, the table's internal structure can be changed with ALTER TABLE, so a SELECT * statement might return different results before and after an ALTER TABLE statement.

Renaming or Aliasing Retrieved Columns

mysql> SELECT productId AS 'Product ID', productName AS 'Product Name', productPirce AS 'Product Price' FROM products;

+------------+------------------+

| Product ID | Product Name     |

+------------+------------------+

|   1        |               12 |

+------------+------------------+

When you name a table in a SELECT statement, it's normally assumed to be a table in the default database. To specify a database explicitly in the SELECT statement itself, qualify the table name. That is, precede the table name with the database name and a period:

SELECT * FROM dbname.table_name;
SELECT table_name.column1, table_name.column2 FROM dbname.table_name;

Specifying Which Rows to Retrieve

To be more specific about which rows are of interest, include a WHERE clause that describes the characteristics of those rows. A WHERE clause can be as simple or complex as necessary to identify the rows that are relevant for your purposes. For example, to retrieve records from the Country table for those countries that achieved independence after the year 1990, it's sufficient to use a WHERE clause that specifies a single condition:

SELECT * FROM Country WHERE IndepYear > 1990;

More complex WHERE clauses specify multiple conditions, which may be combined using logical operators such as AND and OR. The following statement returns rows with Population values in the range from 1 million to 2 million:

SELECT * FROM Country WHERE Population >= 1000000 AND Population <= 2000000;

For testing values in a range, you can also use the BETWEEN operator:

SELECT * FROM Country WHERE Population BETWEEN 1000000 AND 2000000;