MySQL SELECT statement

This article describes basic syntax of the SELECT statement, and how to use the SELECT statement to query data from the database.

The SELECT statement is used to retrieve data from one or more tables and is the most used statement in MySQL.

SELECT statement syntax

The following is the syntax of the SELECT statement:

SELECT columns_list
FROM table_name;

Here:

  • columns_list after SELECT keyword is one or more column. If you retrieve more columns, you must seperate them use a comma ,.
  • table_name after FROM Keyword is the table name from which to retrieve data.
  • Use SELECT * FROM table_name when you want to retrieve all the columns in the table.
  • Semicolon ; indicates the end of the statement, and it is optional. If there are two or more statements, you need to use a semicolon to separate them, in order to execute each statement individually MySQL.

It is a good coding practice to write SQL keywords in uppercase. However, SQL is not case sensitive. For example, the following statement is executed completely correctly:

select columns_list
from table_name;

You can filter the result of SELECT using WHERE clause.

You can sort the result of SELECT using ORDER BY clause.

You can limit the count of result using LIMIT clause.

SELECT statement example

We use the actor table in the the Sakila sample database as a demonstration. The following is the definition of the actor table:

+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id    | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   | MUL | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

We can see that the actor table has four columns: actor_id, first_name, last_name and last_update.

There are 200 rows in the actor table, as follows:

+----------+-------------+--------------+---------------------+
| actor_id | first_name  | last_name    | last_update         |
+----------+-------------+--------------+---------------------+
|        1 | PENELOPE    | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK        | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED          | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER    | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY      | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE       | NICHOLSON    | 2006-02-15 04:34:33 |
...
|      198 | MARY        | KEITEL       | 2006-02-15 04:34:33 |
|      199 | JULIA       | FAWCETT      | 2006-02-15 04:34:33 |
|      200 | THORA       | TEMPLE       | 2006-02-15 04:34:33 |
+----------+-------------+--------------+---------------------+

Querying one column

The following SELECT statement returns all the actor’s last name (last_name):

SELECT
    last_name
FROM
    actor;

The following is part of the output:

+--------------+
| last_name    |
+--------------+
| AKROYD       |
| AKROYD       |
| AKROYD       |
| ALLEN        |
| ALLEN        |
| ALLEN        |
| ASTAIRE      |
| BACALL       |
...

The SELECT statement’s result is called the result set, because it is a set of rows.

Querying multiple columns

The following SELECT statement returns all the actor’s first name (first_name) and last name (last_name):

SELECT
    first_name, last_name
FROM
    actor;

The following is part of the output:

+-------------+--------------+
| first_name  | last_name    |
+-------------+--------------+
| PENELOPE    | GUINESS      |
| NICK        | WAHLBERG     |
| ED          | CHASE        |
| JENNIFER    | DAVIS        |
| JOHNNY      | LOLLOBRIGIDA |
| BETTE       | NICHOLSON    |
| GRACE       | MOSTEL       |
| MATTHEW     | JOHANSSON    |
...

Querying all columns

Use the following SELECT statement to query all the fields in the cast of:

The following SELECT statement returns all the columns in the actor table:

SELECT
    *
FROM
    actor

The following is part of the output:

+----------+-------------+--------------+---------------------+
| actor_id | first_name  | last_name    | last_update         |
+----------+-------------+--------------+---------------------+
|        1 | PENELOPE    | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK        | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED          | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER    | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY      | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE       | NICHOLSON    | 2006-02-15 04:34:33 |
...
|      198 | MARY        | KEITEL       | 2006-02-15 04:34:33 |
|      199 | JULIA       | FAWCETT      | 2006-02-15 04:34:33 |
|      200 | THORA       | TEMPLE       | 2006-02-15 04:34:33 |
+----------+-------------+--------------+---------------------+

Of course, you can list the names of all columns, and use a comma , separated. as follows:

SELECT
    actor_id, first_name, last_name, last_update
FROM
    actor

You may want to ask, what is th difference between SELECT * and SELECT column_name? In general, the performance of the two is similar, but the following:

  1. Writing clear fields is easier to understand your SQL.
  2. Some large fields are not suitable for direct query.
  3. SELECT * is most used in command line or test scenarios.

SELECT without FROM

In MySQL, you can omit the FROM clause in some cases . The syntax is as follows:

SELECT expression_list

For example:

  • Query system time

    SELECT NOW();

    +---------------------+
| NOW()               |
+---------------------+
| 2021-09-07 22:45:33 |
+---------------------+
1 row in set (0.00 sec)

  • Math Calculations

    SELECT 1+2;

    +-----+
| 1+2 |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

dual table

In the above examples, you use the SELECT statement without FROM. If you want to add FROM clause, you can use the dummy table dual. For example:

SELECT NOW() FROM dual;
SELECT 1+2 FROM dual;

The results are exactly the same as the example above.

Conclusion

The article described basic syntax of the SELECT statement, and how to use the SELECT statement to query data from the database. The main points of the SELECT statement are as follows:

  • The SELECT statement is used to retrieve data from one or more tables.
  • The SELECT keyword is followed by names of the columns, and multiple columns are separated by commas.
  • The FROM keyword is followed by the name of table which you want to retrieve data from.
  • You can select all the columns of a table using SELECT *.
  • The expression can be followed SELECT directly. In this case, FROM can be omitted.
  • The dual table is a dummy table. You can make no FROM statement to meet the structure of the SELECT ... FROM statement.