MySQL SELECT

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

SELECT reads rows out of a table. This lesson covers its smallest useful form: name a table, name some columns, get rows back. Every example queries the Sakila database. If you have not loaded it yet, load Sakila first, then open the client with mysql -u root -p sakila so that sakila is the current database.

How SELECT works

SELECT COLUMN_LIST
FROM TABLE_NAME;

COLUMN_LIST is what you want in each row, usually column names separated by commas. TABLE_NAME is the table the rows come from. A statement ends at the semicolon. The client keeps reading until it finds one, so a line without a semicolon leaves you on a continuation prompt:

mysql> SELECT name FROM language
    -> ;
+----------+
| name     |
+----------+
| English  |
| Italian  |
| Japanese |
| Mandarin |
| French   |
| German   |
+----------+
6 rows in set (0.00 sec)

Examples

1) Select one column

Ask the film table for the title of each film and nothing else.

SELECT title FROM film;

+-----------------------------+
| title                       |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
| AFFAIR PREJUDICE            |
| AFRICAN EGG                 |
...
+-----------------------------+
1000 rows in set (0.01 sec)

One row comes back for every row in the table, 1,000 of them here. SELECT filters nothing on its own. Choosing which rows you get is the job of WHERE.

2) Select several columns

Separate the column names with commas. They come back in the order you asked for, which need not be the order they sit in the table.

SELECT title, release_year, rental_rate FROM film;

+-----------------------------+--------------+-------------+
| title                       | release_year | rental_rate |
+-----------------------------+--------------+-------------+
| ACADEMY DINOSAUR            |         2006 |        0.99 |
| ACE GOLDFINGER              |         2006 |        4.99 |
| ADAPTATION HOLES            |         2006 |        2.99 |
| AFFAIR PREJUDICE            |         2006 |        2.99 |
| AFRICAN EGG                 |         2006 |        2.99 |
...
+-----------------------------+--------------+-------------+
1000 rows in set (0.00 sec)

3) Select every column

An asterisk stands for every column in the table. The language table holds 6 rows, so this one prints a complete result.

SELECT * FROM language;

+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

SELECT * earns its place while you are exploring a table you do not know. Inside application code, name your columns. An asterisk sends every column over the wire whether the code reads it or not, and it changes shape underneath the code on the day somebody adds a column.

4) Compute a value

The select list does not have to name a column. Put an expression there and the result becomes a column of its own.

SELECT title, rental_rate, rental_rate * 1.1 FROM film;

+-----------------------------+-------------+-------------------+
| title                       | rental_rate | rental_rate * 1.1 |
+-----------------------------+-------------+-------------------+
| ACADEMY DINOSAUR            |        0.99 |             1.089 |
| ACE GOLDFINGER              |        4.99 |             5.489 |
| ADAPTATION HOLES            |        2.99 |             3.289 |
| AFFAIR PREJUDICE            |        2.99 |             3.289 |
| AFRICAN EGG                 |        2.99 |             3.289 |
...
+-----------------------------+-------------+-------------------+
1000 rows in set (0.00 sec)

The heading of that third column is the expression you typed, which is accurate and ugly. A column alias gives it a name of your own.

2 things that catch people out

Keywords ignore case. SELECT, select and SeLeCt all run.

select title from film;

+-----------------------------+
| title                       |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
...
+-----------------------------+
1000 rows in set (0.00 sec)

This tutorial writes keywords in upper case and everything else in lower case, as a convention. It exists so the shape of a long query reads at a glance. A misspelled column raises an error. MySQL does not guess what you meant, and it does not hand back an empty result.

SELECT titel FROM film;

ERROR 1054 (42S22): Unknown column 'titel' in 'field list'

field list is the server’s name for the select list, so that message locates the mistake as well as naming it. The same error ends differently when the bad name sits elsewhere in the statement, so read the tail of it before you go looking.

Summary

Use SELECT with a list of columns and FROM with a table to read rows.
List columns in whatever order you want them returned.
Reach for SELECT * while exploring, and name your columns in application code.
Put an expression in the select list to compute a value per row.
Expect ERROR 1054 when a column name is wrong.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How SELECT works

Examples

1) Select one column

2) Select several columns

3) Select every column

4) Compute a value

2 things that catch people out

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How SELECT works

​Examples

​1) Select one column

​2) Select several columns

​3) Select every column

​4) Compute a value

​2 things that catch people out

​Summary

​See also

How SELECT works

Examples

1) Select one column

2) Select several columns

3) Select every column

4) Compute a value

2 things that catch people out

Summary

See also