2️⃣ Основы выборки

Многотабличные запросы, JOIN

Многотабличные запросы

В предыдущих статьях описывалась работа только с одной таблицей базы данных. В реальности же очень часто приходится делать выборку из нескольких таблиц, каким-то образом объединяя их. В данной статье вы узнаете основные способы соединения таблиц.

Общая структура многотабличного запроса

SELECT поля_таблиц
FROM таблица_1
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2
    ON условие_соединения
[[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_n
    ON условие_соединения]

Как можно увидеть по структуре, соединение бывает:

• внутренним INNER (по умолчанию)
• внешним OUTER, при этом внешнее соединение делится на левое LEFT, правое RIGHT и полное FULL

Вывод всех столбцов из таблицы в многотабличном запросе

Ранее, когда все запросы выполнялись на одной таблице, было достаточно указать символ *, чтобы вывести все поля из этой таблицы. Теперь же, когда таблиц может быть несколько, * будет означать "вывести все столбцы из таблиц, перечисленных в выражении FROM".

В некоторых случаях нам может понадобиться вывести столбцы, принадлежащие только какой-то конкретной таблице. Например, дано соединение таблиц Payments и FamilyMembers, и в итоговую выборку необходимо вывести только поля из таблицы FamilyMembers. Как это сделать? Всё очень просто! Необходимо перед символом * добавить название таблицы:

SELECT FamilyMembers.* FROM Payments
INNER JOIN FamilyMembers
    ON Payments.family_member = FamilyMembers.member_id

Внутреннее соединение INNER JOIN

Внешнее соединение OUTER JOIN

Внешнее соединение может быть трёх типов: левое (LEFT), правое (RIGHT) и полное (FULL). По умолчанию оно является полным.

Главным отличием внешнего соединения от внутреннего является то, что оно обязательно возвращает все строки одной (LEFT, RIGHT) или двух таблиц (FULL).

Внешнее левое соединение (LEFT OUTER JOIN)

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

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

Внешнее правое соединение (RIGHT OUTER JOIN)

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

Внешнее полное соединение (FULL OUTER JOIN)

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

Алгоритм работы полного соединения:

• Формируется таблица на основе внутреннего соединения (INNER JOIN)
• В таблицу добавляются значения, не вошедшие в результат формирования из левой таблицы (LEFT OUTER JOIN)
• В таблицу добавляются значения, не вошедшие в результат формирования из правой таблицы (RIGHT OUTER JOIN)

Соединение FULL JOIN реализовано не во всех СУБД. Например, в MySQL оно отсутствует, однако его можно очень просто эмулировать:

SELECT *
FROM левая_таблица
LEFT JOIN правая_таблица
   ON правая_таблица.ключ = левая_таблица.ключ

UNION ALL

SELECT *
FROM левая_таблица
RIGHT JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
 WHERE левая_таблица.ключ IS NULL

Базовые запросы для разных вариантов объединения таблиц

Схема	Запрос с JOIN
	Получение всех данных из левой таблицы, соединённых с соответствующими данными из правой:```javascript
SELECT поля_таблиц
FROM левая_таблица LEFT JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
```
	Получение всех данных из правой таблицы, соединённых с соответствующими данными из левой:```javascript
SELECT поля_таблиц
FROM левая_таблица RIGHT JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
```
	Получение данных, относящихся только к левой таблице:```javascript
SELECT поля_таблиц
FROM левая_таблица LEFT JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
WHERE правая_таблица.ключ IS NULL
```
	Получение данных, относящихся только к правой таблице:```javascript
SELECT поля_таблиц
FROM левая_таблица RIGHT JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
WHERE левая_таблица.ключ IS NULL
```
	Получение данных, относящихся как к левой, так и к правой таблице:```javascript
SELECT поля_таблиц
FROM левая_таблица INNER JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
```
	Получение всех данных, относящихся к левой и правой таблицам, а также их внутреннему соединению:```javascript
SELECT поля_таблиц
FROM левая_таблица
FULL OUTER JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
```
	Получение данных, не относящихся к левой и правой таблицам одновременно (обратное INNER JOIN):```javascript
SELECT поля_таблиц
FROM левая_таблица
FULL OUTER JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
WHERE левая_таблица.ключ IS NULL
OR правая_таблица.ключ IS NULL
```

И так можно было:

Ограничение выборки, оператор LIMIT

Оператор LIMIT позволяет извлечь определённый диапазон записей из одной или нескольких таблиц.

Общая структура запроса с оператором LIMIT

SELECT поля_выборки
FROM список_таблиц
LIMIT [количество_пропущенных_записей,] количество_записей_для_вывода;

Если не указать количество пропущенных записей, то их отсчёт будет вестись с начала таблицы.

Оператор LIMIT реализован не во всех СУБД, например, в MSSQL для вывода записей с начала таблицы используется оператор TOP, а для тех случаев, когда необходимо сделать отступ от начала таблицы, предназначена конструкция OFFSET FETCH.

Подзапросы

Подзапросы являются одним из самых мощных инструментов 💪 в SQL, который можно использовать в любых видах запросов. В ближайших уроках мы познакомимся с основными типами подзапросов и рассмотрим примеры как их можно использовать.

Подзапрос — это запрос, использующийся в другом SQL запросе. Подзапрос всегда заключён в круглые скобки и обычно выполняется перед основным запросом.

Как и любой другой SQL запрос, подзапрос возвращает результирующий набор, который может быть одним из следующих:

• одна строка и один столбец;
• нескольких строк с одним столбцом;
• нескольких строк с несколькими столбцами.

В зависимости от типа результирующего набора подзапроса определяются операторы, которые могут использоваться в основном запросе.\n

Подзапрос с одной строкой с одним столбцом

В этом уроке давайте более детально остановимся на подзапросе, возвращающем одну строку и один столбец. Данный тип подзапросов также известен как скалярный подзапрос.

Он может использоваться в различных частях основного SQL запроса, но чаще всего он используется в условиях ограничений выборки с помощью операторов сравнения (=, <>, >, <).

Примеры

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

SELECT (SELECT name FROM company LIMIT 1) AS company_name;

company_name
Don_avia

Таким же образом можно использовать скалярные подзапросы для фильтрации строк с помощью WHERE, используя операторы сравнения.

SELECT * FROM FamilyMembers
    WHERE birthday = (SELECT MAX(birthday) FROM FamilyMembers);

member_id	status	member_name	birthday
8	daughter	Wednesday Addams	2005-01-13T00:00:00.000Z

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

Подзапросы с несколькими строками и одним столбцом

Если подзапрос возвращает более одной строки, его нельзя просто использовать с операторами сравнения, как это можно было делать со скалярными подзапросами.

Однако c подзапросами, возвращающими несколько строк и один столбец, можно использовать 3 дополнительных оператора.

Подзапрос и оператор ALL

С помощью оператора ALL мы можем сравнивать отдельное значение с каждым значением в наборе, полученным подзапросом. При этом данное условие вернёт TRUE, только если все сравнения отдельного значения со значениями в наборе вернут TRUE.

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

SELECT 200 > ALL(SELECT price FROM Rooms)

Подзапрос и оператор IN

Оператор IN проверяет входит ли конкретное значение в набор значений. В качестве такого набора как раз может использоваться подзапрос, возвращающий несколько строк с одним столбцом.

Например, если нам необходимо получить всю информацию о владельцах жилья стоимостью больше 150 условных единиц, то это можно сделать следующим образом:

SELECT * FROM Users WHERE id IN (
    SELECT DISTINCT owner_id FROM Rooms WHERE price >= 150
)

Подзапрос и оператор ANY

Условное выражение с ANY имеет схожее поведение, но оно возвращает TRUE, если хотя бы одно сравнение отдельного значения со значением в наборе вернёт TRUE.

Давайте с его помощью напишем такой же запрос, что мы делали с оператором IN: найдём пользователей, которые владеют хотя бы 1 жилым помещением стоимостью более 150.

SELECT * FROM Users WHERE id = ANY (
    SELECT DISTINCT owner_id FROM Rooms WHERE price >= 150
)

Многостолбцовые подзапросы

До этого мы рассматривали лишь подзапросы, возвращающие один столбец. Но мы также можем работать с подзапросами, возвращающими несколько столбцов и несколько строк (производные таблицы).

Сравнение по нескольким столбцам

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

Например, если мы хотим получить информацию о всех бронированиях, в которых цена жилья на момент брони (Reservations.price) соответствует текущей стоимостью жилья (Rooms.price). То мы это можем сделать следующим образом:

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

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

Пример коррелированного подзапроса

Например, следующий коррелируемый подзапрос находит кто и сколько потратил:

SELECT FamilyMembers.member_name, (
    SELECT SUM(Payments.unit_price * Payments.amount)
    FROM Payments
    WHERE Payments.family_member = FamilyMembers.member_id
) AS total_spent
FROM FamilyMembers;

Коррелированный подзапрос отличается от некоррелированного подзапроса тем, что он выполняется не один раз перед выполнением запроса, в который он вложен, а для каждой строки, которая может быть включена в окончательный результат.

Влияние на производительность

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

Обобщённое табличное выражение, оператор WITH

Обобщённое табличное выражение или CTE (Common Table Expressions) - это временный результирующий набор данных, к которому можно обращаться в последующих запросах. Для написания обобщённого табличного выражения используется оператор WITH.

-- Пример использования конструкции WITH
WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot")

SELECT plane, COUNT(plane) AS amount FROM Aeroflot_trips GROUP BY plane;

Синтаксис оператора WITH

WITH название_cte [(столбец_1 [, столбец_2 ] …)] AS (подзапрос)
    [, название_cte [(столбец_1 [, столбец_2 ] …)] AS (подзапрос)] …

Порядок использования оператора WITH:

1. Ввести оператор WITH
2. Указать название обобщённого табличного выражения
3. Опционально: определить названия для столбцов получившегося табличного выражения, разделённых знаком запятой
4. Ввести AS и далее подзапрос, результат которого можно будет использовать в других частях SQL запроса, используя имя, определённое на 2 этапе
5. Опционально: если необходимо более одного табличного выражения, то ставится запятая и повторяются шаги 2-4

Объединение запросов, оператор Union

Результаты выполнения SQL запросов можно объединять. Для этого существует оператор UNION.

Общая структура запроса с оператором UNION

SELECT поля_таблиц FROM список_таблиц ...
UNION [ALL]
SELECT поля_таблиц FROM список_таблиц ... ;

UNION по умолчанию убирает повторения в результирующей таблице. Для отображения с повторением есть необязательный параметр ALL.

• Не путайте операции объединения запросов с операциями объединения таблиц. Для этого служит оператор JOIN.
• Не путайте операции объединения запросов с подзапросами. Подзапросы выполняются для связанных таблиц.

Объединение таблиц оператором UNION выполняется для таблиц никак не связанных, но со схожей структурой.

Для корректной работы UNION нужно, чтобы результирующие таблицы каждого из SQL запросов имели одинаковое число столбцов, с одним и тем же типом данных и в той же самой последовательности.

Существует два других оператора, чьё поведение крайне схоже с UNION:

• INTERSECT Комбинирует два запроса SELECT, но возвращает записи только первого SELECT, которые имеют совпадения во втором элементе SELECT.
• EXCEPT Комбинирует два запроса SELECT, но возвращает записи только первого SELECT, которые не имеют совпадения во втором элементе SELECT.

Условная логика, оператор CASE

SQL, подобно многим языкам программирования, позволяет писать условную логику, чтобы в зависимости от набора условий возвращать одно из множества возможных значений. В этой статье мы рассмотрим как это реализуется в SQL с помощью оператора CASE.

Что такое условная логика?

Под условной логикой понимается наличие у программы нескольких путей выполнения в зависимости от каких-то условий.

Например, в базе данных «Расписание» есть таблица Student с полем birthday, отражающим дату рождения студента. Допустим, в выборке необходимо отобразить не саму дату рождения, а текстовое значение «Совершеннолетний» или «Несовершеннолетний» в зависимости от того, есть ли студенту 18 лет. Это и есть пример условной логики, при которой должно вывестись либо одно значение, либо другое в зависимости от конкретного условия.

Синтаксис поискового выражения CASE

CASE
    WHEN условие_1 THEN возвращаемое_значение_1
    WHEN условие_2 THEN возвращаемое_значение_2
    WHEN условие_n THEN возвращаемое_значение_n
    [ELSE возвращаемое_значение_по_умолчанию]
END

Если условие_1 возвращает истинное значение, то выражение CASE вернёт возвращаемое_значение_1, иначе будет сделана проверка на условие_2 и т.д. Если ни одно из предложенных условий не будет выполнено, то вернётся NULL или возвращаемое_значение_по_умолчанию, если была использована конструкция ELSE.

Добавление данных, оператор INSERT

Для добавления новых записей в таблицу предназначен оператор INSERT. Рассмотрим его общую структуру.

Общая структура запроса с оператором INSERT

MySQL

INSERT INTO имя_таблицы [(поле_таблицы, ...)]
VALUES (значение_поля_таблицы, ...)
| SELECT поле_таблицы, ... FROM имя_таблицы ...

Значения можно вставлять перечислением с помощью слова VALUES, перечислив их в круглых скобках через запятую или c помощью оператора SELECT.

Обновление данных, оператор UPDATE

Для редактирования существующих записей в таблицах существует SQL оператор UPDATE.

Общая структура запроса с оператором UPDATE

UPDATE имя_таблицы
SET поле_таблицы1 = значение_поля_таблицы1,
    поле_таблицыN = значение_поля_таблицыN
[WHERE условие_выборки]