лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
COALESCE : 1. SELECT COALESCE ( m_pc.maker, m_printer.maker ) FROM 2. ( SELECT DISTINCT maker FROM Product WHERE type= 'pc' ) m_pc 3. FULL JOIN 4. ( SELECT DISTINCT maker FROM Product WHERE type= 'printer' ) m_printer 5. ON m_pc.maker = m_printer.maker 6. WHERE m_pc.maker IS NULL OR m_printer.maker IS NULL ; Разумеется, это не единственный способ решения данной задачи. Он лишь демонстрирует замену репрессированного типа соединений. Мне неизвестны СУБД, в которых было бы реализовано соединение UNION JOIN. Ассоциативность и коммутативность соединений Внутреннее и полное внешнее соединения являются как коммутативными, так и ассоциативными, т.е. для них справедливо следующее: 1. A [ FULL | INNER ] JOIN B = B [ FULL | INNER ] JOIN A и 1. ( A [ FULL | INNER ] JOIN B ) [ FULL | INNER ] JOIN С = 2. A [ FULL | INNER ] JOIN ( B [ FULL | INNER ] JOIN С ) Очевидно, что левое/правое соединения не коммутативны, т.к. 1. A LEFT JOIN B = B RIGHT JOIN A но ассоциативны, например: 1. ( A LEFT JOIN B ) LEFT JOIN C = A LEFT JOIN ( B LEFT JOIN C ) С практической точки зрения ассоциативность означает, что мы можем не расставлять скобки, определяющие прядок выполнения соединений. Однако закон ассоциативности, справедливый для однотипных соединений, нарушается, если в одном запросе используются соединения разных типов. Покажем это на примере. 1. WITH a ( a_id ) AS 2. ( SELECT * FROM ( VALUES ( '1' ) , ( '2' ) , ( '3' )) x ( y )) , 3. b ( b_id ) AS 4. ( SELECT * FROM ( VALUES ( '1' ) , ( '2' ) , ( '4' )) x ( y )) , 5. c ( c_id ) AS 6. ( SELECT * FROM ( VALUES ( '5' ) , ( '2' ) , ( '3' )) x ( y )) 7. SELECT a_id, b_id, c_id FROM ( a LEFT JOIN b ON a_id=b_id ) INNER JOIN c ON b_id=c_id 8. UNION ALL 9. SELECT '' , '' , '' 10. UNION ALL 11. SELECT a_id, b_id, c_id FROM a LEFT JOIN ( b INNER JOIN c ON b_id=c_id ) ON a_id=b_id; a_id b_id c_id 2 2 2 1 NULL NULL 2 2 2 3 NULL NULL Результаты двух запросов отделены друг от друга пробельной строкой для удобства. Заметим, что при отсутствии скобок мы получим результат, совпадающий с результатом первого запроса, поскольку соединения будут выполняться в том порядке, в каком они записаны. Эквисоединения Соединения, которые мы рассмотрели ранее и которые преобладают в примерах данного учебника, называются соединениями по предикату. Синтаксис этого вида соединения такой: 1. Таблица_1 <тип соединения> JOIN Таблица_2 ON <предикат> где тип соединения := [INNER] | [OUTER]{LEFT | RIGHT | FULL} Эти соединения являются наиболее общими, т.к. в качестве предиката может быть использовано любое логическое выражение. Именно по этой причине все диалекты поддерживают этот вид соединения. Частным, но часто используемым соединением является эквисоединение - случай, когда предикат представляет собой равенство значений в столбцах соединяемых таблиц. При этом соединяемые столбцы зачастую имеют одинаковые имена, поскольку в соединении участвуют таблицы, связанные внешним ключом. Впрочем, последнее не суть важно, т.к. мы можем переименовать столбцы, если это нам потребуется. Так вот для этого частного случая соединения - эквисоединения по столбцам с одинаковыми именами - имеются отдельные синтаксические формы соединения: естественное соединение и соединение, использующее имена столбцов. Естественное соединение 1. Таблица_1 NATURAL <тип соединения> JOIN Таблица_2 Предикат здесь не нужен, т.к. он подразумевается, а именно попарное равенство всех столбцов с одинаковыми именами в обеих таблицах. Например, если у обеих соединяемых таблиц есть столбцы a и b, то естественное соединение 1. Таблица_1 NATURAL INNER JOIN Таблица_2 будет эквивалентно такому соединению по предикату: 1. Таблица_1 INNER JOIN Таблица_2 ON Таблица_1.a = Таблица_2.a AND Таблица_1.b = Таблица_2.b Кроме того, при естественном соединении одноименные столбцы будут присутствовать в выборке в одном экземпляре. Сравните, например, результаты таких запросов ( база данных Аэрофлот ) 1. SELECT * FROM Pass_in_trip 2. JOIN Passenger ON Pass_in_trip.id_psg = Passenger.id_psg 3. WHERE trip_no= 1123 ; trip_no date id_psg place id_psg name 1123 2003-04-05 00:00:00 3 2a 3 Kevin Costner 1123 2003-04-08 00:00:00 1 4c 1 Bruce Willis 1123 2003-04-08 00:00:00 6 4b 6 Ray Liotta и 1. SELECT * FROM Pass_in_trip 2. NATURAL JOIN Passenger 3. WHERE trip_no= 1123 ; id_psg trip_no date place name 6 1123 2003-04-08 00:00:00 4b Ray Liotta 3 1123 2003-04-05 00:00:00 2a Kevin Costner 1 1123 2003-04-08 00:00:00 4c Bruce Willis Как видно из представленных результатов, столбец id_psg, по которому выполняется соединение, не повторяется для естественного соединения. Из СУБД, доступных на сайте sql-ex.ru , только SQL Server не поддерживает естественное соединение. Если вы хотите поработать с естественным соединением практически, выберите в консоли PostgreSQL или MySQL. Если требуется выполнить эквисоединение не по всем столбцам с совпадающими именами, а только по их части, тогда мы можем использовать соединение USING: 1. Таблица_1 <тип соединения> JOIN Таблица_2 USING ( <список столбцов> ) Список столбцов содержит те столбцы, по которым выполняется эквисоединение. Соответственно, в этом списке могут присутствовать только те из столбцов, имена которых совпадают в обеих соединяемых таблицах. Сравните результаты следующих запросов (предикат в предложении WHERE использован лишь для сокращения размера выборки). Соединение строк из таблиц Income и Outcome по равенству значений в столбце date ( база данных Фирма вторсырья ) 1. SELECT * FROM Income JOIN Outcome USING ( date ) 2. WHERE MONTH ( date ) >= 4 ; date code point inc code point out 2001-04-13 00:00:00 6 1 5000.00 7 1 4490.00 2001-04-13 00:00:00 10 1 5000.00 7 1 4490.00 2001-05-11 00:00:00 7 1 4500.00 9 1 2530.00 2001-09-13 00:00:00 12 3 1350.00 16 3 1200.00 2001-09-13 00:00:00 13 3 1750.00 16 3 1200.00 2001-09-13 00:00:00 12 3 1350.00 17 3 1500.00 2001-09-13 00:00:00 13 3 1750.00 17 3 1500.00 Соединение строк из таблиц Income и Outcome по равенству значений в столбцах date и point 1. SELECT * FROM Income JOIN Outcome USING ( date, point ) 2. WHERE MONTH ( date ) >= 4 ; point date code inc code out 1 2001-04-13 00:00:00 6 5000.00 7 4490.00 1 2001-04-13 00:00:00 10 5000.00 7 4490.00 1 2001-05-11 00:00:00 7 4500.00 9 2530.00 3 2001-09-13 00:00:00 12 1350.00 16 1200.00 3 2001-09-13 00:00:00 13 1750.00 16 1200.00 3 2001-09-13 00:00:00 12 1350.00 17 1500.00 3 2001-09-13 00:00:00 13 1750.00 17 1500.00 Соединение строк из таблиц Income и Outcome по равенству значений в столбцах date, point и code 1. SELECT * FROM Income JOIN Outcome USING ( date, point, code ) ; не возвращает строк. Это соединение по всем столбцам с совпадающими именами эквивалентно естественному соединению 1. SELECT * FROM Income NATURAL JOIN Outcome; Чтобы продемонстрировать вывод в последнем варианте, воспользуемся левым соединением 1. SELECT * FROM Income LEFT JOIN Outcome USING ( date, point, code ) 2. WHERE MONTH ( date ) >= 4 ; code point date inc out 6 1 2001-04-13 00:00:00 5000.00 NULL 7 1 2001-05-11 00:00:00 4500.00 NULL 10 1 2001-04-13 00:00:00 5000.00 NULL 12 3 2001-09-13 00:00:00 1350.00 NULL 13 3 2001-09-13 00:00:00 1750.00 NULL FULL JOIN и MySQL Полное внешнее соединение ( FULL JOIN ) не поддерживается в MySQL. Можно считать, что это – «избыточная» операция, т.к. она представляется через объединение левого и правого внешних соединений. Например, запрос 1. --(1)-- 2. SELECT * FROM Income_o I FULL JOIN Outcome_o O 3. ON I.point = O.point AND I.date = O.date; который на каждый рабочий день по каждому пункту выводит в одну строку приход и расход ( схема «Вторсырье» ), можно переписать в виде: 1. --(2)-- 2. SELECT * FROM Income_o I LEFT JOIN Outcome_o O 3. ON I.point = O.point AND I.date = O.date 4. UNION 5. SELECT * FROM Income_o I RIGHT JOIN Outcome_o O 6. ON I.point = O.point AND I.date = O.date; С логической точки зрения эти запросы эквивалентны; оба они выводят как дни, когда был и приход, и расход, так и дни, когда отсутствовала одна из операций (отсутствующие значения заменяются NULL). Однако с точки зрения производительности второй запрос проигрывает первому вдвое по оценке стоимости плана. Это связано с тем, что операция UNION приводит к выполнению сортировки, которая отсутствует в плане первого запроса. Сортировка же необходима для процедуры исключения дубликатов, т.к. левое и правое соединения оба содержат строки, соответствующие внутреннему соединению, т.е. случаю, когда есть как приход, так и расход. Поэтому, если вместо UNION написать UNION ALL, то такие строки будут присутствовать в результирующем наборе в двух экземплярах. Тем не менее, чтобы получить план, близкий по стоимости FULL JOIN, нужно избавиться от сортировки. Например, использовать UNION ALL, но в одном из объединяемых запросов исключить строки, соответствующие внутреннему соединению: 1. --(3)-- 2. SELECT * FROM Income_o I LEFT JOIN Outcome_o O 3. ON I.point = O.point AND I.date = O.date 4. UNION ALL 5. SELECT NULL , NULL , NULL ,* FROM Outcome_o O 6. WHERE NOT EXISTS ( SELECT 1 FROM Income_o I 7. WHERE I.point = O.point AND I.date = O.date ) ; Обратите внимание, что заведомо отсутствующие значения, которые появлялись в правом соединении решения (2), здесь формируются явным заданием NULL-значений. Если по каким-то причинам, явное задание NULL вместо соединения вам не подходит, можно оставить соединение, но это даст более дорогой план, хотя и он будет дешевле плана с сортировкой (2): 1. SELECT * FROM Income_o I LEFT JOIN Outcome_o O 2. ON I.point = O.point AND I.date = O.date 3. UNION ALL 4. SELECT * FROM Income_o I RIGHT JOIN Outcome_o O 5. ON I.point = O.point AND I.date = O.date 6. WHERE NOT EXISTS ( SELECT 1 FROM Income_o I 7. WHERE I.point = O.point AND I.date = O.date ) ; Декартово произведение Ранее мы уже рассмотрели реализацию декартова произведения ( пункт 5.6 ), состоящую в перечислении через запятую табличных выражений в предложении FROM (таблицы, представления, подзапросы) при отсутствии предложения WHERE, связывающего столбцы из перечисленных источников строк. Кроме того, можно использовать еще и явную операцию соединения – CROSS JOIN, например: 1. SELECT Laptop.model, Product.model 2. FROM Laptop CROSS JOIN 3. Product; Напомним, что при декартовом произведении каждая строка из первой таблицы соединяется с каждой строкой второй таблицы. В результате количество строк результирующего набора равно произведению количества строк операндов декартова произведения. В нашем примере таблица Laptop содержит 5 строк, а таблица Product — 16. В результате получается 5 * 16 = 80 строк. Поэтому мы не приводим здесь результат выполнения этого запроса. Вы можете сами проверить это утверждение, нажав ссылку "выполнить" или выполнив приведенный выше запрос с помощью консоли. Примечание : В чистом виде декартово произведение практически не используется, оно, как правило, является промежуточным результатом выполнения операции горизонтальной проекции (выборки) при наличии в операторе SELECT предложения WHERE. Объединение Для объединения запросов используется служебное слово UNION: 1. <запрос 1 > 2. UNION [ ALL ] 3. <запрос 2 > Предложение UNION приводит к появлению в результирующем наборе всех строк каждого из запросов. При этом, если определен параметр ALL, то сохраняются все дубликаты выходных строк, в противном случае в результирующем наборе присутствуют только уникальные строки. Заметим, что можно связывать вместе любое число запросов. Кроме того, с помощью скобок можно задавать порядок объединения. Операция объединения может быть выполнена только при выполнении следующих условий: количество выходных столбцов каждого из запросов должно быть одинаковым; выходные столбцы каждого из запросов должны быть совместимы между собой (в порядке их следования) по типам данных; в результирующем наборе используются имена столбцов, заданные в первом запросе; предложение ORDER BY применяется к результату соединения, поэтому оно может быть указано только в конце всего составного запроса. Пример 5.7.1 Найти номера моделей и цены ПК и портативных компьютеров: 1. SELECT model, price 2. FROM PC 3. UNION 4. SELECT model, price 5. FROM Laptop 6. ORDER BY price DESC ; model Price 1750 1200 1752 1150 1298 1050 1233 980 1321 970 1233 950 1121 850 1298 700 1232 600 1233 600 1232 400 1232 350 1260 350 Пример 5.7.2 Найти тип продукции, номер модели и цену ПК и портативных компьютеров: 1. SELECT Product.type, PC.model, price 2. FROM PC INNER JOIN 3. Product ON PC.model = Product.model 4. UNION 5. SELECT Product.type, Laptop.model, price 6. FROM Laptop INNER JOIN 7. Product ON Laptop.model = Product.model 8. ORDER BY price DESC ; Type Model price Laptop 1750 1200 Laptop 1752 1150 Laptop 1298 1050 PC 1233 980 Laptop 1321 970 PC 1233 950 PC 1121 850 Laptop 1298 700 PC 1232 600 PC 1233 600 PC 1232 400 PC 1232 350 PC 1260 350 Рассмотрим следующую задачу. Найти все имеющиеся единицы продукции производителя 'B'. Вывести номер модели и тип. В базе имеется один ноутбук и три ПК от производителя B, при этом все три ПК - одной модели. Если мы будем использовать объединение с помощью UNION ALL, то мы получим все эти изделия. 1. SELECT p.model, p.type FROM pc JOIN Product p ON PC.model=p.model WHERE maker= 'B' 2. UNION ALL 3. SELECT p.model, p.type FROM printer pr JOIN Product p ON pr.model=p.model WHERE maker= 'B' 4. UNION ALL 5. SELECT p.model, p.type FROM laptop lp JOIN Product p ON lp.model=p.model WHERE maker= 'B' ; model type 1121 PC 1121 PC 1121 PC 1750 Laptop А если - UNION, то из результата будут исключены дубликаты строк: 1. SELECT p.model, p.type FROM pc JOIN Product p ON PC.model=p.model WHERE maker= 'B' 2. UNION 3. SELECT p.model, p.type FROM printer pr JOIN Product p ON pr.model=p.model WHERE maker= 'B' 4. UNION 5. SELECT p.model, p.type FROM laptop lp JOIN Product p ON lp.model=p.model WHERE maker= 'B' ; |