SQLite подзапросы

Последнее обновление 5 июня 2019 07:34:30 (UTC / GMT +8 часов)

script1adsense2code

script1adsense3code

Вступление

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

Подзапрос может появляться в:

- предложение SELECT
- ОТ условия
- ГДЕ оговорка

Подзапрос обычно добавляется в предложении WHERE другого оператора SQL SELECT.
Вы можете использовать операторы сравнения, такие как>, <или =. Оператор сравнения также может быть оператором из нескольких строк, например IN, NOT IN
Подзапрос может рассматриваться как внутренний запрос, который представляет собой SQL-запрос, размещенный как часть другого запроса, называемого внешним запросом.
Внутренний запрос выполняется первым перед его родительским запросом, чтобы результаты внутреннего запроса могли быть переданы во внешний запрос.

Оглавление

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

Пример подзапроса SQLite

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

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

SQLite подзапросы с IN, а не IN

Подзапросы SQLite с EXISTS

SQLite коррелированные подзапросы

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

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

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

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

Используя подзапрос, перечислите имена сотрудников, которым заплатили больше, чем «Александр» (имя) от сотрудников:

Пример таблицы: сотрудники

«SQLite

Код:

sqlite> SELECT first_name,last_name, salary 
FROM employees 
WHERE salary >(
SELECT salary FROM employees WHERE first_name='Alexander');

Выход:

 Имя Фамилия Зарплата
---------- ---------- ----------
Стивен Кинг 24000
Нина Кочхар 17000
Лекс Де Хаан 17000
Нэнси Гринберг 12000
Ден Рафаэли 11000
Джон Рассел 14000
Карен Партнерс 13500
Альберто Эрразуриз 12000
Джеральд Кэмбро 11000
Элени Злоткей 10500
Питер Такер 10000
Дэвид Бернштейн 9500
Джанет Кинг 10000
Патрик Салли 9500
Клара Вишни 10500
Даниэль Грин 9500
Лиза Озер 11500
Харрисон Блум 10000
Тайлер Фокс 9600
Эллен Абель 11000
Михаэль Хартштейн 13000
Герман Баер 10000
Шелли Хиггинс 12000

Подзапросы: Руководство

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

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

Подзапрос как скалярный операнд
Сравнение с использованием подзапросов
Подзапросы с оператором IN, NOT IN, EXISTS
Коррелированные подзапросы
Подзапросы в предложении FROM

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

Оператор SELECT, заключенный в скобки, может отображаться в виде скалярной величины. Скалярный подзапрос - это подзапрос, который возвращает ровно одно значение столбца из одной строки.

Запрос SELECT выполняется, и единственное возвращаемое значение используется в выражении окружающего значения.
Ошибочно использовать запрос, который возвращает более одной строки или более одного столбца в качестве скалярного подзапроса.
Если во время определенного выполнения подзапрос не возвращает строк, это не является ошибкой; скалярный результат принимается равным нулю.
Подзапрос может ссылаться на переменные из окружающего запроса, которые будут действовать как константы во время любой оценки подзапроса.

Пример:

sqlite> SELECT employee_id, last_name, 
(CASE WHEN department_id=( SELECT department_id from departments 
WHERE location_id=2500) THEN 'Canada' 
ELSE 'USA' END) location 
FROM employees;

Выход:

 employee_id last_name location
----------- ---------- ----------
100 Кинг США
101 Коххар США
102 Де Хаан США
103 Hunold USA
104 Эрнст США
105 Остин США
106 Pataballa США
107 Лоренц США
----------- ---------- ----------
----------- ---------- ----------

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

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

оператор	Описание
знак равно	Тест на равенство.
==	Тест на равенство.
>	Лучше чем.
<	Меньше, чем.
> =	Больше чем равно.
<=	Менее чем равно.
<>	Тест на неравенство.
знак равно	Тест на неравенство.

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

sqlite> SELECT employee_id,first_name,last_name,salary 
FROM employees 
WHERE salary > (SELECT AVG(SALARY) FROM employees);

Выход :

 ИД сотрудника имя_ фамилии зарплата
