Подзапросы MySQL

Подзапросы

Подзапрос — это SQL-запрос, вложенный в более крупный запрос.

  • Подзапрос может встречаться в:
    • — Предложение SELECT
    • — Предложение FROM
    • — Предложение WHERE
  • В MySQL подзапрос может быть вложен внутри оператора SELECT, INSERT, UPDATE, DELETE, SET или DO или внутри другого подзапроса.
  • Подзапрос обычно добавляется в предложение WHERE другого Оператор SQL SELECT.
  • Вы можете использовать операторы сравнения, такие как>,
  • Подзапрос можно рассматривать как внутренний запрос, который представляет собой запрос SQL, помещенный как часть другого запроса, называемого внешним запросом.
  • Внутренний запрос выполняется сначала перед его родительским запросом, так что результаты внутреннего запроса могут быть переданы внешнему запросу.

Содержание:

  • Синтаксис подзапроса
  • Пример подзапроса MySQL
  • Подзапросы: рекомендации и типы подзапросов
  • Подзапросы MySQL как скалярный операнд
  • Подзапросы MySQL: использование сравнений
  • Подзапросы MySQL со ВСЕМИ, ЛЮБЫМИ , IN или НЕКОТОРЫЕ
  • Подзапросы строк MySQL

Подзапросы MySQL с СУЩЕСТВУЕТ или НЕ СУЩЕСТВУЕТ

Коррелированные подзапросы MySQL

Подзапросы MySQL в предложении FROM

Синтаксис подзапроса:

  • Подзапрос (внутренний запрос) выполняется один раз перед выполнением основного запроса (внешнего запроса) .
  • Основной запрос (внешний запрос) использует результат подзапроса.

Синтаксис подзапроса, как определено стандартом SQL и поддерживается в MySQL.

 DELETE FROM t1 WHERE s11> ANY  (SELECT COUNT (*)/* no hint */FROM t2 WHERE NOT EXISTS (SELECT * FROM t3 WHERE ROW (5 * t2.s1,77) = (SELECT 50,11 * s1 FROM t4 UNION SELECT 50,77 FROM (  SELECT * FROM t5) AS t5))); 

Подзапрос может возвращать скаляр (одно значение), одну строку, один столбец или таблицу (одну или несколько строк одного или более столбцов). Они называются скалярными, столбцовыми, строковыми и табличными подзапросами.

Пример подзапроса MySQL:

Используя подзапрос, перечислите имя сотрудников, получивших больше, чем «Александр» из emp_details.

 mysql> SELECT first_name, last_name, salary FROM emp_details WHERE salary> (SELECT salary FROM emp_details WHERE first_name = 'Alexander'); + ------------ + ---  -------- + ---------- + |  first_name |  last_name |  зарплата | + ------------ + ----------- + ---------- + |  Стивен |  Король |  24000.00 ||  Нина |  Кочхар |  17000.00 ||  Лекс |  Де Хаан |  17000.00 ||  РАБИ |  ЧАНДРА |  15000.00 ||  Ана |  Король |  17000.00 | + ------------ + ----------- + ---------- + 5 рядов в наборе (0. 00 сек) 

Подзапросы: рекомендации

При использовании подзапросов следует учитывать некоторые рекомендации:
— A подзапрос должен быть заключен в круглые скобки.
— Используйте однострочные операторы с однорядными подзапросами и используйте многострочные операторы с многострочными подзапросами.
— Если подзапрос (внутренний запрос) возвращает нулевое значение внешнему запросу , внешний запрос не вернет никаких строк при использовании определенных операторов сравнения в предложении WHERE.

Типы подзапросов

  • Подзапрос как скалярный операнд
  • Сравнение с использованием подзапросов
  • Подзапросы со ВСЕМИ, ЛЮБОЙ, ВХОДНОЙ или НЕКОТОРОЙ
  • строкой Подзапросы
  • Подзапросы с EXISTS или НЕ СУЩЕСТВУЕТ
  • Коррелированные подзапросы
  • Подзапросы в предложении FROM

Подзапрос MySQL как скалярный операнд

