Традиционные операции над множествами. Пересечение и разность.

Явные операции соединения таблиц.

В предложении from может быть указана явная операция соединения 2-х или более таблиц.

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

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

From <табл.1> inner join <табл.2> on <предикат>

Пример. Найти производителей, номер модели и цену каждого компьютера, имеющегося в БД.

Select maker, pc.model, price
from pc join product on pc.model=product.model

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

2) Внешнее соединение LEFT JOIN означает, что помимо строк для которых выполняется условие предиката в результирующий набор попадут все остальные строки из 1-ой таблицы(левой). При этом отсутствующие значения полей из 1-ой таблицы будут заполнены NULL значениями.

Пример. Вывести все модели pc, их производителей и цену. Поле model указать 2 раза.

Select maker, product.model, pc.model, price
from product left join pc on product.model=pc.model
where type=’pc’

Внешнее соединение RIGHT JOIN. Данное соединение обратно соединению LEFT JOIN. Т.е. в результирующий набор попадут все записи из 2-ой(правой) таблицы, которые соединяются только с теми строками из 1-ой табл, для которых вып-ся условие.
Пример. см предыдущий пример
Select maker, product.model, pc.model, price
from pc right join product on pc.model= product.model
where type=’pc’

3) Полное соединение FULL JOIN

При таком соединении в результирующую табл попадут не только те строки, которые имеют одинаковое значение в сопоставляемых столбцах, но и все остальные строки исходных таблиц, не имеющих соответствующих значений в другой таблице. В этих строках все столбцы той таблицы, в которой не было найдено соответствия заполняются null значениями. Полное соединение – это комбинация левого и правого внешних соединений.
Пример. сделаем полное соединение табл А и В по предикату.
select *.A, *.B
from A full join B on A.a=B.c

Традиционные операции над множествами. Декартово произведение, объединение.

Трад.операции над мн-ми – это объединение, пересечение, разность и декартово произведение.
Декартово произведение. При дек.произведении каждая строка из одной таблицы соединяется с кажд строкой из др таблицы. В рез-те кол-во строк результирующего набора равно произведению кол-ва строк 1-го набора на кол-во строк 2-ого набора.

Ранее уже рассмотрели реализацию декартового произведения, когда в предложении from перечисляли через запятую имена таблиц.

Декартово произведение – cross join

Пример. рассмотрим декартово прoизведение табл laptop и product (выведем модель).
I) select laptop.model, product.model
from laptop cross join product on laprop.model=product.model

Кол-во строк= 6*16=96
или дек произ можно записать так:
II) select laptop.model, product.model
from laptop, product
В чистом виде дек.произ-ие практически не используется, оно яв-ся промежуточным значением горизонтальной проекции.
Объединение. Для объединения запросов исп-ют служ слово union.
общий вид:
<запрoc 1>
union
<запрос 2>

Оператор union объединяет выходные строки каждого из запросов в один результирующий набор. Можно связать любое число запросов. При объединении запросов должны выполнятся след условия:
1) кол-во выходных столбцов кажд из запросов должны быть одинаковыми;
2) выходные столбцы кажд из запросов должны быть сравнимыми между собой(в порядке их следования) по типам данных;
3) в результирующем наборе исп-ся имена столбцов заданные в 1-ом запросе.
пример. найти номера моделей и цены ПК и ПК-блокнотов.
select model, price

from pc
union
select model, price

from laptop

Традиционные операции над множествами. Пересечение и разность.

В стандарте языка SQL имеются предложения для выполнения операций пересечения и разности:
-intersect

-except

Которые работают аналогично как пред union.

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

Для разности в результирующий набор попадут те строки 1-ого запроса, которые отсутствуют во 2-ом.

Однако, многие СУБД не поддерживают эти предложения. Это справедливо и для Microsoft SQL Server. Поэтому для выполнения операций пересечения и разности могут быть использованы другие средства. Напр, оператор EXISTS, который исп-ся с подзапросом.
Предикат EXISTS принимает значение истина, если подзапрос возвращает какое-либо количество строк, иначе его значение=ложь. Для NOT EXISTS все наоборот.
Обычно предикат EXISTS исп-ся в зависимых подзапросах. Этот подзапрос имеет внешнюю ссылку со значением в основном запросе. Результат подзапроса может зависеть от этого значения и должен оценив-ся отдельно для каждой строки запроса, в котором содержится данный подзапрос. Поэтому предикат EXISTS может иметь разные значения для кажд строки основного запроса.
пример(пересечение). Найти тех производителей ПК-блокнотов, которые производят принтеры.
select distinct lap.maker
from product as lap
where type=’laptop’ and
exists (select maker from product where type=’printer’ and maker=lap.maker)

В подзапросе выбираются производители принтеров и срав-ся с производителем, значение которого предается из осн запроса. В осн запрос отбираются производители ПК-блокнотов.

Пример(разность). Найти тех произ-ей ПК-блокнотов, которые не производятпринтеры.

select distinct lap.maker
from product as lap
where type=’laptop’ and
not exists (select maker from product where type=’printer’ and maker=lap.maker)