Course Module 4. Working with databases - Lecture: Subqueries

Nested queries in SQL

The SQL language allows you to nest one query within another query. This makes it possible to write one very large query that will do something large and complex, although the readability of the code is greatly reduced.

Depending on how many values are returned by subqueries, the area where they can be applied changes. There are three options in total:

The subquery returns one single value (one column and one row).
The subquery returns a list of values (a table with one column).
The subquery returns a table (many columns, any number of rows).

Let's look at one example for each case.

Subquery with scalar result

Let's find a list of all our employees from the employee table whose salary is higher than the average for the company. How can we do it?

We can easily filter employees by comparing their salary with the average if we know it in advance. At the same time, we have already written a query that allows us to calculate the average salary of the company's employees. Let's remember it:

SELECT AVG(salary) FROM employee

Then MySQL returned us the value: 76833.3333 .

How now to find a list of all employees whose salary is above average? It's also very simple:

 SELECT * FROM employee 
   WHERE salary > 76833.3333

The result of this query will be:

id	name	occupation	salary
1	Ivanov Ivan	Programmer	100000
2	Petrov Petr	Programmer	80000
4	Rabinovich Moisha	Director	200000

And now we just combine both requests by substituting the first request instead of the value 76833:

   SELECT * FROM employee 
   WHERE salary > (SELECT AVG(salary) FROM employee)

The result of this query will be the same:

id	name	occupation	salary
1	Ivanov Ivan	Programmer	100000
2	Petrov Petr	Programmer	80000
4	Rabinovich Moisha	Director	200000

Subquery with list of values

Do you remember once upon a time we had a task - to find all records from one table for which there are no corresponding records from another?

There was also this picture:

If I'm not mistaken, the task is as follows: display a list of all employees from the employee table for which there are no tasks in the task table .

Let's also find a solution in two steps.

First, let's write a query that will return the id of all employees who have tasks in the task table. Just remember two things:

remove duplicates - use the DISTINCT keyword.
remove NULL values from the result.

SELECT DISTINCT employee_id FROM task 
   WHERE employee_id IS NOT NULL

And here we got a beautiful result of such a request:

employee_id
1
2
5
4
6

Let's temporarily write it down for convenience as a sequence: 1,2,5,4,6. Now let's write a second query - to the employee table, which will return a list of employees whose id is not contained in the first list:

SELECT * FROM employee  
WHERE id NOT IN (1,2,5,4,6)

And the result of this query:

id	name	occupation	salary	age	join_date
3	Ivanov Sergey	Tester	40000	thirty	2014-01-01

And now, as in the previous example, you can combine both requests by simply substituting the body of the first request instead of the id list.

 SELECT * FROM employee 
   WHERE id NOT IN ( 
      	SELECT DISTINCT employee_id FROM task 
      	WHERE employee_id IS NOT NULL 
   )