Retrieving Records From a Table With Queries in MySQL

Before diving into this lesson and the following ones, you will have to perform some basic setup on your database server.

💡 Tip: Once logged in, you can directly copy the following SQL statements, paste them into the MySQL client interface, and press the ENTER key to execute them.

1. Log in to the MySQL client the root account:

   $ mysql -u root
   

2. Once logged in, create a new database named learnbackend:

   CREATE DATABASE learnbackend;
   

3. Select the database:

   USE learnbackend;
   

4. Create a new table named employees:

   CREATE TABLE employees (
     id INT AUTO_INCREMENT PRIMARY KEY,
     first_name VARCHAR(255) NOT NULL,
     last_name VARCHAR(255) NOT NULL,
     department ENUM('Sales', 'Engineering', 'HR', 'Marketing') NOT NULL,
     hire_date DATE NOT NULL,
     salary INT NOT NULL
   );
   

5. Insert records into the employees table:

   INSERT INTO employees (id, first_name, last_name, department, hire_date, salary) VALUES
   (1, 'John', 'Doe', 'Sales', '2022-01-15', 50000),
   (2, 'Jane', 'Smith', 'Engineering', '2020-03-22', 70000),
   (3, 'Alice', 'Johnson', 'HR', '2019-07-30', 45000),
   (4, 'Bob', 'Brown', 'Sales', '2018-11-10', 52000),
   (5, 'Charlie', 'Davis', 'Engineering', '2021-06-18', 75000),
   (6, 'Diana', 'Miller', 'Marketing', '2017-05-14', 60000),
   (7, 'Eve', 'Wilson', 'HR', '2023-02-25', 48000),
   (8, 'Frank', 'Moore', 'Marketing', '2020-09-30', 62000),
   (9, 'Grace', 'Taylor', 'Engineering', '2021-04-02', 77000),
   (10, 'Hank', 'Anderson', 'Sales', '2019-12-12', 55000);
   

Note: To output the list of existing tables in a database, you can use the SHOW TABLES statement.

Retrieving rows based on columns

To retrieve rows from a single table, you can use the SELECT keyword combined with the FROM clause:

SELECT column_name, ...
FROM table_name;

Where:

• column_name, ... is a list of comma-separated column names.
• table_name is the name of the table to retrieve the columns from.

Alternatively, to retrieve all the columns from a table at once, you can use the * wildcard:

SELECT *
FROM table_name;

Example

This SQL statement will retrieve all the columns of all the rows in the employees table:

mysql> SELECT * FROM employees;
+----+------------+-----------+-------------+------------+--------+
| id | first_name | last_name | department  | hire_date  | salary |
+----+------------+-----------+-------------+------------+--------+
|  1 | John       | Doe       | Sales       | 2022-01-15 |  50000 |
|  2 | Jane       | Smith     | Engineering | 2020-03-22 |  70000 |
|  3 | Alice      | Johnson   | HR          | 2019-07-30 |  45000 |
|  4 | Bob        | Brown     | Sales       | 2018-11-10 |  52000 |
|  5 | Charlie    | Davis     | Engineering | 2021-06-18 |  75000 |
|  6 | Diana      | Miller    | Marketing   | 2017-05-14 |  60000 |
|  7 | Eve        | Wilson    | HR          | 2023-02-25 |  48000 |
|  8 | Frank      | Moore     | Marketing   | 2020-09-30 |  62000 |
|  9 | Grace      | Taylor    | Engineering | 2021-04-02 |  77000 |
| 10 | Hank       | Anderson  | Sales       | 2019-12-12 |  55000 |
+----+------------+-----------+-------------+------------+--------+
10 rows in set (0.00 sec)

---

This SQL statement will only retrieve the last_name and department columns of all the rows in the employees table:

mysql> SELECT last_name, department FROM employees;
+-----------+-------------+
| last_name | department  |
+-----------+-------------+
| Doe       | Sales       |
| Smith     | Engineering |
| Johnson   | HR          |
| Brown     | Sales       |
| Davis     | Engineering |
| Miller    | Marketing   |
| Wilson    | HR          |
| Moore     | Marketing   |
| Taylor    | Engineering |
| Anderson  | Sales       |
+-----------+-------------+
10 rows in set (0.00 sec)

