MySQL Subqueries
подзапросов
Подзапрос - это SQL-запрос, вложенный в более крупный запрос.
- Подзапрос может появляться в:
- - предложение SELECT
- - ОТ условия
- - ГДЕ оговорка
- В MySQL подзапрос может быть вложен внутри оператора SELECT, INSERT, UPDATE, DELETE, SET или DO или внутри другого подзапроса.
- Подзапрос обычно добавляется в предложении WHERE другого оператора SQL SELECT.
- Вы можете использовать операторы сравнения, такие как>, <или =. Оператор сравнения также может быть оператором из нескольких строк, таким как IN, ANY, SOME или ALL.
- Подзапрос может рассматриваться как внутренний запрос, который представляет собой SQL-запрос, размещенный как часть другого запроса, называемого внешним запросом.
- Внутренний запрос выполняется первым перед его родительским запросом, так что результаты внутреннего запроса могут быть переданы во внешний запрос.
Содержание:
Подзапросы MySQL с EXISTS или NOT EXISTS
MySQL коррелированные подзапросы
Подзапросы MySQL в предложении FROM
Синтаксис подзапроса:
- Подзапрос (внутренний запрос) выполняется один раз перед выполнением основного запроса (внешнего запроса).
- Основной запрос (внешний запрос) использует результат подзапроса.
Синтаксис подзапроса, как указано в стандарте SQL и поддерживается в MySQL
УДАЛИТЬ ОТ t1
ГДЕ s11> ЛЮБОЙ
(ВЫБЕРИТЕ СЧЕТЧИК (*) / * без подсказки * / ОТ t2
ГДЕ НЕ СУЩЕСТВУЕТ
(ВЫБРАТЬ * ОТ T3
WHERE ROW (5 * t2.s1,77) =
(ВЫБЕРИТЕ 50,11 * s1 ОТ t4 СОЮЗ ВЫБРАТЬ 50,77 ОТ
(ВЫБРАТЬ * ОТ t5) AS t5))); Подзапрос может возвращать скаляр (одно значение), одну строку, один столбец или таблицу (одну или несколько строк одного или нескольких столбцов). Они называются скалярными, столбцовыми, строчными и табличными подзапросами.
Пример подзапроса MySQL:
Используя подзапрос, перечислите имена сотрудников, которым заплатили больше, чем «Александр» из emp_details.
mysql> SELECT first_name, last_name, salary FROM emp_details
ГДЕ зарплата> (ВЫБЕРИТЕ зарплату ОТ emp_details
ГДЕ first_name = 'Александр');
+ ------------ + ----------- + ---------- +
| имя_файла | фамилия | зарплата |
+ ------------ + ----------- + ---------- +
| Стивен | Король | 24000,00 |
| Нина | Коххар | 17000,00 |
| Лекс | Де Хаан | 17000,00 |
| РАБИ | ЧАНДРА | 15000,00 |
| Ана | Король | 17000,00 |
+ ------------ + ----------- + ---------- +
5 рядов в наборе (0,00 сек)
Подзапросы: Руководство
Есть несколько рекомендаций, которые следует учитывать при использовании подзапросов:
- Подзапрос должен быть заключен в скобки.
- Используйте однострочные операторы с однорядными подзапросами и используйте многострочные операторы с многострочными подзапросами.
- Если подзапрос (внутренний запрос) возвращает нулевое значение для внешнего запроса, внешний запрос не будет возвращать никаких строк при использовании определенных операторов сравнения в предложении WHERE.
Типы подзапросов
- Подзапрос как скалярный операнд
- Сравнение с использованием подзапросов
- Подзапросы с ALL, ANY, IN или SOME
- Подзапросы строки
- Подзапросы с EXISTS или NOT EXISTS
- Коррелированные подзапросы
- Подзапросы в предложении FROM
MySQL подзапрос как скалярный операнд
Скалярный подзапрос - это подзапрос, который возвращает ровно одно значение столбца из одной строки. Скалярный подзапрос - это простой операнд, и вы можете использовать его почти везде, где допустимо значение одного столбца или литерал. Если подзапрос возвращает 0 строк, то значение скалярного выражения подзапроса в NULL, а если подзапрос возвращает более одной строки, MySQL возвращает ошибку.
В некоторых ситуациях скалярный подзапрос не может быть использован. Если оператор допускает только буквальное значение, вы не можете использовать подзапрос. Например, LIMIT требует буквально целочисленных аргументов, а LOAD DATA INFILE требует буквально строкового имени файла. Вы не можете использовать подзапросы для предоставления этих значений.
Пример: подзапрос MySQL как скалярный операнд
mysql> SELECT employee_id, фамилия, (СЛУЧАЙ КОГДА Department_id = ( ВЫБЕРИТЬ Department_id из отделов WHERE location_id = 2500) ТОГДА «КАНАДА» ИЛИ «США» КОНЕЦ) расположение ОТ сотрудников; + ------------- + ------------- + ---------- + | employee_id | фамилия | местоположение | + ------------- + ------------- + ---------- + | 100 | Король | США | | 101 | Коххар | США | | 102 | Де Хаан | США | | 103 | Hunold | США | | 104 | Эрнст | США | | 105 | Остин | США | | - - - - - - - - - - - - - - - - - - - | | - - - - - - - - - - - - - - - - - - - | 107 строк в наборе (0,00 сек)
MySQL Subqueries: Использование сравнений
Подзапрос может использоваться до или после любого из операторов сравнения. Подзапрос может возвращать не более одного значения. Значение может быть результатом арифметического выражения или функции столбца. Затем SQL сравнивает значение, полученное в подзапросе, со значением на другой стороне оператора сравнения. Вы можете использовать следующие операторы сравнения:
| оператор | Описание |
|---|---|
| знак равно | Равно |
| > | Лучше чем |
| > = | Больше или равно |
| < | Меньше, чем |
| <= | Меньше или равно |
| знак равно | Не равно |
| <> | Не равно |
| <=> | NULL-сейф равен оператору |
Например, предположим, что вы хотите найти идентификатор сотрудника, имя, фамилию и фамилию, а также зарплаты для сотрудников, средняя зарплата которых выше, чем средняя зарплата по компании.
mysql> SELECT employee_id, first_name, last_name, salary
ОТ сотрудников ГДЕ ЗАРЛАДА
(ВЫБЕРИТЕ AVG (SALARY) ИЗ СОТРУДНИКОВ);
+ ------------- + ------------- + ------------ + -------- - +
| employee_id | имя_файла | фамилия | зарплата |
+ ------------- + ------------- + ------------ + -------- - +
| 100 | Стивен | Король | 24000,00 |
| 101 | Нина | Коххар | 17000,00 |
| 102 | Лекс | Де Хаан | 17000,00 |
| 103 | Александр | Hunold | 9000,00 |
| 108 | Нэнси | Гринберг | 12000,00 |
| 109 | Даниэль | Фавиет | 9000,00 |
| 120 | Мэтью | Вайс | 8000,00 |
| 121 | Адам | Фрипп | 8200,00 |
| 122 | Payam | Кауфлинг | 7900,00 |
| - - - - - - - - - - - - - - - - - - - - - - - - - - |
| - - - - - - - - - - - - - - - - - - - - - - - - - - |
+ ------------- + ------------- + ------------ + -------- - +
51 ряд в наборе (0,00 сек)
MySQL подзапросы с ALL, ANY, IN или SOME
Вы можете использовать подзапрос после оператора сравнения, за которым следует ключевое слово ALL, ANY или SOME.
Оператор ALL сравнивает значение с каждым значением, возвращаемым подзапросом. Поэтому оператор ALL (который должен следовать за оператором сравнения) возвращает TRUE, если сравнение равно TRUE для ВСЕХ значений в столбце, который возвращает подзапрос.
Синтаксис:операнд сравнения_оператор ALL (подзапрос)
NOT IN - это псевдоним для <> ALL. Таким образом, эти два утверждения одинаковы:
Код:
SELECT c1 FROM t1 WHERE c1 <> ALL (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT c1 FROM t2);
Пример: MySQL Subquery, оператор ALL
Следующий запрос выбирает отдел с самой высокой средней зарплатой. Подзапрос находит среднюю зарплату для каждого отдела, а затем основной запрос выбирает отдел с самой высокой средней зарплатой.
mysql> SELECT Department_id, AVG (SALARY) ОТ ГРУППЫ СОТРУДНИКОВ BY отдела_ид Имея AVG (SALARY)> = ВСЕ (ВЫБЕРИТЕ AVG (SALARY) ИЗ ГРУППЫ СОТРУДНИКОВ BY отдела_id); + --------------- + -------------- + | отдел_ид | AVG (SALARY) | + --------------- + -------------- + | 90 | 19333,333333 | + --------------- + -------------- + 1 ряд в наборе (0,00 сек)
Примечание. Здесь мы использовали ключевое слово ALL для этого подзапроса, поскольку выбранный запросом отдел должен иметь среднюю зарплату, превышающую или равную средней заработной плате других отделов.
ЛЮБОЙ оператор сравнивает значение с каждым значением, возвращаемым подзапросом. Поэтому ключевое слово ANY (которое должно следовать за оператором сравнения) возвращает TRUE, если сравнение равно TRUE для ЛЮБОГО значения в столбце, который возвращает подзапрос.
Синтаксис:
операнд сравнения_оператор ЛЮБОЙ (подзапрос)
Пример: MySQL Subquery, ЛЮБОЙ оператор
Следующий запрос выбирает любого сотрудника, который работает в расположении 1800. Подзапрос находит идентификатор отдела в расположении 1800, а затем основной запрос выбирает сотрудников, которые работают в любом из этих отделов.
таблица сотрудников :
Таблица отделов :
mysql> ВЫБЕРИТЕ имя, фамилию, отдел ОТ сотрудников ГДЕ Department_id = ЛЮБОЙ (ВЫБЕРИТЕ DEPARTMENT_ID ИЗ ОТДЕЛОВ, ГДЕ location_id = 1800); + ------------ + ----------- + --------------- + | имя_файла | фамилия | отдел_ид | + ------------ + ----------- + --------------- + | Майкл | Хартштейн | 20 | | Пэт | Фэй | 20 | + ------------ + ----------- + --------------- + 2 ряда в наборе (0,00 сек)
Примечание. Мы использовали ЛЮБОЕ ключевое слово в этом запросе, поскольку вполне вероятно, что подзапрос найдет более одного отдела в 1800 местах. Если вы используете ключевое слово ALL вместо ключевого слова ANY, данные не будут выбраны, потому что ни один сотрудник не работает во всех отделениях с местоположением 1800
При использовании с подзапросом слово IN (равное любому члену списка) является псевдонимом для = ANY. Таким образом, следующие два утверждения одинаковы:
Код:
SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2);
Слово НЕКОТОРЫЕ - псевдоним ЛЮБОГО. Таким образом, эти два утверждения одинаковы:
Код:
SELECT c1 FROM t1 WHERE c1 <> ANY (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 <> SOME (SELECT c1 FROM t2);
MySQL Row Subqueries
Подзапрос строки - это подзапрос, который возвращает одну строку и более одного значения столбца. Вы можете использовать операторы сравнения =,>, <,> =, <=, <>,! =, <=>. Смотрите следующие примеры:
Код:
SELECT * FROM table1 WHERE (col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10);
SELECT * FROM table1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10);
Для обоих запросов
- если таблица table2 содержит одну строку с id = 10, подзапрос возвращает одну строку. Если эта строка имеет значения col3 и col4, равные значениям col1 и col2 любых строк в таблице table1, выражение WHERE имеет значение TRUE, и каждый запрос возвращает эти строки таблицы table1.
- Если значения строки col2 и col4 таблицы2 не равны значениям col1 и col2 какой-либо строки таблицы1, выражение имеет значение FALSE, и запрос возвращает пустой набор результатов. Выражение неизвестно (т. Е. NULL), если подзапрос не создает строк.
- Ошибка возникает, если подзапрос создает несколько строк, поскольку подзапрос строки может возвращать не более одной строки.
Пример: MySQL Row Subqueries
В следующих примерах запросы показывают другой результат в соответствии с вышеуказанными условиями:
Таблица отделов :
таблица сотрудников :
mysql> SELECT first_name ОТ сотрудников WHERE ROW (отдел_ид, менеджер_идентификатор) = (ВЫБРАТЬ отдел_идентификатор, менеджер_ид ОТ ОТДЕЛОВ ГДЕ location_id = 1800); + ------------ + | имя_файла | + ------------ + | Пэт | + ------------ + 1 ряд в наборе (0,00 сек)
Код:
mysql>SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM departments WHERE location_id = 2800);
Empty set (0.00 sec)
Код:
mysql>SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM departments WHERE location_id = 1700);
ERROR 1242 (21000): Subquery returns more than 1 row
Подзапросы MySQL с EXISTS или NOT EXISTS
Оператор EXISTS проверяет наличие строк в наборе результатов подзапроса. Если найдено значение строки подзапроса, подзапрос EXISTS равен TRUE, и в этом случае подзапрос NOT EXISTS равен FALSE.
Синтаксис:
SELECT column1 FROM table1, ГДЕ СУЩЕСТВУЕТ (SELECT * FROM table2);
В приведенном выше операторе, если table2 содержит какие-либо строки, даже строки со значениями NULL, условие EXISTS имеет значение TRUE. Как правило, подзапрос EXISTS начинается с SELECT *, но он может начинаться с SELECT 'X', SELECT 5 или SELECT column1 или чего-либо еще. MySQL игнорирует список SELECT в таком подзапросе, поэтому это не имеет значения.
Пример: подзапросы MySQL с EXISTS
Из приведенных ниже таблиц (сотрудники) найдите сотрудников (employee_id, first_name, last_name, job_id, Department_id), у которых есть хотя бы один человек, подотчетный им.
таблица сотрудников :
ВЫБРАТЬ идентификатор сотрудника, имя, фамилия, идентификатор задания, идентификатор отдела ОТ сотрудников E ГДЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ * ОТ РАБОТНИКОВ, ГДЕ manager_id = E.employee_id); + ------------- + ------------ + ----------- + --------- + --------------- + | employee_id | имя_файла | фамилия | job_id | отдел_ид | + ------------- + ------------ + ----------- + --------- + --------------- + | 100 | Стивен | Король | AD_PRES | 90 | | 101 | Нина | Коххар | AD_VP | 90 | | 102 | Лекс | Де Хаан | AD_VP | 90 | | 103 | Александр | Hunold | IT_PROG | 60 | | 108 | Нэнси | Гринберг | FI_MGR | 100 | | 114 | Ден | Рафаэли | PU_MAN | 30 | | 120 | Мэтью | Вайс | ST_MAN | 50 | | 121 | Адам | Фрипп | ST_MAN | 50 | | ---------- | ---------- | --------- | ------- | ------------- | + ------------- + ------------ + ----------- + --------- + --------------- + 18 рядов в наборе (0,02 сек)
Пример: подзапросы MySQL с NOT EXISTS
Подзапрос NOT EXISTS почти всегда содержит корреляции. Вот пример:
В следующей таблице (отделы и сотрудники) найдите все отделы (департамент_ид, отдел_имя), в которых нет сотрудников.
Таблица отделов :
таблица сотрудников :
mysql> SELECT Department_id, Department_name ОТ департаментов d ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ * ОТ РАБОТНИКОВ, ГДЕ Department_id = d.department_id); + --------------- + ---------------------- + | отдел_ид | название отдела | + --------------- + ---------------------- + | 120 | Казначейство | | 130 | Корпоративный налог | | 140 | Контроль и Кредит | | 150 | Услуги для акционеров | | 160 | Преимущества | 170 | Производство | | 180 | Строительство | | 190 | Договаривающиеся | | 200 | Операции | | ------------ | -------------------- | + --------------- + ---------------------- + 16 рядов в наборе (0,00 сек)
MySQL коррелированные подзапросы
Коррелированный подзапрос - это подзапрос, который содержит ссылку на таблицу (в родительском запросе), которая также появляется во внешнем запросе. MySQL оценивает изнутри наружу.
Коррелированный синтаксис подзапроса:
Пример - 1: коррелированные подзапросы MySQL
Следующий запрос найти всех сотрудников, которые зарабатывают больше, чем средняя зарплата в их отделе.
таблица сотрудников :
mysql> ВЫБРАТЬ фамилию, зарплату, департамент_ид ОТ сотрудников посторонних ГДЕ зарплата> (ВЫБЕРИТЕ AVG (зарплата) ОТ сотрудников ГДЕ отдел_ид = externalr.department_id); + ----------- + ---------- + --------------- + | фамилия | зарплата | отдел_ид | + ----------- + ---------- + --------------- + | Король | 24000,00 | 90 | | Hunold | 9000,00 | 60 | | Эрнст | 6000,00 | 60 | | Гринберг | 12000,00 | 100 | | Фавиет | 9000,00 | 100 | | Рафаэли | 11000,00 | 30 | | Вайс | 8000,00 | 50 | | Фрипп | 8200,00 | 50 | | -------- | -------- | ------------ | + ----------- + ---------- + --------------- + 38 рядов в наборе (0,02 сек)
Пример - 2: коррелированные подзапросы MySQL
Из таблиц employee и job_history отображаются сведения о тех сотрудниках, которые сменили работу хотя бы один раз.
таблица сотрудников :
таблица job_history :
mysql> SELECT first_name, last_name, employee_id, job_id ОТ сотрудников E WHERE 1 <= (ВЫБЕРИТЕ COUNT (*) ИЗ Job_history WHERE employee_id = E.employee_id); + ------------ + ----------- + ------------- + --------- + | имя_файла | фамилия | employee_id | job_id | + ------------ + ----------- + ------------- + --------- + | Нина | Коххар | 101 | AD_VP | | Лекс | Де Хаан | 102 | AD_VP | | Ден | Рафаэли | 114 | PU_MAN | | Payam | Кауфлинг | 122 | ST_MAN | | Джонатон | Тейлор | 176 | SA_REP | | Дженнифер | Кит | 200 | AD_ASST | | Майкл | Хартштейн | 201 | MK_MAN | + ------------ + ----------- + ------------- + --------- + 7 рядов в наборе (0,00 сек)
Подзапросы MySQL в предложении FROM
Подзапросы работают в предложении оператора SELECT FROM. Синтаксис:
SELECT ... FROM (подзапрос) [AS] имя ...
Каждая таблица в предложении FROM должна иметь имя, поэтому предложение имени [AS] является обязательным. Любые столбцы в списке выбора подзапроса должны иметь уникальные имена.
Пример: подзапросы MySQL в предложении FROM
У нас есть следующая таблица tb1.
mysql> CREATE TABLE tb1 (c1 INT, c2 CHAR (5), c3 FLOAT); Запрос в порядке, затронуто 0 строк (0,73 с)
Давайте вставим некоторые значения в таблицу tb1.
mysql> INSERT INTO tb1 VALUES (1, '1', 1.0); Запрос в порядке, затрагивается 1 строка (0,11 сек) mysql> INSERT INTO tb1 VALUES (2, '2', 2.0); Запрос в порядке, затрагивается 1 строка (0,07 сек) mysql> INSERT INTO tb1 VALUES (3, 3, 3,0); Запрос в порядке, затрагивается 1 строка (0,03 сек) mysql> select * from tb1; + ------ + ------ + ------ + | с1 | с2 | с3 | + ------ + ------ + ------ + | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | + ------ + ------ + ------ + 3 ряда в наборе (0,00 сек)
Вот как использовать подзапрос в предложении FROM, используя таблицу примеров (tb1):
mysql> SELECT sc1, sc2, sc3 ОТ (ВЫБЕРИТЕ c1 AS sc1, c2 AS sc2, c3 * 3 AS sc3 ОТ tb1) AS sb ГДЕ sc1> 1; + ------ + ------ + ------ + | sc1 | sc2 | sc3 | + ------ + ------ + ------ + | 2 | 2 | 6 | | 3 | 3 | 9 | + ------ + ------ + ------ + 2 ряда в наборе (0,02 сек)
Предыдущая: ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ
Далее: Процедура MySQL
Новый контент: Composer: менеджер зависимостей для PHP , R программирования