Содержание

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

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

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

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

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

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

    SELECT P.pnum, pname, dnum, volume
    FROM P, PD
    WHERE P.pnum=PD.pnum
    
  • Условие соединения указывается в предложении 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.

pnamednamedpricevolumecost
ИвановБолт10 руб.1001000 руб.
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 (правой таблицы).

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

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

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

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

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

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

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

НомерНаименование
5Орлов

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

SELECT *
FROM P1
UNION
SELECT *
FROM P
WHERE pnum=1
ORDER BY Номер
НомерНаименование
1Иванов
5Орлов

Пример 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, который выполняется аналогично оператору UNION.

Пример 7.
Вывести номера деталей, которые поставляет и поставщик с номером 1, и поставщик с номером 2.
SELECT dnum
 FROM PD
 WHERE pnum=1
INTERSECT
SELECT dnum
 FROM PD
 WHERE pnum=2

Вычитание выполняется с помощью оператора 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

Комментарии