Filtering results with conditions

In SQL, a condition is an expression used to filter the rows that should be included in the result set of a query.

It is usually composed of a column name, a comparison operator, and a value.

Upon execution of the query, the condition is logically evaluated for each row, and either returns the current row if it evaluates to true, or skips it if it evaluates to false or unknown.

To filter the results based on a condition, you can use the WHERE clause followed by the condition:

SELECT column_name, ...
FROM table_name
WHERE condition;

Example

This SQL statement will retrieve the last_name, department, and salary columns of all the rows in the employees table, where the value of the salary column is greater than or equal to 60000:

mysql> SELECT last_name, department, salary FROM employees WHERE salary >= 60000;
+-----------+-------------+--------+
| last_name | department  | salary |
+-----------+-------------+--------+
| Smith     | Engineering |  70000 |
| Davis     | Engineering |  75000 |
| Miller    | Marketing   |  60000 |
| Moore     | Marketing   |  62000 |
| Taylor    | Engineering |  77000 |
+-----------+-------------+--------+
5 rows in set (0.00 sec)

The AND operator

To combine two or more conditions, you can use the AND operator that requires all conditions to be true to include the row in the result set:

SELECT column_name, ...
FROM table_name
WHERE condition AND condition;

Example

This SQL statement will retrieve the last_name and hire_date columns of all the rows in the employees table, where the value of the department column is equal to the 'Engineering' string, and the value of the hire_date column is greater than or equal to the '2021-01-01' date:

mysql> SELECT last_name, hire_date FROM employees WHERE department = 'Engineering' AND hire_date >= '2021-01-01';
+-----------+------------+
| last_name | hire_date  |
+-----------+------------+
| Davis     | 2021-06-18 |
| Taylor    | 2021-04-02 |
+-----------+------------+
2 rows in set (0.00 sec)

The OR operator

To combine two or more conditions, you can use the OR operator that requires at least one condition to be true to include the row in the result set:

SELECT column_name, ...
FROM table_name
WHERE condition OR condition;

Example

This SQL statement will retrieve the last_name, department, and salary columns of all the rows in the employees table, where the value of the department column is equal to the 'Sales' or 'HR' string:

mysql> SELECT last_name, department, salary FROM employees WHERE department = 'Sales' OR department = 'HR';
+-----------+------------+--------+
| last_name | department | salary |
+-----------+------------+--------+
| Doe       | Sales      |  50000 |
| Johnson   | HR         |  45000 |
| Brown     | Sales      |  52000 |
| Wilson    | HR         |  48000 |
| Anderson  | Sales      |  55000 |
+-----------+------------+--------+
5 rows in set (0.00 sec)

Combining operators

The AND and OR operators can be combined together to form complex conditions:

SELECT column_name, ...
FROM table_name
WHERE condition OR condition AND condition;

Note: In SQL, the AND operator has higher precedence than the OR operator, which means that when used together, the AND operator is evaluated first before the OR operator.

However, to ensure clarity regarding the evaluation order, you can use parentheses to explicitly define the grouping and precedence in complex conditions:

SELECT column_name, ...
FROM table_name
WHERE (condition OR condition) AND condition;

Example

This SQL statement will retrieve all the columns of all the rows in the employees table, where the value of the department column is equal to the 'Sales', or the value of the department column is equal to the 'Marketing' and the value of the hire_date column is greater or equal to the '2021-01-01' date:

mysql> SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing' AND hire_date >= '2021-01-01';
+----+------------+-----------+------------+------------+--------+
| id | first_name | last_name | department | hire_date  | salary |
+----+------------+-----------+------------+------------+--------+
|  1 | John       | Doe       | Sales      | 2022-01-15 |  50000 |
|  4 | Bob        | Brown     | Sales      | 2018-11-10 |  52000 |
| 10 | Hank       | Anderson  | Sales      | 2019-12-12 |  55000 |
+----+------------+-----------+------------+------------+--------+
3 rows in set (0.00 sec)

Note that this SQL statement is equivalent to this one:

mysql> SELECT * FROM employees WHERE department = 'Sales' OR (department = 'Marketing' AND hire_date >= '2021-01-01');

---