Скалярный подзапрос — это подзапрос, который возвращает ровно одно значение столбца из одной строки. Скалярный подзапрос — это простой операнд, и вы можете использовать его почти везде, где разрешено одно значение столбца или литерал. Если подзапрос возвращает 0 строк, тогда значение выражения скалярного подзапроса равно NULL, а если подзапрос возвращает более одной строки, MySQL возвращает ошибку.
В некоторых случаях нельзя использовать скалярный подзапрос. Если оператор допускает только буквальное значение, вы не можете использовать подзапрос. Например, LIMIT требует буквальных целочисленных аргументов, а LOAD DATA INFILE требует буквального строкового имени файла. Вы не можете использовать подзапросы для предоставления этих значений.

Пример: подзапрос MySQL как скалярный операнд

 mysql> SELECT employee_id, last_name,  (СЛУЧАЙ, КОГДА Department_id = (ВЫБЕРИТЕ Department_id из отделов WHERE location_id = 2500) THEN 'Canada' ELSE 'USA' END) местоположение ОТ сотрудников; + ------------- + ------  ------- + ---------- + |  employee_id |  last_name |  расположение | + ------------- + ------------- + ---------- + |  100 |  Король |  США ||  101 |  Кочхар |  США ||  102 |  Де Хаан |  США ||  103 |  Хунольд |  США ||  104 |  Эрнст |  США ||  105 |  Остин |  США ||  - - - - - - - - - - - - - - - - - - - ||  - - - - - - - - - - - - - - - - - - | 107 строк в наборе (0,00 сек) 

Подзапросы MySQL: использование сравнений

Подзапрос можно использовать до или после любого из операторов сравнения. Подзапрос может возвращать не более одного значения. Значение может быть результатом арифметического выражения или функции столбца. Затем SQL сравнивает значение, полученное в результате подзапроса, со значением на другой стороне оператора сравнения.. Вы можете использовать следующие операторы сравнения:

Описание
= Равно
> Больше, чем
> = Больше или равно
Меньше
Меньше или равно
! = Не равно в
Не равно
NULL-безопасный оператор равенства

Например, предположим, что вы хотите найти сотрудника id, first_name, last_name и оклады сотрудников, средняя зарплата которых выше, чем средняя зарплата по всей компании.

 mysql> ВЫБРАТЬ ID сотрудника, имя, фамилию, зарплату ОТ сотрудников, ГДЕ зарплата> (ВЫБРАТЬ СРЕДНЮЮ (ЗАПЧАСТЬ) ОТ сотрудников);  + ------------- + ------------- + ------------ + --------  - + |  employee_id |  first_name |  last_name |  зарплата | + ------------- + ------------- + ------------ + ------  ---- + |  100 |  Стивен |  Король |  24000.00 ||  101 |  Нина |  Кочхар |  17000.00 ||  102 |  Лекс |  Де Хаан |  17000.00 ||  103 |  Александр |  Хунольд |  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 (подзапрос) 

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 подзапрос, оператор ALL

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

 mysql> SELECT Department_id, AVG (SALARY  ) ИЗ ГРУППЫ СОТРУДНИКОВ ПО идентификатору отдела ИМЕЮЩИЕ СРЕДНЮЮ (ЗАРПЛАТУ)> = ВСЕ (ВЫБРАТЬ СРЕДНЮЮ (ЗАПЛАТА) ИЗ ГРУППЫ СОТРУДНИКОВ ПО идентификатору отдела); + --------------- + ------  -------- + |  Department_id |  СРЕДНЯЯ (ЗАРПЛАТА) | + --------------- + -------------- + |  90 |  19333. 333333 | + --------------- + -------------- + 1 строка в наборе (0,00 сек) 

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

Оператор ANY сравнивает значение с каждым значением, возвращаемым подзапросом. Поэтому ключевое слово ANY (которое должно следовать за оператором сравнения) возвращает TRUE, если сравнение TRUE для ЛЮБОГО из значений в столбце, возвращаемого подзапросом.

Синтаксис:

 оператор сравнения операндов ANY (подзапрос) 

Пример: подзапрос MySQL, оператор ANY

Следующий запрос выбирает любого сотрудника, который работает в местоположение 1800. Подзапрос находит идентификатор отдела в местоположении 1800, а затем основной запрос выбирает сотрудников, которые работают в любом из этих отделов.

таблица сотрудников :

таблица отделов :

 mysql> ВЫБРАТЬ имя, фамилию, идентификатор отдела ИЗ сотрудников, ГДЕ Department_id = ЛЮБОЙ (ВЫБРАТЬ ИД ОТДЕЛА ИЗ отделов, ГДЕ location_id = 1800); + ------------ + -  ---------- + --------------- + |  first_name |  last_name |  Department_id | + ------------ + ----------- + --------------- + |  Майкл |  Хартштейн |  20 ||  Пэт |  Фэй |  20 | + ------------ + ----------- + --------------- + 2 строки в наборе (0.00  sec) 

