Вложенные запросы в SQL
Язык SQL позволяет вкладывать одни запросы в другие запросы. Это дает возможность написать один очень большой запрос, который будет делать что-то большое и сложное, хотя при этом читабельность кода значительно снижается.
В зависимости от того сколько значений возвращают подзапросы, меняется область, где их можно применять. Всего возможны три варианта:
- Подзапрос возвращает одно единственное значение (одна колонка и одна строка).
- Подзапрос возвращает список значений (таблица с одной колонкой).
- Подзапрос возвращает таблицу (много колонок, любое количество строк).
Давай разберем по одному примеру для каждого случая.
Подзапрос со скалярным результатом
Давай найдем список всех наших сотрудников из таблицы employee, чья зарплата выше средней по компании. Как нам это сделать?
Мы можем легко отфильтровать сотрудников, сравнив их зарплату со средней, если будем знать ее заранее. В то же время мы уже писали запрос, который позволяет вычислить среднюю зарплату сотрудников компании. Вспомним его:
SELECT AVG(salary) FROM employee
Тогда MySQL вернул нам значение: 76833.3333.
Как теперь найти список всех сотрудников, чья зарплата выше средней? Тоже очень просто:
SELECT * FROM employee WHERE salary > 76833.3333
Результат этого запроса будет таким:
| id | name | occupation | salary |
|---|---|---|---|
| 1 | Иванов Иван | Программист | 100000 |
| 2 | Петров Петр | Программист | 80000 |
| 4 | Рабинович Мойша | Директор | 200000 |
А теперь мы просто совместим оба запроса, подставив вместо значения 76833 первый запрос:
SELECT * FROM employee WHERE salary > (SELECT AVG(salary) FROM employee)
Результат этого запроса будет таким же:
| id | name | occupation | salary |
|---|---|---|---|
| 1 | Иванов Иван | Программист | 100000 |
| 2 | Петров Петр | Программист | 80000 |
| 4 | Рабинович Мойша | Директор | 200000 |
Подзапрос со списком значений
Помнишь когда-то давно у нас была задача – найти все записи из одной таблицы, для которых нет соответствующих записей из другой?
Там еще была такая картинка:
Если не ошибаюсь, то задание звучит так: отобразите список всех сотрудников из таблицы employee, для которых нет задач в таблице task.
Давай тоже найдем решение в два этапа.
Сначала напишем запрос, который вернет id всех сотрудников, у которых есть задачи в таблице task. Только не забудьте две вещи:
- убрать дубликаты – используйте ключевое слово DISTINCT.
- уберите NULL-значения из результата.
SELECT DISTINCT employee_id FROM task WHERE employee_id IS NOT NULL
И вот мы получили красивый результат такого запроса:
| employee_id |
|---|
| 1 |
| 2 |
| 5 |
| 4 |
| 6 |
Давай для удобства временно запишем его в виде последовательности: 1,2,5,4,6. Теперь напишем второй запрос – к таблице employee, которая вернет нам список сотрудников, чьи id не содержатся в первом списке:
SELECT * FROM employee WHERE id NOT IN (1,2,5,4,6)
И результат такого запроса:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 3 | Иванов Сергей | Тестировщик | 40000 | 30 | 2014-01-01 |
А теперь можно, как и в предыдущем примере, совместить оба запроса, просто подставив вместо списка id тело первого запроса.
SELECT * FROM employee WHERE id NOT IN ( SELECT DISTINCT employee_id FROM task WHERE employee_id IS NOT NULL )
