Retrieving Complex Data With Subqueries in MySQL

In MySQL, a subquery is a query nested within another query, whose intermediate result can be used by the main query as a condition or input.

When a subquery doesn't rely on the outer query, it is executed only once and is referred to as "non-correlated".

On the other hand, when a subquery references columns from the outer query, it is executed repeatedly, once for each row processed by the outer query, and is referred to as "correlated".

Subqueries in the WHERE clause

Subqueries in the WHERE clause allow you to compare the value of a column to the result of the subquery using comparison operators, such as = or >:

SELECT column_name, ...
FROM table_name
WHERE column_name operator (subquery);

Note: Subqueries can also be used in INSERT, UPDATE, and DELETE statements.

Example

This SQL statement will retrieve the first_name, last_name, and salary attributes of all the records in the employees table, where the value of the salary attribute is greater than its average value across all records:

mysql> SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Jane       | Smith     |  70000 |
| Charlie    | Davis     |  75000 |
| Diana      | Miller    |  60000 |
| Frank      | Moore     |  62000 |
| Grace      | Taylor    |  77000 |
+------------+-----------+--------+
5 rows in set (0.00 sec)

---

This SQL statements will retrieve the id, first_name, and last_name attributes of all the records in the employees table, whose id attribute matches the employee_id attribute in the contracts table, where the status attribute is equal to 'Completed':

mysql> SELECT id, first_name, last_name FROM employees WHERE id IN (SELECT employee_id FROM contracts WHERE status = 'Completed');
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | John       | Doe       |
|  3 | Alice      | Johnson   |
+----+------------+-----------+
2 rows in set (0.00 sec)

The ALL and ANY keywords

To compare the value of a column against a set of values returned by a subquery, you can use the ALL and ANY keywords:

SELECT column_name, ...
FROM table_name
WHERE condition operator ALL|ANY (subquery);

Where:

• ALL: The condition is true only if the value satisfies the comparison for every value in the result set of the subquery.
• ANY: The condition is true if the value satisfies the comparison for at least one value in the result set of the subquery.

Example

This SQL statement will retrieve first_name, last_name, and salary attributes of all the records in the employees table whose salary attribute is greater than the salary attribute of the records where the department attribute is equal to 'Sales':

mysql> SELECT first_name, last_name, salary FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales');
+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Jane       | Smith     |  70000 |
| Charlie    | Davis     |  75000 |
| Diana      | Miller    |  60000 |
| Frank      | Moore     |  62000 |
| Grace      | Taylor    |  77000 |
+------------+-----------+--------+
5 rows in set (0.00 sec)

---

This SQL statement will retrieve first_name, last_name, and salary attributes of all the records in the employees table whose salary attribute is greater than the salary attribute of any of the records where the department attribute is equal to 'Sales':

mysql> SELECT first_name, last_name, salary FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Sales');
+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Jane       | Smith     |  70000 |
| Bob        | Brown     |  52000 |
| Charlie    | Davis     |  75000 |
| Diana      | Miller    |  60000 |
| Frank      | Moore     |  62000 |
| Grace      | Taylor    |  77000 |
| Hank       | Anderson  |  55000 |
+------------+-----------+--------+
7 rows in set (0.00 sec)

The EXISTS keyword

To check whether a subquery returns any rows, you can use the EXISTS keyword:

SELECT column_name, ...
FROM table_name
WHERE [NOT] EXISTS (subquery);

Note that if the subquery returns at least one row, the EXISTS condition is satisfied.

Example

This SQL statement will retrieve the id, first_name, and last_name attributes of all the records in the employees table, where the id attribute matches an employee_id attribute in the contracts table:

mysql> SELECT id, first_name, last_name FROM employees WHERE EXISTS (SELECT 1 FROM contracts WHERE contracts.employee_id = employees.id);
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | John       | Doe       |
|  3 | Alice      | Johnson   |
|  5 | Charlie    | Davis     |
|  8 | Frank      | Moore     |
|  9 | Grace      | Taylor    |
+----+------------+-----------+
5 rows in set (0.00 sec)

Subqueries in the SELECT Clause

Subqueries in the SELECT clause allow you to include calculated or derived values in the result set by embedding a query inside the SELECT statement.

These subqueries are evaluated once for each row processed by the main query and return a single value for each of these rows, which can then be displayed alongside other columns in the result.

SELECT column_name, ..., (subquery)
FROM table_name;

Example

This SQL statement will retrieve the first_name and last_name attributes of all the records in the employees table, alongside with their associated contract_name attribute in the contracts table:

mysql> SELECT first_name, last_name, (SELECT contract_name FROM contracts WHERE employees.id = contracts.employee_id) AS contract_name FROM employees;
+------------+-----------+-----------------------------+
| first_name | last_name | contract_name               |
+------------+-----------+-----------------------------+
| John       | Doe       | Sales Expansion Project     |
| Jane       | Smith     | NULL                        |
| Alice      | Johnson   | Employee Onboarding Program |
| Bob        | Brown     | NULL                        |
| Charlie    | Davis     | Data Migration to Cloud     |
| Diana      | Miller    | NULL                        |
| Eve        | Wilson    | NULL                        |
| Frank      | Moore     | Rebranding Strategy         |
| Grace      | Taylor    | AI Development Initiative   |
| Hank       | Anderson  | NULL                        |
+------------+-----------+-----------------------------+
10 rows in set (0.00 sec)

Subqueries in the FROM Clause

Subqueries in the FROM clause, also known as derived tables, allow you to create temporary tables that can be queried just like regular tables:

SELECT column_name, ...
FROM (subquery)
AS derived_table_name;

Note: Derived tables must be given an alias in order to be referenced in the outer query.

Example

This SQL statement will retrieve the department_name and employee_count attributes from the derived table department_summary, where records are aggregated using the COUNT() function and grouped using the department attribute:

mysql> SELECT department_name, employee_count FROM (SELECT department AS department_name, COUNT(id) AS employee_count FROM employees GROUP BY department) AS department_summary;
+-----------------+----------------+
| department_name | employee_count |
+-----------------+----------------+
| Sales           |              3 |
| Engineering     |              3 |
| HR              |              2 |
| Marketing       |              2 |
+-----------------+----------------+
4 rows in set (0.00 sec)

Summary

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

• A subquery is a query nested within another query whose result can be used by the outer query.
• A non-correlated subquery is a subquery that doesn't reference columns from the outer query and is executed once.
• A correlated subquery is a subquery that references columns from the outer query and is executed for each row.