This SQL statement will retrieve all the columns of all the rows in the employees table, where the value of the department column is either equal to the 'Sales' or 'Marketing' string, and the value of the hire_date column is greater or equal to the '2021-01-01' date:

mysql> SELECT * FROM employees WHERE (department = 'Sales' OR department = 'Marketing') AND hire_date >= '2021-01-01';
+----+------------+-----------+------------+------------+--------+
| id | first_name | last_name | department | hire_date  | salary |
+----+------------+-----------+------------+------------+--------+
|  1 | John       | Doe       | Sales      | 2022-01-15 |  50000 |
+----+------------+-----------+------------+------------+--------+
1 row in set (0.00 sec)

Retrieving rows based on multiple values

Retrieving rows in a list of values

To retrieve rows from a single table based on the value of a column in a list of values, you can use the IN operator:

SELECT column_name, ...
FROM table_name
WHERE column_name IN (value, ...);

Example

This SQL statement will retrieve the first_name, last_name, and department columns of all the rows in the employees table, where the value of the department column is in the list containing the 'Sales' and 'Marketing' strings:

mysql> SELECT first_name, last_name, department FROM employees WHERE department IN ('Sales', 'Marketing');
+------------+-----------+------------+
| first_name | last_name | department |
+------------+-----------+------------+
| John       | Doe       | Sales      |
| Bob        | Brown     | Sales      |
| Diana      | Miller    | Marketing  |
| Frank      | Moore     | Marketing  |
| Hank       | Anderson  | Sales      |
+------------+-----------+------------+
5 rows in set (0.00 sec)

Retrieving rows in a range of values

To retrieve rows from a single table based on the value of a column between a range of values, you can use the BETWEEN operator combined with the AND operator:

SELECT column_name, ...
FROM table_name
WHERE column_name BETWEEN start_value AND end_value;

Example

This SQL statement will retrieve the first_name, last_name, department, and hire_date columns of all the rows in the employees table, where the value of the hire_date column is between the '2019-01-01' and '2020-12-31' dates:

mysql> SELECT first_name, last_name, department, hire_date FROM employees WHERE hire_date BETWEEN '2019-01-01' AND '2020-12-31';
+------------+-----------+-------------+------------+
| first_name | last_name | department  | hire_date  |
+------------+-----------+-------------+------------+
| Jane       | Smith     | Engineering | 2020-03-22 |
| Alice      | Johnson   | HR          | 2019-07-30 |
| Frank      | Moore     | Marketing   | 2020-09-30 |
| Hank       | Anderson  | Sales       | 2019-12-12 |
+------------+-----------+-------------+------------+
4 rows in set (0.00 sec)

Filtering results based on patterns

To retrieve rows from a single table based on the value of a column that matches a pattern, you can use the LIKE and REGEXP operators.

Matching basing patterns

The LIKE operator allows you to match basic patterns using wildcard characters:

SELECT column_name, ...
FROM table_name
WHERE column_name LIKE 'pattern';

Where:

• _ matches a single character.
• % matches zero, one, or more characters.

Example

This SQL statement will retrieve the first_name and last_name columns of all the rows in the employees table, where the value of the last_name column starts with the letter M followed by zero or more characters:

mysql> SELECT first_name, last_name FROM employees WHERE last_name LIKE 'M%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Diana      | Miller    |
| Frank      | Moore     |
+------------+-----------+
2 rows in set (0.00 sec)

Matching regular expressions

The REGEXP (or RLIKE) operator allows you to match regular expressions using advanced wildcard characters:

SELECT column_name, ...
FROM table_name
WHERE column REGEXP 'pattern';

Where:

• ^ matches the pattern to the beginning of the value being tested.
• $ matches the pattern to the end of the value being tested.
• . matches any single character.
• [chars] matches any single character listed within the brackets.
• [char1–char2] matches any single character within the given range.
• | separates two string patterns and matches either one.
• {number} repeats the specified characters.

Example

This SQL statement will retrieve the id, first_name and last_name columns of all the rows in the employees table, where the value of the last_name column starts and ends with 5 characters:

mysql> SELECT id, first_name, last_name FROM employees WHERE last_name REGEXP '^.{5}$';
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  2 | Jane       | Smith     |
|  4 | Bob        | Brown     |
|  5 | Charlie    | Davis     |
|  8 | Frank      | Moore     |
+----+------------+-----------+
4 rows in set (0.00 sec)

