PostgreSQL подзапросы
Вступление
Подзапрос - это SQL-запрос, вложенный в более крупный запрос.
- Подзапрос может появляться в:
- В PostgreSQL подзапрос может быть вложен внутри оператора SELECT, INSERT, UPDATE, DELETE, SET или DO или внутри другого подзапроса.
- Подзапрос обычно добавляется в предложении WHERE другого оператора SQL SELECT.
- Вы можете использовать операторы сравнения, такие как>, <или =. Оператор сравнения также может быть оператором из нескольких строк, таким как IN, ANY, SOME или ALL.
- Подзапрос может рассматриваться как внутренний запрос, который представляет собой SQL-запрос, размещенный как часть другого запроса, называемого внешним запросом.
- Внутренний запрос выполняется первым перед его родительским запросом, чтобы результаты внутреннего запроса могли быть переданы во внешний запрос.
Содержание:
- Синтаксис подзапроса
- Пример подзапроса PostgreSQL
- Подзапросы: рекомендации и типы подзапросов
- PostgreSQL подзапрос как скалярный операнд
- Подзапросы PostgreSQL: использование сравнений
- Подзапросы PostgreSQL с ALL, ANY, IN или SOME
- Подзапросы PostgreSQL с EXISTS
- PostgreSQL Row Subqueries
- PostgreSQL коррелированные подзапросы
- Подзапросы PostgreSQL в предложении FROM
Синтаксис подзапроса:
- Подзапрос (внутренний запрос) выполняется один раз перед выполнением основного запроса (внешнего запроса).
- Основной запрос (внешний запрос) использует результат подзапроса.
Пример подзапроса PostgreSQL:
Используя подзапрос, перечислите имена сотрудников, заплативших больше, чем «Александр» из сотрудников.
Код:
SELECT first_name,last_name, salary FROM employees
WHERE salary >
(SELECT max(salary) FROM employees
WHERE first_name='Alexander');
Пример вывода:
имя_файла | фамилия | оплата труда ----------- + ----------- + ---------- Стивен | Король | 24000,00 Нина | Коххар | 17000,00 Лекс | Де Хаан | 17000,00 Нэнси | Гринберг | 12000,00 Ден | Рафаэли | 11000,00 Джон | Рассел | 14000,00 Карен | Партнеры | 13500,00 Альберто | Эрразуриз | 12000,00 Джеральд | Камбро | 11000,00 Элени | Злотый | 10500,00 Питер | Такер | 10000,00 Дэвид | Бернштейн | 9500,00 Джанетт | Король | 10000,00 ..............
Подзапросы: Руководство
Есть несколько рекомендаций, которые следует учитывать при использовании подзапросов:
- Подзапрос должен быть заключен в скобки.
- Используйте однострочные операторы с однорядными подзапросами и используйте многострочные операторы с многострочными подзапросами.
- Если подзапрос (внутренний запрос) возвращает нулевое значение для внешнего запроса, внешний запрос не будет возвращать никаких строк при использовании определенных операторов сравнения в предложении WHERE.
Типы подзапросов
- Подзапрос как скалярный операнд
- Сравнение с использованием подзапросов
- Подзапросы с ALL, ANY, IN или SOME
- Подзапросы строки
- Подзапросы с EXISTS или NOT EXISTS
- Коррелированные подзапросы
- Подзапросы в предложении FROM
PostgreSQL подзапрос как скалярный операнд
Скалярный подзапрос - это подзапрос, который возвращает ровно одно значение столбца из одной строки.
- Запрос SELECT выполняется, и единственное возвращаемое значение используется в выражении окружающего значения.
- Ошибочно использовать запрос, который возвращает более одной строки или более одного столбца в качестве скалярного подзапроса.
- Если во время определенного выполнения подзапрос не возвращает строк, это не является ошибкой; скалярный результат принимается равным нулю.
- Подзапрос может ссылаться на переменные из окружающего запроса, которые будут действовать как константы во время любой оценки подзапроса.
Примерp: Подзапрос PostgreSQL как скалярный операнд
Код:
SELECT employee_id, last_name,
(CASE WHEN department_id=(
SELECT department_id from departments WHERE location_id=2500)
THEN 'Canada' ELSE 'USA' END)
FROM employees;
Пример вывода:
employee_id | фамилия | дело
------------- + ------------- + --------
100 | Король | Соединенные Штаты Америки
101 | Коххар | Соединенные Штаты Америки
102 | Де Хаан | Соединенные Штаты Америки
103 | Hunold | Соединенные Штаты Америки
104 | Эрнст | Соединенные Штаты Америки
105 | Остин | Соединенные Штаты Америки
106 | Патабала | Соединенные Штаты Америки
107 | Лоренц | Соединенные Штаты Америки
108 | Гринберг | Соединенные Штаты Америки
109 | Фавиет | Соединенные Штаты Америки
......................
107 строк в наборе (0,00 сек)
Подзапросы PostgreSQL: использование сравнений
Подзапрос может использоваться до или после любого из операторов сравнения. Подзапрос может возвращать не более одного значения. Значение может быть результатом арифметического выражения или функции столбца. Затем SQL сравнивает значение, полученное в подзапросе, со значением на другой стороне оператора сравнения. Вы можете использовать следующие операторы сравнения:
| оператор | Описание |
|---|---|
| знак равно | Равно |
| > | Лучше чем |
| > = | Больше или равно |
| < | Меньше, чем |
| <= | Меньше или равно |
| знак равно | Не равно |
| <> | Не равно |
| <=> | NULL-сейф равен оператору |
Например, предположим, что вы хотите найти идентификатор сотрудника, имя, фамилию и фамилию, а также зарплаты для сотрудников, средняя зарплата которых выше, чем средняя зарплата по компании.
Код:
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE salary >
(SELECT AVG(SALARY) FROM employees);
Пример вывода:
employee_id | имя_файла | фамилия | оплата труда
------------- + ------------- + ------------ + --------- -
100 | Стивен | Король | 24000,00
101 | Нина | Коххар | 17000,00
102 | Лекс | Де Хаан | 17000,00
103 | Александр | Hunold | 9000,00
108 | Нэнси | Гринберг | 12000,00
109 | Даниэль | Фавиет | 9000,00
110 | Джон | Чен | 8200,00
111 | Исмаэль | Sciarra | 7700,00
112 | Жозе Мануэль | Урман | 7800,00
113 | Луис | Попп | 6900,00
....................
....................
Подзапросы PostgreSQL с оператором ALL
Синтаксис:
оператор выражения ALL (подзапрос)
Оператор ALL сравнивает значение с каждым значением, возвращаемым подзапросом. Правая часть - это вложенный в скобки подзапрос, который должен возвращать ровно один столбец. Левое выражение оценивается и сравнивается с каждой строкой результата подзапроса с использованием данного оператора, который должен давать логический результат.
- Результат ALL равен true, если все строки возвращают true (включая случай, когда подзапрос не возвращает строк).
- Результат ложен, если найден какой-либо ложный результат.
- Результат равен NULL, если сравнение не возвращает false для какой-либо строки и возвращает NULL как минимум для одной строки.
Пример: подзапрос PostgreSQL, оператор ALL
Следующий запрос выбирает отдел с самой высокой средней зарплатой. Подзапрос находит среднюю зарплату для каждого отдела, а затем основной запрос выбирает отдел с самой высокой средней зарплатой.
Код:
SELECT department_id, AVG(SALARY)
FROM employees GROUP BY department_id
HAVING AVG(SALARY)>=ALL
(SELECT AVG(SALARY) FROM employees
GROUP BY department_id);
Пример вывода:
отдел_ид | средний
--------------- + --------------------
90 | +19333,333333333333
(1 ряд)
Примечание. Здесь мы использовали ключевое слово ALL для этого подзапроса, поскольку выбранный запросом отдел должен иметь среднюю зарплату, превышающую или равную средней заработной плате других отделов.
Подзапросы PostgreSQL с оператором ANY / SOME
Синтаксис:
оператор выражения ЛЮБОЙ (подзапрос) оператор выражения SOME (подзапрос)
ЛЮБОЙ оператор сравнивает значение с каждым значением, возвращаемым подзапросом. Поэтому ключевое слово ANY (которое должно следовать за оператором сравнения) возвращает TRUE, если сравнение равно TRUE для ЛЮБОГО значения в столбце, который возвращает подзапрос.
НЕКОТОРЫЙ является синонимом для ЛЮБОГО. IN эквивалентно = ЛЮБОМУ.
Пример: PostgreSQL Subquery, ЛЮБОЙ оператор
Следующий запрос выбирает любого сотрудника, который работает в местоположении 1700. Подзапрос находит идентификатор отдела в расположении 1700, а затем основной запрос выбирает сотрудников, которые работают в любом из этих отделов.
Таблица отделов:
Код:
SELECT first_name, last_name,department_id
FROM employees
WHERE department_id= ANY
(SELECT DEPARTMENT_ID
FROM departments WHERE location_id=1700);
Пример вывода:
имя_файла | фамилия | department_id ------------- + ------------ + --------------- Стивен | Король | 90 Нина | Коххар | 90 Лекс | Де Хаан | 90 Нэнси | Гринберг | 100 Даниэль | Фавиет | 100 Джон | Чен | 100 Исмаэль | Sciarra | 100 Жозе Мануэль | Урман | 100 Луис | Попп | 100 Ден | Рафаэли | 30 Александр | Ху | 30 Шелли | Байда | 30 Сигал | Тобиас | 30 Парень | Химуро | 30 Карен | Кольменарес | 30 Дженнифер | Кит | 10 Шелли | Хиггинс | 110 Уильям | Гитц | 110 (18 рядов)
Примечание. Мы использовали ЛЮБОЕ ключевое слово в этом запросе, поскольку вполне вероятно, что подзапрос найдет более одного отдела в 1700 местах. Если вы используете ключевое слово ALL вместо ключевого слова ANY, никакие данные не будут выбраны, потому что ни один сотрудник не работает во всех отделениях с местоположением 1700
Подзапросы PostgreSQL с оператором IN
Синтаксис:
выражение IN (подзапрос)
Правая часть - это вложенный в скобки подзапрос, который должен возвращать ровно один столбец. Левое выражение оценивается и сравнивается с каждой строкой результата подзапроса.
- Результат IN равен true, если найдена какая-либо одинаковая строка подзапроса.
- Результатом является «false», если не найдено ни одной равной строки (включая случай, когда подзапрос не возвращает строк).
- Если левое выражение возвращает ноль или если нет равных правых значений и хотя бы одна правая строка возвращает ноль, результатом конструкции IN будет ноль, а не ложь.
Пример: подзапрос PostgreSQL, оператор IN
Следующий запрос выбирает тех сотрудников, которые работают в расположении 1800. Подзапрос находит идентификатор отдела в расположении 1800, а затем основной запрос выбирает сотрудников, которые работают в любом из этих отделов.
Код:
SELECT first_name, last_name,department_id
FROM employees
WHERE department_id IN
(SELECT DEPARTMENT_ID FROM departments
WHERE location_id=1800);
Пример вывода:
------------ + ----------- + --------------- Майкл | Хартштейн | 20 Пэт | Фэй | 20 (2 ряда)
Подзапросы PostegreSQL с оператором NOT IN
Синтаксис:
выражение NOT IN (подзапрос)
Правая часть - это вложенный в скобки подзапрос, который должен возвращать ровно один столбец. Левое выражение оценивается и сравнивается с каждой строкой результата подзапроса.
- Результат NOT IN равен true, если найдена какая-либо одинаковая строка подзапроса.
- Результатом является «false», если не найдено ни одной равной строки (включая случай, когда подзапрос не возвращает строк).
- Если левое выражение возвращает ноль или если нет равных правых значений и хотя бы одна правая строка возвращает ноль, результатом конструкции IN будет ноль, а не ложь.
Пример: подзапрос PostgreSQL, оператор NOT IN
Следующий запрос выбирает тех сотрудников, которые не работают в тех отделах, где работают менеджеры ID от 100 до 200. Подзапрос находит идентификатор отдела, который находится под менеджером с идентификатором от 100 до 200, а затем основной запрос выбирает сотрудников, которые не работают ни в одном из этих отделов.
Код:
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);
Пример вывода:
имя_файла | фамилия | department_id ------------ + ----------- + --------------- Кимберели | Грант | 0 Майкл | Хартштейн | 20 Пэт | Фэй | 20 Сьюзен | Маврис | 40 Германн | Баер | 70 Шелли | Хиггинс | 110 Уильям | Гитц | 110 (7 рядов)
Подзапросы PostgreSQL с оператором EXISTS
Синтаксис:
EXISTS (подзапрос)
Аргумент EXISTS - это произвольный оператор SELECT или подзапрос. Подзапрос оценивается, чтобы определить, возвращает ли он какие-либо строки. Если он возвращает хотя бы одну строку, результат EXISTS равен true; если подзапрос не возвращает строк, результатом EXISTS будет false.
Пример: подзапросы PostgreSQL с EXISTS
Следующий запрос находит сотрудников (employee_id, first_name, last_name, job_id, Department_id) из таблицы сотрудников, у которых есть хотя бы один человек, который сообщает о них.
Код:
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);
Пример вывода:
employee_id | имя_файла | фамилия | job_id | department_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
122 | Payam | Кауфлинг | ST_MAN | 50
123 | Шанта | Фольман | ST_MAN | 50
............
18 рядов в наборе (0,02 сек) PostgreSQL Row Subqueries
Подзапрос строки - это подзапрос, который возвращает одну строку и более одного значения столбца. Вы можете использовать операторы сравнения =,>, <,> =, <=, <>,! =, <=>. Смотрите следующие примеры:
Синтаксис:
оператор row_constructor (подзапрос)
Пример: подзапросы PostgreSQL Row
В следующих примерах запросы показывают другой результат в соответствии с вышеуказанными условиями:
Код:
SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) =
(SELECT department_id, manager_id
FROM departments
WHERE location_id = 1800);
Пример вывода:
имя ------------ похлопывание (1 ряд)
Код:
SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) =
(SELECT department_id, manager_id
FROM departments
WHERE location_id = 2800);
Пример вывода:
имя ------------ (0 строк)
Код:
SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) =
(SELECT department_id, manager_id
FROM departments
WHERE location_id = 1700);
Пример вывода:
ОШИБКА: более одной строки, возвращенной подзапросом, используемым в качестве выражения
PostgreSQL коррелированные подзапросы
Коррелированный подзапрос - это подзапрос, который содержит ссылку на таблицу (в родительском запросе), которая также появляется во внешнем запросе. PostgreSQL оценивает изнутри наружу.
Коррелированный синтаксис подзапроса:
Пример - 1: коррелированные подзапросы PostgreSQL
Следующий запрос найти всех сотрудников, которые зарабатывают больше, чем средняя зарплата в их отделе.
Код:
SELECT last_name, salary, department_id
FROM employees outerr
WHERE salary>
(SELECT AVG(salary)
FROM employees
WHERE department_id = outerr.department_id);
Пример вывода:
фамилия | зарплата | 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 Кауфлинг | 7900,00 | 50 Фольман | 6500,00 | 50 Моргос | 5800,00 | 50 ....................
Пример 2: коррелированные подзапросы PostgreSQL
Из таблиц employee и job_history отображаются сведения о тех сотрудниках, которые сменили работу хотя бы один раз.
таблица job_history:
Код:
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);
Пример вывода:
имя_файла | фамилия | 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 рядов)
Подзапросы PostgreSQL в предложении FROM
Подзапросы работают в предложении оператора SELECT FROM. Синтаксис:
SELECT ... FROM (подзапрос) [AS] имя ...
Каждая таблица в предложении FROM должна иметь имя, поэтому предложение имени [AS] является обязательным. Любые столбцы в списке выбора подзапроса должны иметь уникальные имена.
Пример: подзапросы PostgreSQL в предложении FROM
У нас есть следующая таблица tb1.
Код:
CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT);
СОЗДАТЬ СТОЛ
Давайте вставим некоторые значения в таблицу tb1.
Код:
INSERT INTO tb1 VALUES (1, '1', 1.0);
Пример вывода:
ВСТАВИТЬ 0 1
Код:
INSERT INTO tb1 VALUES (2, '2', 2.0);
Пример вывода:
ВСТАВИТЬ 0 1
Код:
INSERT INTO tb1 VALUES (3, '3', 3.0);
Пример вывода:
ВСТАВИТЬ 0 1
Код:
SELECT * FROM tb1;
Пример вывода:
с1 | с2 | c3 ---- + ------- + ---- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 (3 ряда)
Вот как использовать подзапрос в предложении FROM, используя таблицу примеров (tb1):
Код:
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 ряда)
Предыдущий: ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
Далее: ВИДЫ
Новый контент: Composer: менеджер зависимостей для PHP , R программирования