----------- ---------- ---------- ----------
100 Стивен Кинг 24000
101 Нина Кочхар 17000
102 Лекс Де Хаан 17000
103 Александр Гунольд 9000
108 Нэнси Гринберг 12000
109 Даниэль Фавиет 9000
110 Джон Чен 8200
----------- ---------- ---------- ----------
----------- ---------- ---------- ----------

Подзапросы SQLite с оператором IN

Синтаксис:

 выражение IN (подзапрос)

Правая часть - это вложенный в скобки подзапрос, который должен возвращать ровно один столбец. Левое выражение оценивается и сравнивается с каждой строкой результата подзапроса.

Результат IN равен true, если найдена какая-либо одинаковая строка подзапроса.
Результатом является «false», если не найдено ни одной равной строки (включая случай, когда подзапрос не возвращает строк).
Если левое выражение возвращает ноль или если нет равных правых значений и хотя бы одна правая строка возвращает ноль, результатом конструкции IN будет ноль, а не ложь.

Пример: подзапрос SQLite, оператор IN

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

Пример таблицы: сотрудники

Код:

sqlite> SELECT first_name, last_name,department_id
...> FROM employees
...> WHERE department_id IN
...> (SELECT DEPARTMENT_ID FROM departments
...> WHERE location_id=1700);

Выход:

 имя_фамилия фамилия отдел_идентификатор
 ---------- ---------- -------------
 Стивен Кинг 90
 Neena Kochhar 90
 Лекс Де Хаан 90
 Нэнси Гринберг 100
 Даниэль Фавиет 100
 Джон Чен 100
 Исмаэль Скиарра 100
 Хосе Мануэ Урман 100
 Луис Попп 100
 Ден Рафаэли 30
 Александр Ху 30
 Шелли Байда 30
 Сигал Тобиас 30
 Ги Химуро 30
 Карен Кольменарес 30
 Дженнифер Уэйлен 10
 Шелли Хиггинс 110
 Уильям Гитц 110)

Подзапросы SQLite с оператором NOT IN

Синтаксис:

 выражение NOT IN (подзапрос)

Результат NOT IN равен true, если найдена какая-либо одинаковая строка подзапроса.
Результатом является «false», если не найдено ни одной равной строки (включая случай, когда подзапрос не возвращает строк).
Если левое выражение возвращает ноль или если нет равных правых значений и хотя бы одна правая строка возвращает ноль, результатом конструкции NOT IN будет ноль, а не ложь.

Пример: подзапрос SQLite, оператор NOT IN

В следующем запросе выбираются те сотрудники, которые не работают под руководством менеджера, чей идентификатор находится в диапазоне от 100 до 200. Подзапрос находит идентификатор отдела, который находится под менеджером, чей идентификатор находится в диапазоне от 100 до 200, а затем основной запрос выбирает сотрудников, которые не работайте ни в одном из этих отделов.

Пример таблицы: сотрудники

Пример таблицы: отделы

Код:

sqlite> SELECT first_name, last_name,department_id
...> FROM employees
...> WHERE department_id NOT IN
..> (SELECT DEPARTMENT_ID FROM departments
...> WHERE manager_id
...> BETWEEN 100 AND 200);

Выход:

 
  имя_фамилия фамилия отдел_идентификатор
  ---------- ---------- -------------
  Кимберли Грант
  Михаэль Хартштейн 20
  Пэт Фай 20
  Сьюзен Маврис 40
  Герман Баер 70
  Шелли Хиггинс 110
  Уильям Гитц 110

Подзапросы SQLite с оператором EXISTS

Синтаксис:

 EXISTS (подзапрос)

Аргумент EXISTS - это произвольный оператор или подзапрос SELECT. Подзапрос оценивается, чтобы определить, возвращает ли он какие-либо строки. Если он возвращает хотя бы одну строку, результат EXISTS равен true; если подзапрос не возвращает строк, результатом EXISTS будет false.

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

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

Пример таблицы: таблица сотрудников

Код:

sqlite> SELECT employee_id, first_name, last_name, job_id, department_id
  ...> FROM employees E
  ...> WHERE EXISTS
  ...> (SELECT * FROM employees
  ...> WHERE manager_id = E.employee_id);