Sorting results in lexicographic order

To retrieve rows from a single table based on the value of a column in ascending or descending order, you can use the ORDER BY clause:

SELECT column_name, ...
FROM table_name
ORDER BY column_name [ASC|DESC], ...;

Where:

• ASC is an optional keyword used to sort the specified column's value in ascending order (default).
• DESC is an optional keyword used to sort the specified column's value in descending order.

Note that you can also specify multiple columns at once:

SELECT column_name, ...
FROM table_name
ORDER BY column_name, ... [ASC|DESC], ...;

Example

This SQL statement will retrieve the id, last_name, and department columns of all the rows in the employees table, and sort the rows based on the last_name column in descending order:

mysql> SELECT id, last_name, department FROM employees ORDER BY last_name DESC;
+----+-----------+-------------+
| id | last_name | department  |
+----+-----------+-------------+
|  7 | Wilson    | HR          |
|  9 | Taylor    | Engineering |
|  2 | Smith     | Engineering |
|  8 | Moore     | Marketing   |
|  6 | Miller    | Marketing   |
|  3 | Johnson   | HR          |
|  1 | Doe       | Sales       |
|  5 | Davis     | Engineering |
|  4 | Brown     | Sales       |
| 10 | Anderson  | Sales       |
+----+-----------+-------------+
10 rows in set (0.00 sec)

Limiting the number of results

To limit the number of rows returned by the SELECT keyword, you can use the LIMIT clause:

SELECT column_name, ...
FROM table_name
LIMIT rows_number;

Where rows_number is the maximum number of rows to return.

To skip a certain number of rows before returning the result set, you can combine the LIMIT clause with the OFFSET clause:

SELECT column_name, ...
FROM table_name
LIMIT rows_number OFFSET offset_number;

Where offset_number is the number of rows to skip from the beginning of the result set.

This is particularly useful in scenarios where the result set might be too large or implementing pagination.

Example

Let's consider this SQL statement is used to retrieve the first_name, last_name, department, and salary columns of all the rows in the employees table, sort the rows based on the salary column in descending order, and limit the result set to 3 rows:

mysql> SELECT first_name, last_name, department, salary FROM employees ORDER BY salary DESC LIMIT 3;
+------------+-----------+-------------+--------+
| first_name | last_name | department  | salary |
+------------+-----------+-------------+--------+
| Grace      | Taylor    | Engineering |  77000 |
| Charlie    | Davis     | Engineering |  75000 |
| Jane       | Smith     | Engineering |  70000 |
+------------+-----------+-------------+--------+
3 rows in set (0.00 sec)

The IS NULL operator

In SQL, a NULL value represents missing or undefined data.

It is different from an empty string or a zero value, as NULL means no value, while an empty string is a value of zero length and zero is a numerical value.

To retrieve rows from a single table based on the NULL value of a column, you can combined the WHERE clause and the IS NULL operator:

SELECT column_name, ...
FROM table_name
WHERE column_name IS NULL;

Note: Standard comparison operators like = or != cannot be used to compare with NULL values. Instead, IS NULL and IS NOT NULL should be used.

Summary

Here's a summary of what you've learned in this lesson:

• The SELECT keyword combined with the FROM clause are used to retrieve rows from a table.
• The WHERE clause is used to filter rows based on a condition.
• The AND operator is used to combine multiple conditions where all conditions must be true.
• The OR operator is used to combine multiple conditions where at least one condition must be true.
• The () parentheses are used to specify the precedence in complex conditions.
• The IN operator is used to filter rows based on a list of values.
• The BETWEEN operator is used to filter rows based on a range of values.
• The LIKE operator is used to match basic patterns using wildcard characters.
• The REGEXP operator is used to match advanced patterns using regular expressions.
• The ORDER BY...ASC|DESC clause is used to sort rows by ascending or descending lexicographic order.
• The LIMIT clause is used to limit the number of rows.
• The LIMIT...OFFSET clause is used to limit the number of rows starting at a specific result offset.
• The IS NULL and IS NOT NULL operators are used to check whether the value of columns is NULL or not.