Subquery returns a table
And finally, the third option is when the subquery returns the whole table. This is the most common option.
Very often there are situations when we want to tweak a certain table a little. And only then join (using the JOIN ON operator) the corrected table with another.
Let's start with the simplest case, where we joined two tables with a JOIN:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
And as you probably remember, there are tasks in the task table that are not assigned to anyone: employee_id is NULL .
Let's generate a corrected table , where we assign all the hanging tasks to the director (his ID = 4).
To do this, we use the IFNULL() function :
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task
And the result of this query:
id | employee_id | name | deadline |
---|---|---|---|
1 | 1 | Fix a bug on the frontend | 2022-06-01 |
2 | 2 | Fix a bug on the backend | 2022-06-15 |
3 | 5 | Buy coffee | 2022-07-01 |
4 | 5 | Buy coffee | 2022-08-01 |
5 | 5 | Buy coffee | 2022-09-01 |
6 | 4 | Clean up the office | (NULL) |
7 | 4 | Enjoy life | (NULL) |
8 | 6 | Enjoy life | (NULL) |
The corrected cell is marked in red.
Now let's substitute our corrected table into the query:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
Instead of the task table .
Such a request would look something like this:
SELECT * FROM employee e JOIN (
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline
FROM task
) t ON e.id = t.emploee_id
Instead of the word task , we wrote parentheses and placed the request body in them.
By the way, the alias t (alias) for the nested query was very useful. A nested query, unlike a table, does not have its own name, so the alias is very out of place.
And here is the result of such a query:
id | name | occupation | salary | age | join_date | id | employee_id | name |
---|---|---|---|---|---|---|---|---|
1 | Ivanov Ivan | Programmer | 100000 | 25 | 2012-06-30 | 1 | 1 | Fix a bug on the frontend |
2 | Petrov Petr | Programmer | 80000 | 23 | 2013-08-12 | 2 | 2 | Fix a bug on the backend |
4 | Rabinovich Moisha | Director | 200000 | 35 | 2015-05-12 | 6 | 4 | Clean up the office |
4 | Rabinovich Moisha | Director | 200000 | 35 | 2015-05-12 | 7 | 4 | Enjoy life |
5 | Kirienko Anastasia | Office Manager | 40000 | 25 | 2015-10-10 | 4 | 5 | Buy coffee |
5 | Kirienko Anastasia | Office Manager | 40000 | 25 | 2015-10-10 | 5 | 5 | Buy coffee |
5 | Kirienko Anastasia | Office Manager | 40000 | 25 | 2015-10-10 | 3 | 5 | Buy coffee |
6 | Vaska | cat | 1000 | 3 | 2018-11-11 | 8 | 6 | Enjoy life |
Our director has the task of “cleaning up the office”, I think he will quickly find someone to delegate it to :) Using the WITH statement
By the way, starting with version 8 of MySQL, you no longer have to put all your subqueries right inside the final query. They can be performed separately. For this, the WITH statement is used .
It allows you to create a virtual table (named query) and its appearance is given by a template:
WITH Name AS (request)
There are often times when your subquery has unnamed columns, such as COUNT(*), that you haven't assigned a unique name to. In this case, the WITH statement has the option to specify new column names for the subquery.
Its second form is given by the template:
WITH Name(column1, column2, …) AS (request)
You can use as many virtual tables (named queries) as you want and refer to each other in them. The general form of your request will have something like this:
WITH name1 AS (request1),
name2 AS (request2),
name3 AS (request3)
SELECT * FROM name1 JOIN name2 ON …
Now let's take our scary query:
SELECT * FROM employee e JOIN (
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline
FROM task
) t ON e.id = t.emploee_id
And rewrite it using the WITH statement:
WITH task2(id, employee_id, name, deadline)
AS (SELECT id, IFNULL(employee_id, 4), name, deadline FROM task)
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id
Or you can do without column names, but then you will have to specify an alias for the IFNULL() function:
WITH task2 AS (
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task
)
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id
GO TO FULL VERSION