MySQL SELECT
Summary: in this tutorial, you will learn how to use the basic form of the MySQL SELECT
statement to query data from a table.
Introduction to MySQL SELECT statement
The SELECT
statement allows you to select data from one or more tables. To write a SELECT
statement in MySQL, you use this syntax:
SELECT select_list
FROM table_name;
Code language: SQL (Structured Query Language) (sql)
In this syntax:
- First, specify one or more columns from which you want to select data after the
SELECT
keyword. If theselect_list
has multiple columns, you need to separate them by a comma (,
). - Second, specify the name of the table from which you want to select data after the
FROM
keyword.
The semicolon (;
) is optional. It denotes the end of a statement. If you have two or more statements, you need to use the semicolon(;)
to separate them so that MySQL will execute each statement individually.
The SELECT
and FROM
are the keywords. By convention, you write the SQL keywords in uppercase. However, it’s not mandatory. Because SQL is case-insensitive, you can write the SQL statement in lowercase, uppercase, etc. For example:
select select_list
from table_name;
Code language: SQL (Structured Query Language) (sql)
When executing the SELECT
statement, MySQL evaluates the FROM
clause before the SELECT
clause:
MySQL SELECT statement examples
We’ll use the employees
table in the sample database for the following examples.
The employees
table has eight columns: employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, and jobTitle. The table also has many rows as shown in the following picture:
A) Using the MySQL SELECT statement to retrieve data from a single column example
The following example uses the SELECT
statement to select the last names of all employees:
SELECT lastName
FROM employees;
Code language: SQL (Structured Query Language) (sql)
Here’s the partial output:
+-----------+
| lastName |
+-----------+
| Murphy |
| Patterson |
| Firrelli |
| Patterson |
| Bondur |
| Bow |
| Jennings |
...
Code language: plaintext (plaintext)
The result of a SELECT
statement is called a result set as it’s a set of rows that results from the query.
B) Using the MySQL SELECT statement to query data from multiple columns example
The following example uses the SELECT
statement to get the first name, last name, and job title of employees:
SELECT
lastName,
firstName,
jobTitle
FROM
employees;
Code language: SQL (Structured Query Language) (sql)
Even though the employees
table has many columns, the SELECT
statement returns data of three columns lastName
, firstName
, and jobTitle
specified in the SELECT
clause:
+-----------+-----------+----------------------+
| lastname | firstname | jobtitle |
+-----------+-----------+----------------------+
| Murphy | Diane | President |
| Patterson | Mary | VP Sales |
| Firrelli | Jeff | VP Marketing |
| Patterson | William | Sales Manager (APAC) |
| Bondur | Gerard | Sale Manager (EMEA) |
...
Code language: plaintext (plaintext)
C) Using the MySQL SELECT statement to retrieve data from all columns example
If you want to select data from all the columns of the employees
table, you can specify all the column names in the SELECT
clause like this:
SELECT employeeNumber,
lastName,
firstName,
extension,
email,
officeCode,
reportsTo,
jobTitle
FROM employees;
Code language: SQL (Structured Query Language) (sql)
Alternatively, you can use the asterisk (*) which is the shorthand for all columns. For example:
SELECT *
FROM employees;
Code language: SQL (Structured Query Language) (sql)
The query returns data from all the columns of the employees
table.
The SELECT *
is often called “select star” or “select all” since it selects data from all columns of the table. In practice, you should use the SELECT *
for the ad-hoc queries only.
If you embed the SELECT
statement in the code such as PHP, Java, Python, Node.js, you should explicitly specify the columns from which you want to select data.
Summary
- Use the
SELECT
statement to select data from a table. - Use the
SELECT *
to select data from all columns of a table.