Примечание. Мы использовали ЛЮБОЕ ключевое слово в этом запросе, потому что вполне вероятно, что подзапрос найдет более одного отдела в 1800 местоположениях. Если вы используете ключевое слово ALL вместо ключевого слова ANY, данные не выбираются, потому что ни один сотрудник не работает во всех отделах местоположения 1800

При использовании с подзапросом слово IN (равно любому члену список) является псевдонимом для = ЛЮБОЙ. Таким образом, следующие два оператора одинаковы:

Код:

  ВЫБРАТЬ c1 ИЗ t1, ГДЕ c1 = ANY (ВЫБРАТЬ c1 ИЗ t2);  ВЫБРАТЬ c1 ИЗ t1, ГДЕ c1 IN (ВЫБРАТЬ c1 ИЗ t2);  

Слово НЕКОТОРЫЕ является псевдонимом для ЛЮБОГО. Таким образом, эти два оператора одинаковы:

Код:

  SELECT c1 FROM t1 WHERE c1 ANY (SELECT c1 FROM t2); SELECT c1  ОТ t1 ГДЕ c1 НЕКОТОРЫЕ (ВЫБЕРИТЕ c1 ИЗ 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.
  • Если table2 строки col3 и col4 значения не равны значениям col1 и col2 любой строки table1, выражение равно FALSE, и запрос возвращает пустой набор результатов. Выражение неизвестно (то есть NULL), если подзапрос не создает строк.
  • Ошибка возникает, если подзапрос создает несколько строк, потому что подзапрос строки может вернуть не более одной строки.

Пример: подзапросы строк MySQL

В следующих примерах запросы показывают другой результат в соответствии с вышеуказанными условиями:

таблица отделов :

таблица сотрудников :

 mysql> ВЫБРАТЬ first_name ИЗ сотрудников WHERE ROW (Department_id, manager_id) = (ВЫБРАТЬ Department_id, manager_id ИЗ отделов WHERE location_id = 1800); + -  ---------- + |  first_name | + ------------ + |  Pat | + ------------ + 1 строка в наборе (0,00 сек) 

Код:

  mysql  > ВЫБРАТЬ first_name FROM employeeWHERE ROW (Department_id, manager_id) = (SELECT Department_id, manager_id FROM sizes WHERE location_id = 2800); Пустой набор (0,00 сек)  

Код:

  mysql> ВЫБЕРИТЕ first_name ИЗ сотрудников WHERE ROW (Department_id, manager_id) = (SELECT Department_id, manager_id FROM sizes WHERE location_id = 1700); ERROR 1242 (21000): Subquery returns more than 1 row  

Подзапросы MySQL с EXISTS или NOT EXISTS

Оператор EXISTS проверяет наличие строк в наборе результатов подзапроса. Если найдено значение строки подзапроса, подзапрос EXISTS имеет значение ИСТИНА, а в этом случае подзапрос НЕ СУЩЕСТВУЕТ — ЛОЖЬ.

 ВЫБРАТЬ столбец1 ИЗ таблицы1, ГДЕ СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ таблицы2); 

В приведенном выше утверждении, если таблица2 содержит какие-либо строки, даже строки со значениями NULL, условие EXISTS — ИСТИНА. Как правило, подзапрос EXISTS начинается с SELECT *, но он может начинаться с SELECT ‘X’, SELECT 5, SELECT column1 или чего-либо еще. MySQL игнорирует список SELECT в таком подзапросе, поэтому это не имеет значения.

Пример: подзапросы MySQL с EXISTS

Из следующих таблиц (сотрудники) найдите сотрудников ( employee_id, first_name, last_name, job_id, department_id), которым подчиняется хотя бы один человек.

