SQL SELECT Statement

SELECT is the SQL statement you will write more than any other. It reads rows from a table and returns only the columns you ask for. In this lesson every query runs on a real MySQL 8.4 server, and you see the table it reads, the result it returns and what happens at each step.

What does SELECT do?

A database stores data in tables: rows and columns, like a spreadsheet. SELECT asks the database a question and gets a result set back, which is also a table. It never changes the stored data, so it is always safe to run.

Every query in this lesson reads the employees table of our sample company database. Here are the columns we use:

SELECT syntax

SELECT column1, column2, ...FROM table_name;

• SELECT lists the columns you want, separated by commas.
• FROM names the table to read.
• The semicolon ; ends the statement. MySQL needs it when you send several statements together.

Select specific columns

Show each employee’s first name and job title:

SELECT first_name, job_titleFROM employees;

Select all columns with SELECT *

The asterisk * means “every column, in table order”:

SELECT * FROM employees;

Calculated columns and aliases

A column in the SELECT list can be an expression. MySQL calculates it for every row. Use AS to give it a readable name (an alias):

SELECT first_name,       salary,       salary * 12 AS yearly_salaryFROM employees;

You can also join text with CONCAT():

SELECT emp_id,       CONCAT(first_name, ' ', last_name) AS full_name,       cityFROM employees;

SELECT without a table

SELECT can also evaluate expressions without reading any table. That is handy for testing a function before you use it in a real query:

SELECT 7 * 6 AS answer, UPPER('sqatools') AS site, CURDATE() IS NOT NULL AS has_date;

The order MySQL runs a query

You write a query in one order but MySQL runs it in another. Knowing this explains most beginner errors, such as “unknown column” when you use an alias in WHERE.

Because SELECT (step 5) runs after WHERE (step 2), an alias created in SELECT does not exist yet inside WHERE:

SELECT first_name, salary * 12 AS yearly_salaryFROM employeesWHERE yearly_salary > 1000000;

ERROR 1054 (42S22): Unknown column 'yearly_salary' in 'where clause'

Common SELECT mistakes

• Missing comma between columns. SELECT first_name job_title FROM employees does not fail: MySQL reads job_title as an alias for first_name, so you get one column with the wrong header.
• Comma after the last column. SELECT first_name, FROM employees is a syntax error.
• Quoting column names with single quotes. SELECT 'salary' FROM employees returns the text salary on every row. Write column names without quotes; wrap a name in backticks only when it has spaces or is a reserved word (see SQL Syntax).
• Expecting a fixed row order. Without ORDER BY the database may return rows in any order. Never rely on the order you happen to see.

Here is the missing-comma trap in action:

SELECT first_name job_titleFROM employeesLIMIT 3;

SELECT last_name, salary / 1000 AS salary_kFROM employeesLIMIT 2;

SELECT emp_id, first_name, city AS officeFROM employees;

SELECT first_name, salary, salary * 0.10 AS bonusFROM employees;

SELECT FAQ