Молодогвардейцев 454015 Россия, Челябинская область, город Челябинск 89085842764
MindHalls logo

Простые и сложные SQL подзапросы с примерами

Приветствую всех! Сегодня я коснусь такой замечательной темы, как базы данных. Уже давно проекты, даже самые мелкие, не мыслят своего существования без БД. Прошли те времена, когда информацию нужно было хранить в файликах, самостоятельно следить за ее систематизацией, структурированием и прочим. СУБД нас спасает от всего этого, поэтому нельзя не уметь ей пользоваться. Будь то MySQL, Oracle, PostgreSQL или любая другая.

Речь пойдет о подзапросах, почему часто без них не обойтись, и как использовать их максимально эффективно в своей работе.

Использование SQL подзапросов

Вряд ли найдется проект, который использует БД с одной единственной таблицей. И даже если так, то скорее всего появится задача, которую будет очень сложно решить одним запросом. Поэтому приходится писать еще один запрос, чаще всего в блоке WHERE.

Почему приходится? Все просто, потому что…

Первое и самое главное правило подзапросов: не используйте подзапросы!

А теперь, когда вы узнали всю правду, но желание писать подзапросы все еще сохранилось, я расскажу чем отличаются простые подзапросы от сложных.

Сложные SQL подзапросы

Это такой противный вид подзапроса, который зависит от внешнего. Нетрудно догадаться, что он будет исполняться ровно столько раз, сколько строк вернет внешний запрос. В масштабах сотни это не трагично, а теперь представьте, что у нас миллион строк? Получается, что мы вместо одного запроса выполним миллион! А если в подзапросе есть еще один сложный подзапрос, который тоже вернет миллион строк? Тогда количество обращений к базе данных будет равно 1 000 0002, миллион в квадрате, Карл! А теперь представьте, что приложение написано на Java…

Для иллюстрации я выбрал две задачки из своего университетского курса по базам.

Схема базы данных

Схема базы данных SQL

Примеры сложных SQL подзапросов

Для каждого менеджера выведите его порядковый номер, имя, и наиболее 
высокооплачиваемого сотрудника этого менеджера. Сотрудники, чей менеджер
не определен, в запросе не учитываются.

SELECT e1.employee_id, e1.first_name, e2.first_name, e2.salary 
FROM 
employees e1 JOIN employees e2 ON(e1.employee_id = e2.manager_id) 
WHERE 
e2.salary = 
(SELECT max(e3.salary) FROM employees e3 WHERE e3.manager_id = e1.employee_id);

Создайте запрос, который возвращает имя, должность и зарплату для всех сотрудников, чья зарплат выше средней, и чьим коллегой является сотрудник с именем, содержащим ‘T’.

SELECT e.first_name, j.job_title, e.salary FROM 
employees e JOIN 
jobs j ON(e.job_id = j.job_id) JOIN 
employees e1 ON(e1.department_id = e.department_id) 
WHERE 
e.salary > (SELECT AVG(e2.salary) FROM employees e2) AND 
e1.first_name LIKE '%T%' AND 
e1.first_name NOT LIKE e.first_name;

Простые SQL подзапросы

Простой подзапрос нам друг. Он исполнится ровно один раз в любом случае. Чаще всего простые подзапросы пишутся в блоке FROM, когда мы заранее формируем некий набор строк, например, заранее сгруппированный, и из него уже выбираем данные.

Примеры простых SQL подзапросов

Для каждого менеджера выведите его порядковый номер, имя, и наиболее 
высокооплачиваемого сотрудника этого менеджера. Сотрудники, чей менеджер 
не определен, в запросе не учитываются.

SELECT e3.first_name, e4.first_name, e4.salary 
FROM 
(
SELECT e1.employee_id, MAX(e2.salary) AS maxsalary 
FROM 
employees e1 JOIN employees e2 ON(e1.employee_id = e2.manager_id) 
GROUP BY e1.employee_id
) ntable /* Внутренний простой запрос выполнится один раз */ 
INNER JOIN employees e3 ON(ntable.employee_id = e3.employee_id) INNER JOIN 
employees e4 ON(e3.employee_id = e4.manager_id) 
WHERE ntable.maxsalary = e4.salary;

Напишите запрос, который выводит фамилию, зарплату и комиссию тех сотрудников, чья зарплата совпадает с зарплатой хотя бы одного коллеги (то есть человека, работающего в том же департаменте), получающего комиссию.

SELECT e.last_name, e.salary, e.commission_pct 
FROM 
employees e JOIN 
(
SELECT DISTINCT(e1.employee_id) 
FROM 
employees e1 JOIN 
employees e2 ON(e1.department_id = e2.department_id AND 
e1.employee_id != e2.employee_id) 
WHERE e1.salary = e2.salary AND e2.commission_pct IS NOT NULL
) ntable ON(e.employee_id = ntable.employee_id);

Заключение

К огромному счастью разработчиков и пользователей, любой сложный подзапрос можно переписать на простой и избежать миллионов-миллионов-миллионов обращений к базе данных. А если у вас не получается, то удаляйте все и пишите заново, или попросите своего коллегу, который уже постиг этот нелегкий навык.

А вы умеете переписывать сложные подзапросы на простые?