Примеры запросов с использованием предиката сравнения
SELECT DISTINCT EMP.DEPT_NO FROM EMP WHERE EMP.EMP_NAME = 'Smith';
Пример 14.1. Найти номера отделов, в которых работают служащие с фамилией 'Smith'. (html, txt)
Мы добавили спецификацию DISTINCT в раздел SELECT, потому что в одном отделе могут работать несколько служащих с фамилией 'Smith', а их число нас в данном случае не интересует. Кстати, если бы нас интересовало число служащих с фамилией 'Smith' в каждом отделе, где такие служащие работают, то следовало бы, например, написать такой запрос (пример 14.1.1):
SELECT EMP.DEPT_NO, COUNT(*) FROM EMP WHERE EMP.NAME = 'Smith' GROUP BY EMP.DEPT_NO;
Пример 14.1.1.
(html, txt)
В этом варианте запроса спецификация DISTINCT не требуется, поскольку в запросе содержится раздел GROUP BY, группировка производится в соответствии со значениями столбца EMP.DEPT_NO, и строка результата соответствует одной группе.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_BDATE > DATE '1965-04-15';
Пример 14.2. Найти номера, имена и номера отделов служащих, родившихся после 15 апреля 1965 г. (html, txt)
В результате этого запроса дубликатов быть не может, поскольку в список выборки включен столбец, являющийся первичным ключом таблицы EMP. Должно быть ясно, что по этой причине все строки результата будут различными.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_SAL > 0.1 * (SELECT DEPT_TOTAL_SAL FROM DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO);
Пример 14.3. Найти номера, имена и номера отделов служащих, размер заработной платы которых составляет больше одной десятой объема фонда заработной платы их отделов. (html, txt)
В этом SQL-запросе имеются две интересные особенности, которые мы до сих пор не обсуждали. Во-первых, второй операнд операции сравнения содержит подзапрос, возвращающий единственное значение, поскольку логическое выражение раздела WHERE этого подзапроса состоит из условия, однозначно определяющего значение первичного ключа таблицы DEPT. Во-вторых, в условии раздела WHERE подзапроса используется ссылка на столбец таблицы EMP, указанной в разделе FROM "внешнего" запроса. Подобные подзапросы в терминологии SQL традиционно называются корреляционными, и их следует понимать следующим образом1).
При выполнении внешнего запроса последовательно, строка за строкой, в некотором порядке, определяемом системой, производится проверка соответствия строк результирующей таблицы раздела FROM условию раздела WHERE. Если это условие включает корреляционные подзапросы, то внутри каждого из этих подзапросов ссылка на столбец внешней таблицы трактуется как ссылка на столбец текущей строки данной таблицы во внешнем цикле. Естественно, условие WHERE любого подзапроса может включать более глубоко вложенные подзапросы, на которые распространяется то же правило корреляции с внешними таблицами.
Кстати, эквивалентная формулировка на языке SQL примера 14.3 выглядит следующим образом (пример 14.3.1):
SELECT PRO_NO FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (DATE '2000-01-15', DATE '2002-12-31');
Пример 14.19. Найти номера проектов, которые выполнялись в период с 15 января 2000 г. по 31 декабря 2002 г. (html, txt) SELECT PRO_TITLE FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (CURRENT_DATE, INTERVAL '1' YEAR);
Пример 14.20. Найти названия проектов, которые будут выполняться в течение следующего года. (html, txt)
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.21. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе не является минимальной. (html, txt)
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката EXISTS (пример 14.21.1):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EXISTS(SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL > EMP1.EMP_SAL);
Пример 14.21.1.
(html, txt)
Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функции MIN (пример 14.21.2):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > (SELECT MIN(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.21.2.
(html, txt) SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME = SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Пример 14.22. Найти номера и имена сотрудников отдела 65, однофамильцы которых работают в этом же отделе. (html, txt)
Заметим, что эта формулировка эквивалентна следующей формулировке (пример 14.22.1):
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME IN (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Пример 14.22.1.
(html, txt)
Возможна формулировка с использованием агрегатной функции COUNT (пример 14.22.2):
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;
Пример 14.22.2.
(html, txt)
Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения (пример 14.22.3):
SELECT DISTINCT EMP.EMP_NO, EMP.EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.DEPT_NO = 65 AND EMP.EMP_NAME = EMP1.EMP_NAME AND EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO;
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (EMP_NAME, EMP_BDATE) DISTINCT FROM (SELECT EMP1.EMP_NAME, EMP1.EMP_BDATE FROM EMP EMP1, DEPT WHERE EMP1.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_MNG = EMP1.EMP_NO);
Пример 14.28. Найти номера и имена служащих отдела 65, которых можно отличить по данным об имени и дате рождения от руководителя отдела 65. (html, txt) SELECT EMP1.EMP_NO, EMP2.EMP_NO FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO <> EMP2.EMP_NO AND NOT ((EMP1.EMP_NAME, EMP1.EMP_BDATE) DISTINCT FROM (EMP2.EMP_NAME, EMP2.EMP_BDATE));
Пример 14.29. Найти все пары номеров таких служащих отдела 65, которых нельзя различить по данным об имени и дате рождения. (html, txt)
SELECT DISTINCT EMP.DEPT_NO FROM EMP WHERE EMP.EMP_NAME = 'Smith';
Пример 14.1. Найти номера отделов, в которых работают служащие с фамилией 'Smith'.
Мы добавили спецификацию DISTINCT в раздел SELECT, потому что в одном отделе могут работать несколько служащих с фамилией 'Smith', а их число нас в данном случае не интересует. Кстати, если бы нас интересовало число служащих с фамилией 'Smith' в каждом отделе, где такие служащие работают, то следовало бы, например, написать такой запрос (пример 14.1.1):
SELECT EMP.DEPT_NO, COUNT(*) FROM EMP WHERE EMP.NAME = 'Smith' GROUP BY EMP.DEPT_NO;
Пример 14.1.1.
В этом варианте запроса спецификация DISTINCT не требуется, поскольку в запросе содержится раздел GROUP BY, группировка производится в соответствии со значениями столбца EMP.DEPT_NO, и строка результата соответствует одной группе.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_BDATE > DATE '1965-04-15';
Пример 14.2. Найти номера, имена и номера отделов служащих, родившихся после 15 апреля 1965 г.
В результате этого запроса дубликатов быть не может, поскольку в список выборки включен столбец, являющийся первичным ключом таблицы EMP. Должно быть ясно, что по этой причине все строки результата будут различными.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_SAL > 0.1 * (SELECT DEPT_TOTAL_SAL FROM DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO);
Пример 14.3. Найти номера, имена и номера отделов служащих, размер заработной платы которых составляет больше одной десятой объема фонда заработной платы их отделов.
В этом SQL-запросе имеются две интересные особенности, которые мы до сих пор не обсуждали. Во-первых, второй операнд операции сравнения содержит подзапрос, возвращающий единственное значение, поскольку логическое выражение раздела WHERE этого подзапроса состоит из условия, однозначно определяющего значение первичного ключа таблицы DEPT. Во-вторых, в условии раздела WHERE подзапроса используется ссылка на столбец таблицы EMP, указанной в разделе FROM "внешнего" запроса. Подобные подзапросы в терминологии SQL традиционно называются корреляционными, и их следует понимать следующим образом2).
При выполнении внешнего запроса последовательно, строка за строкой, в некотором порядке, определяемом системой, производится проверка соответствия строк результирующей таблицы раздела FROM условию раздела WHERE. Если это условие включает корреляционные подзапросы, то внутри каждого из этих подзапросов ссылка на столбец внешней таблицы трактуется как ссылка на столбец текущей строки данной таблицы во внешнем цикле. Естественно, условие WHERE любого подзапроса может включать более глубоко вложенные подзапросы, на которые распространяется то же правило корреляции с внешними таблицами.
Кстати, эквивалентная формулировка на языке SQL примера 14.3 выглядит следующим образом (пример 14.3.1):
SELECT PRO_NO FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (DATE '2000-01-15', DATE '2002-12-31');
Пример 14.19. Найти номера проектов, которые выполнялись в период с 15 января 2000 г. по 31 декабря 2002 г.SELECT PRO_TITLE FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (CURRENT_DATE, INTERVAL '1' YEAR);
Пример 14.20. Найти названия проектов, которые будут выполняться в течение следующего года.
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.21. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе не является минимальной.
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката EXISTS (пример 14.21.1):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EXISTS(SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL > EMP1.EMP_SAL);
Пример 14.21.1.
Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функции MIN (пример 14.21.2):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > (SELECT MIN(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.21.2. SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME = SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Пример 14.22. Найти номера и имена сотрудников отдела 65, однофамильцы которых работают в этом же отделе.
Заметим, что эта формулировка эквивалентна следующей формулировке (пример 14.22.1):
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME IN (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Пример 14.22.1.
Возможна формулировка с использованием агрегатной функции COUNT (пример 14.22.2):
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;
Пример 14.22.2.
Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения (пример 14.22.3):
SELECT DISTINCT EMP.EMP_NO, EMP.EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.DEPT_NO = 65 AND EMP.EMP_NAME = EMP1.EMP_NAME AND EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO;
Пример 14.22.3.
В последней формулировке мы вынуждены везде использовать квалифицированные имена столбцов, потому что на одном уровне используются два вхождения таблицы EMP.
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (EMP_NAME, EMP_BDATE) DISTINCT FROM (SELECT EMP1.EMP_NAME, EMP1.EMP_BDATE FROM EMP EMP1, DEPT WHERE EMP1.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_MNG = EMP1.EMP_NO);
Пример 14.28. Найти номера и имена служащих отдела 65, которых можно отличить по данным об имени и дате рождения от руководителя отдела 65.SELECT EMP1.EMP_NO, EMP2.EMP_NO FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO <> EMP2.EMP_NO AND NOT ((EMP1.EMP_NAME, EMP1.EMP_BDATE) DISTINCT FROM (EMP2.EMP_NAME, EMP2.EMP_BDATE));
Пример 14.29. Найти все пары номеров таких служащих отдела 65, которых нельзя различить по данным об имени и дате рождения.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP. DEPT_NO FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL;
Пример 14.3.1.
(html, txt)
Мы видим, что в терминах реляционной алгебры этот запрос представляет собой ограничение (по условию EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL) эквисоединения таблиц EMP и DEPT (по условию EMP.DEPT_NO = DEPT.DEPT_NO). Подобную операцию часто называют полусоединением (semijoin), поскольку в результирующей таблице используются столбцы только одного из операндов операции эквисоединения. Мы привели вторую формулировку запроса, преследуя две цели: (1) продемонстрировать, каким образом предикат сравнения можно использовать для задания условия соединения, и (2) показать, что запросы, содержащие вложенные запросы, часто могут быть переформулированы в запросы с соединениями.
SELECT EMP1.EMP_NO, EMP1.EMP_NAME, EMP1.DEPT_NO, EMP2.EMP_NAME FROM EMP AS EMP1, EMP AS EMP2, DEPT WHERE EMP1.EMP_SAL < 15000.00 AND EMP1.DEPT_NO = DEPT.DEPT_NO AND DEPT.DEPT_MNG = EMP2.EMP_NO;
Пример 14.4. Найти номера, имена, номера отделов и имена руководителей отделов служащих, размер заработной платы которых меньше 15000 руб. (html, txt)
Этот запрос представляет собой эквисоединение ограничения таблицы EMP (по условию EMP_SAL < 15000.00) с таблицами DEPT и EMP (по условиям EMP.DEPT_NO = DEPT.DEPT_NO и DEPT.DEPT_MNG = EMP2.EMP_NO соответственно). Таблица EMP участвует в качестве операнда операции эквисоединения два раза. Поэтому в разделе FROM ей присвоены два псевдонима - EMP1 и EMP2. Следуя предписанному стандартом порядку выполнения запроса, можно считать, что введение этих псевдонимов обеспечивает переименование столбцов таблицы EMP, требуемое для выполнения раздела FROM с образованием расширенного декартова произведения таблиц-операндов.2) Заметим также, что в данном случае мы имеем дело с полным эквисоединением трех таблиц (а не с полусоединением), поскольку в списке выборки присутствуют имена столбцов каждой из них.
Покажем способ формулировки этого запроса с использованием вложенного подзапроса в качестве элемента списка выборки (пример 14.4.1):
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO, ( SELECT EMP_NAME FROM EMP WHERE EMP_NO = DEPT_MNG) FROM EMP, DEPT WHERE EMP.EMP_SAL < 15000.00 AND EMP.DEPT_NO = DEPT.DEPT_NO;
Пример 14.4.1.
(html, txt)
Как показывает последний пример, в условии выборки подзапроса, участвующего в списке выборки, можно использовать имена столбов таблиц внешнего запроса. Из этой возможности языка SQL видно, что в разделе "Общие синтаксические правила построения скалярных выражений" предыдущей лекции для облегчения понимания материала мы немного исказили семантику оператора выборки. Там было сказано следующее: "После выполнения раздела WHERE (если в запросе отсутствуют разделы GROUP BY и HAVING, случай (a)) или выполнения явно или неявно заданного раздела HAVING (случай (b)) выполняется раздел SELECT. При выполнении этого раздела на основе таблицы T1 в случае (a) или на основе сгруппированной таблицы T3 в случае (b) строится таблица T4, содержащая столько строк, сколько строк или групп строк содержится в таблицах T1 илиT3 соответственно". В действительности, в общем случае очередная строка таблицы T4 должна строиться в тот момент, когда очередная строка или группа строк заносится в таблицу T1 или T3 соответственно.
Пример 14.22.3.
(html, txt)
В последней формулировке мы вынуждены везде использовать квалифицированные имена столбцов, потому что на одном уровне используются два вхождения таблицы EMP.
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.23. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе является максимальной. (html, txt)
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката NOT EXISTS (пример 14.23.1):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND NOT EXISTS (SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL < EMP1.EMP_SAL);
Пример 14.23.1.
(html, txt)
Можно сформулировать этот же запрос с использованием агрегатной функции MAX (пример 14.23.2):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.23.2.
(html, txt) SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Пример 14.24. Найти номера и имена сотрудников, не имеющих однофамильцев (html, txt)
Этот запрос можно переформулировать на основе использования предиката NOT EXISTS или агрегатной функции COUNT (по причине очевидности мы не приводим эти формулировки), но, в отличие от случая в примере 14.22.3, формулировка в виде запроса с соединением здесь не проходит. Формулировка запроса
SELECT DISTINCT EMP_NO, EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.EMP_NAME <> EMP1.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO;
эквивалентна формулировке
SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Очевидно, что этот запрос является бессмысленным ("Найти сотрудников, для которых имеется хотя бы один не однофамилец").
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP. DEPT_NO FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL;
Пример 14.3.1.
Мы видим, что в терминах реляционной алгебры этот запрос представляет собой ограничение (по условию EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL) эквисоединения таблиц EMP и DEPT (по условию EMP.DEPT_NO = DEPT.DEPT_NO). Подобную операцию часто называют полусоединением (semijoin), поскольку в результирующей таблице используются столбцы только одного из операндов операции эквисоединения. Мы привели вторую формулировку запроса, преследуя две цели: (1) продемонстрировать, каким образом предикат сравнения можно использовать для задания условия соединения, и (2) показать, что запросы, содержащие вложенные запросы, часто могут быть переформулированы в запросы с соединениями.
SELECT EMP1.EMP_NO, EMP1.EMP_NAME, EMP1.DEPT_NO, EMP2.EMP_NAME FROM EMP AS EMP1, EMP AS EMP2, DEPT WHERE EMP1.EMP_SAL < 15000.00 AND EMP1.DEPT_NO = DEPT.DEPT_NO AND DEPT.DEPT_MNG = EMP2.EMP_NO;
Пример 14.4. Найти номера, имена, номера отделов и имена руководителей отделов служащих, размер заработной платы которых меньше 15000 руб.
Этот запрос представляет собой эквисоединение ограничения таблицы EMP (по условию EMP_SAL < 15000.00) с таблицами DEPT и EMP (по условиям EMP.DEPT_NO = DEPT.DEPT_NO и DEPT.DEPT_MNG = EMP2.EMP_NO соответственно). Таблица EMP участвует в качестве операнда операции эквисоединения два раза. Поэтому в разделе FROM ей присвоены два псевдонима - EMP1 и EMP2. Следуя предписанному стандартом порядку выполнения запроса, можно считать, что введение этих псевдонимов обеспечивает переименование столбцов таблицы EMP, требуемое для выполнения раздела FROM с образованием расширенного декартова произведения таблиц-операндов.3) Заметим также, что в данном случае мы имеем дело с полным эквисоединением трех таблиц (а не с полусоединением), поскольку в списке выборки присутствуют имена столбцов каждой из них.
Покажем способ формулировки этого запроса с использованием вложенного подзапроса в качестве элемента списка выборки (пример 14.4.1):
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO, ( SELECT EMP_NAME FROM EMP WHERE EMP_NO = DEPT_MNG) FROM EMP, DEPT WHERE EMP.EMP_SAL < 15000.00 AND EMP.DEPT_NO = DEPT.DEPT_NO;
Пример 14.4.1.
Как показывает последний пример, в условии выборки подзапроса, участвующего в списке выборки, можно использовать имена столбов таблиц внешнего запроса. Из этой возможности языка SQL видно, что в разделе "Общие синтаксические правила построения скалярных выражений" предыдущей лекции для облегчения понимания материала мы немного исказили семантику оператора выборки. Там было сказано следующее: "После выполнения раздела WHERE (если в запросе отсутствуют разделы GROUP BY и HAVING, случай (a)) или выполнения явно или неявно заданного раздела HAVING (случай (b)) выполняется раздел SELECT. При выполнении этого раздела на основе таблицы T1 в случае (a) или на основе сгруппированной таблицы T3 в случае (b) строится таблица T4, содержащая столько строк, сколько строк или групп строк содержится в таблицах T1 илиT3 соответственно". В действительности, в общем случае очередная строка таблицы T4 должна строиться в тот момент, когда очередная строка или группа строк заносится в таблицу T1 или T3 соответственно.
Пример 14.23. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе является максимальной.
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката NOT EXISTS (пример 14.23.1):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND NOT EXISTS (SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL < EMP1.EMP_SAL);
Пример 14.23.1.
Можно сформулировать этот же запрос с использованием агрегатной функции MAX (пример 14.23.2):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.23.2. SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Пример 14.24. Найти номера и имена сотрудников, не имеющих однофамильцев
Этот запрос можно переформулировать на основе использования предиката NOT EXISTS или агрегатной функции COUNT (по причине очевидности мы не приводим эти формулировки), но, в отличие от случая в примере 14.22.3, формулировка в виде запроса с соединением здесь не проходит. Формулировка запроса
SELECT DISTINCT EMP_NO, EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.EMP_NAME <> EMP1.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO;
эквивалентна формулировке
SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Очевидно, что этот запрос является бессмысленным ("Найти сотрудников, для которых имеется хотя бы один не однофамилец").