таблица сотрудников :

 ВЫБРАТЬ employee_id, first_name, last_name, job_id, department_id ИЗ сотрудников E ГДЕ СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ сотрудников WHERE manager_id = E. идентификатор_ сотрудника); + ------------- + ------------ + ----------- + -------  - + --------------- + |  employee_id |  first_name |  last_name |  job_id |  ID_отдела | + ------------- + ------------ + ----------- + --------  - + --------------- + |  100 |  Стивен |  Король |  AD_PRES |  90 ||  101 |  Нина |  Кочхар |  AD_VP |  90 ||  102 |  Лекс |  Де Хаан |  AD_VP |  90 ||  103 |  Александр |  Хунольд |  IT_PROG |  60 ||  108 |  Нэнси |  Гринберг |  FI_MGR |  100 ||  114 |  Den |  Рафаэли |  PU_MAN |  30 ||  120 |  Мэтью |  Вайс |  ST_MAN |  50 ||  121 |  Адам |  Фрипп |  ST_MAN |  50 ||  ---------- |  ---------- |  --------- |  ------- |  ------------- | + ------------- + ------------ + --------  --- + --------- + --------------- + 18 строк в наборе (0,02 сек) 

Пример: MySQL Подзапросы с НЕ СУЩЕСТВУЕТ

Подзапрос НЕ СУЩЕСТВУЕТ почти всегда содержит корреляции. Вот пример:
В следующей таблице (отделы и сотрудники) найдите все отделы (идентификатор_отдела, название_отдела), в которых нет сотрудников.

таблица отделов :

таблица сотрудников :

 mysql> ВЫБРАТЬ идентификатор_отдела, имя_отдела ИЗ отделов d ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ сотрудников ГДЕ Department_id = d.department_id); + -----------  ---- + ---------------------- + |  Department_id |  название_отдела | + --------------- + ---------------------- + |  120 |  Казначейство ||  130 |  Корпоративный налог ||  140 |  Контроль и кредит ||  150 |  Услуги для акционеров ||  160 |  Преимущества ||  170 |  Производство ||  180 |  Строительство ||  190 |  Контрактная ||  200 |  Операции ||  ------------ |  -------------------- |  + --------------- + ---------------------- + 16 строк в наборе (0,00 сек) 

Коррелированные подзапросы MySQL

Коррелированный подзапрос — это подзапрос, содержащий ссылку на таблицу (в родительском запросе), которая также появляется во внешнем запросе. MySQL оценивает изнутри и снаружи.

Синтаксис коррелированного подзапроса:

Пример — 1: Коррелированные подзапросы MySQL

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

таблица сотрудников :

 mysql> ВЫБЕРИТЕ last_name, salary, department_id ОТ сотрудников externalrWHERE  зарплата> (ВЫБЕРИТЕ СРЕДНЮЮ (зарплату) ОТ сотрудников ГДЕ Department_id = outerr.department_id); + ----------- + ---------- + --------  ------- + |  last_name |  зарплата |  Department_id | + ----------- + ---------- + --------------- + |  Король |  24000.00 |  90 ||  Хунольд |  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

Из таблиц сотрудников и job_history отобразите сведения о тех сотрудниках, которые изменились вакансии хотя бы один раз.

таблица сотрудников :

таблица истории_работы :

 mysql> ВЫБЕРИТЕ имя_начала, фамилию, идентификатор_работника, идентификатор_задания ИЗ сотрудников E WHERE 1  

Подзапросы MySQL в предложении FROM

Подзапросы работают в FROM оператора SELECT пункт. Синтаксис:

 SELECT ... FROM (подзапрос) [AS] имя ... 

Каждая таблица в предложении FROM должна иметь имя, поэтому Пункт имени [AS] является обязательным. Все столбцы в списке выбора подзапроса должны иметь уникальные имена.

Пример: подзапросы MySQL в предложении FROM

У нас есть следующая таблица tb1.

 mysql> СОЗДАТЬ ТАБЛИЦУ 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; + -----  - + ------ + ------ + |  c1 |  c2 |  c3 | + ------ + ------ + ------ + |  1 |  1 |  1 ||  2 |  2 |  2 ||  3 |  3 |  3 | + ------ + ------ + ------ + 3 строки в наборе (0,00 сек) 

Вот как использовать подзапрос в Предложение FROM, используя пример таблицы (tb1):

 mysql> SELECT sc1, sc2, sc3 FROM (SELECT c1 AS sc1, c2 AS sc2, c3 * 3 AS sc3 FROM tb1) AS sb WHERE  sc1> 1; + ------ + ------ + ------ + |  sc1 |  sc2 |  sc3 | + ------ + ------ + ------ + |  2 |  2 |  6 ||  3 |  3 |  9 | + ------ + ------ + ------ + 2 строки в наборе (0,02 сек) 

Оцените статью
nanomode.ru
Добавить комментарий