Выход:

 идентификатор сотрудника имя_ фамилии фамилия
  ----------- ---------- ---------- ---------- --------- ----
  100 Стивен Кинг AD_PRES 90
  101 Neena Kochhar AD_VP 90
  102 Lex De Haan AD_VP 90
  103 Александр Хунольд IT_PROG 60
  108 Нэнси Гринберг FI_MGR 100
  114 Ден Рафаэли ПУ_МАН 30
  120 Мэтью Вайс ST_MAN 50
  121 Адам Фрипп ST_MAN 50
  122 Payam Kaufling ST_MAN 50
  123 Шанта Воллман ST_MAN 50
  124 Кевин Моргос ST_MAN 50
  145 Джон Рассел SA_MAN 80
  146 Карен Партнерс SA_MAN 80
  147 Альберто Эрразуриз СА_МАН 80
  148 Джеральд Камбро SA_MAN 80
  149 Eleni Zlotkey SA_MAN 80
  201 Майкл Хартштейн MK_MAN 20
  205 Шелли Хиггинс AC_MGR 110

SQLite коррелированные подзапросы

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

Коррелированный синтаксис подзапроса:

«SQLite

Пример - 1: коррелированные подзапросы SQLite

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

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

Код:

sqlite> SELECT last_name, salary, department_id
FROM employees outerr 
WHERE salary >(SELECT AVG(salary) 
FROM employees 
WHERE department_id = outerr.department_id);

Выход:

 фамилия зарплата отдел_ид
 ---------- ---------- -------------
 Кинг 24000 90
 Hunold 9000 60
 Эрнст 6000 60
 Гринберг 12000 100
 Фавиет 9000 100
 Рафаэли 11000 30
 Weiss 8000 50
 Фрипп 8200 50
 Кауфлинг 7900 50
 Фольман 6500 50
 Моргос 5800 50
 Ladwig 3600 50
 Раджс 3500 50
 Рассел 14000 80
 Партнеры 13500 80
 Эрразуриз 12000 80
 Камбро 11000 80
 Zlotkey 10500 80
 Такер 10000 80
 Бернштейн 9500 80
 Зал 9000 80
 Король 10000 80
 Салли 9500 80
 McEwen 9000 80
 Вишней 10500 80
 Грин 9500 80
 Озер 11500 80
 Блум 10000 80
 Fox 9600 80
 Авель 11000 80
 Sarchand 4200 50
 Бык 4100 50
 Чунг 3800 50
 Дилли 3600 50
 Bell 4000 50
 Эверетт 3900 50
 Hartstein 13000 20
 Хиггинс 12000 110

Пример 2: коррелированные подзапросы SQLite

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

Пример таблицы: таблица сотрудников:

Пример таблицы: таблица job_history:

Код:

sqlite> SELECT first_name, last_name, employee_id, job_id
 ...> FROM employees E
 ...> WHERE 1 <=
 ...> (SELECT COUNT(*) FROM Job_history
 ...> WHERE employee_id = E.employee_id);

выход:

 имя_фамилия фамилия сотрудника_идентификатор_работы
 ---------- ---------- ----------- ----------
 Neena Kochhar 101 AD_VP
 Лекс Де Хаан 102 AD_VP
 Ден Рафаэли 114 PU_MAN
 Паям Кауфлинг 122 ST_MAN
 Джонатон Тейлор 176 SA_REP
 Дженнифер Уэйлен 200 AD_ASST
 Михаэль Хартштейн 201 MK_MAN

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

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

SELECT ... FROM (subquery) [AS] name ...

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

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

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

sqlite> CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT);

Давайте вставим некоторые значения в таблицу tb1.

 sqlite> INSERT INTO tb1 VALUES (1, '1', 1.0);
sqlite> INSERT INTO tb1 VALUES (2, '2', 2.0);
sqlite> INSERT INTO tb1 VALUES (3, 3, 3,0);
sqlite> select * from tb1;
с1 с2 с3
---------- ---------- ----------
1 1 1,0
2 2 2.0
3 3 3,0

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

 sqlite> SELECT sc1, sc2, sc3 ОТ (SELECT c1 AS sc1, c2 AS sc2, c3 * 3 AS sc3 ОТ tb1) AS sb ГДЕ sc1> 1; 
 sc1 sc2 sc3 
 ---------- ---------- ---------- 
 2 2 6,0 
 3 3 9,0

Предыдущая: ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ
Далее: Триггеры