Course Module 4. Working with databases - Lecture: Subqueries with tables

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