MySQL SELECT

Created with Sketch.

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 the select_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)

Try It Out

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.

Leave a Reply

Your email address will not be published. Required fields are marked *