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

Оператор JOIN

JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в раздел FROM операторов SELECT, UPDATE или DELETE.

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

SELECT *
FROM P, PD

Существует два способа соединения таблиц:

1) Условие соединения указывается в предложении WHERE

Пример 1.
Пусть требуется вывести информацию о поставках в виде отношения R(pnum, pname, dnum, volume).‌

SELECT P.pnum, pname, dnum, volume
FROM P, PD
WHERE P.pnum=PD.pnum

2) Условие соединения указывается в предложении FROM

Синтаксис такого соединения следующий:

FROM <таблица1>
INNER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN <таблица2>
ON <таблица_1>.<столбец_1>=<таблица_2><столбец_2>

В соответствии с приведенным выше синтаксисом возможны следующие варианты соединения таблиц:

• Внутреннее соединение
• Внешнее соединение(левое, правое и полное)

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

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

SELECT pname, dnum, volume
FROM P INNER JOIN PD ON P.pnum=PD.pnum

Соединять можно и более двух таблиц.

Пример 2.
Пусть требуется вывести информацию о поставках в виде отношения R(pname, dname, volume).

SELECT pname, dname, volume
FROM (P INNER JOIN PD ON P.pnum=PD.pnum)
INNER JOIN D ON PD.dnum=D.dnum

Пример 3.
Пусть требуется вывести информацию о поставках в виде отношения R(pname, dname, dprice, volume, cost), где значения поля стоимость поставки cost определяются как dprice*volume.

SELECT pname, dname, str(dprice)+’руб.’ AS dprice, volume, str(dprice*volume)+’руб.’ AS cost
FROM (P INNER JOIN PD ON P.pnum=PD.pnum)
INNER JOIN D ON PD.dnum=D.dnum

При построении вычисляемых полей для строковых значений может быть использована операция конкатенации (соединения) строк, которая записывается с помощью символа & или +. Эта операция была использована для получения значений вычисляемых полей dprice и cost. Предварительно числовые значения были приведены к строке с помощью функции STR().

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

Различают 3 вида внешних соединений:

• Левое внешнее соединение
• Правое внешнее соединение
• Полное внешнее соединение

Левое внешнее соединение

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

Полное внешние соединение

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

Правое внешнее соединение

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

Оператор UNION – Объединение

Для объединения запросов используется следующий синтаксис:

<запрос1>
UNION [ALL]
<запрос2>

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

Объединяемые запросы должны быть совместимы между собой. В терминологии SQL это обозначает, что:

• Таблицы должны иметь одинаковое количество столбцов.
• Типы данных в соответствующих столбцах должны быть совместимыми.

Объединяемые запросы должны быть совместимы между собой. В терминологии SQL это обозначает, что:

• Таблицы должны иметь одинаковое количество столбцов.
• Типы данных в соответствующих столбцах должны быть совместимыми.

К результату объединения рекомендуется применять предложение ORDER BY, где можно ссылаться только на имена столбцов левого запроса в операторе UNION.

Пример 4.
Пусть задана таблица P1.

Определим результат следующего объединения:

SELECT *
FROM P1
UNION
SELECT *
FROM P
WHERE pnum=1
ORDER BY Номер

Пример 5.
Получить номера деталей, цена которых более 20 рублей или суммарное поставляемое количество более 500 штук.

Запрос разбивается на две части:

• Вывод номеров деталей, цена которых более 200 рублей.
• Вывод номеров деталей, которые поставляются в количестве более 500 штук.

Результирующая таблица получается при объединении двух частей запроса.

SELECT dnum
    FROM D
    WHERE dprice>20
UNION
SELECT dnum
    FROM PD
    GROUP BY dnum
    HAVING sum(volume)>500
    ORDER BY dnum

Пример 6.
Вывести информацию о деталях. В том случае если цена детали не указана вывести ‘цены нет’.

Запрос разбивается на две части:

• Вывод информации о деталях, для которых указана цена.
• Вывод информации о деталях, для которых не указана цена. В этом случае в предложении SELECT вместо атрибута dprice нужно указывать строковую константу ‘цены нет’.

SELECT dnum, dname, dprice
    FROM D
    WHERE dprice IS NOT NULL
UNION
SELECT dnum, dname, 'цены нет'
    FROM PD
    WHERE dprice IS NULL
    ORDER BY dnum

Оператор INTERSECT – Пересечение

Пересечение запросов выполняется с помощью оператора INTERSECT, который выполняется аналогично оператору UNION.

Пример 7.
Вывести номера деталей, которые поставляет и поставщик с номером 1, и поставщик с номером 2.

SELECT dnum
    FROM PD
    WHERE pnum=1
INTERSECT
SELECT dnum
    FROM PD
    WHERE pnum=2

Оператор EXCEPT – Вычитание

Вычитание выполняется с помощью оператора EXCEPT, который выполняется аналогично оператору UNION.

Пример 8.
Вывести номера поставщиков, которые не поставляют детали в настоящее время.

SELECT pnum
    FROM P
EXCEPT
SELECT pnum
    FROM PD

Пример 9.
Вывести номера поставщиков, которые не поставляют деталь № 2.

SELECT pnum
    FROM P
EXCEPT
SELECT pnum
    FROM PD
    WHERE dnum=2

Пример 10.
Вывести номера поставщиков, которые поставляют только деталь № 1.

SELECT pnum
    FROM PD
    WHERE dnum=1
EXCEPT
SELECT pnum
    FROM PD
    WHERE dnum<>1

Заключение

В заключение, операторы JOIN, UNION, INTERSECT и EXCEPT являются важными инструментами при работе с реляционными базами данных.

Оператор JOIN используется для соединения двух таблиц, а операторы UNION, INTERSECT и EXCEPT используются для объединения, пересечения и вычитания данных из двух или более таблиц.

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