Язык SQL. Соединение таблиц
Запросы к нескольким таблицам При проектировании БД стремятся создавать таблицы, в каждой из которых содержалась бы информация об одном типе сущностей. Но затем возникает вопрос: как же получить сведения о продавцах (Salespeople) и покупателях (Customers), связанных между собой заказами (Orders), если нужные данные «рассыпаны» по трем таблицам? SQL обладает механизмом для обработки данных из нескольких взаимосвязанных таблиц. В нем реализованы возможности «соединять» или «объединять» несколько таблиц. В основе этого соединения лежит математическое понятие декартова произведения.
Декартово произведение N таблиц – это таблица, содержащая все возможные строки R, такие, что R является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, . . . и строки из N-й таблицы. Соединения – это подмножества декартова произведения. Пример. Есть 2 множества: (a, b, c) и (d, e, f) Их декартово произведение: n m (a, d); (a, e); (a, f); (b, d); (b, e); (b, f); (c, d); (c, e); (c, f); Результирующий набор содержит n*m пар. В зависимости от количества строк, содержащихся в обеих таблицах, результирующий набор записей будет содержать количество строк, равно N*M, где N – количество строк в таблице A, а M – количество строк в таблице B.
Виды соединений (join) Ø Естественное соединение (natural join) Основано на уже существующих в БД отношениях. SELECT Customers. cname, Salespeople. sname FROM Customers, Salespeople WHERE Salespeople. snume= Customers. snume; Соединение продавцов и их покупателей (по ключам). При соединении SQL просматривает все возможные комбинации строк, т. е. создаёт декартово произведение 2 -х таблиц и из полученного результата выбирает данные на основе предиката. Ø Неестественное соединение (unnatural join) Отношения устанавливаются на основании содержимого БД, а не её структуры. SELECT Customers. cname, Salespeople. sname FROM Customers, Salespeople WHERE Salespeople. city= Customers. city;
Виды соединений (join) Ø Эквисоединения таблиц (equijoin) Используются предикаты с равенствами (все предыдущие примеры). Другие виды: SELECT sname, cname FROM Salespeople, Customers WHERE sname < cname AND rating < 200; Выводятся все комбинации из имён продавца и покупателя таким образом, что первое предшествует второму в алфавитном порядке + рейтинг < 200. Ø Соединение более 2 -х таблиц SELECT sname, cname, onum FROM Customers, Salespeople, Orders WHERE Orders. snum = Salespeople. snum AND Orders. cnum = Customers. cnum; Получены все заказы с соответствующими именами продавцов и покупателей.
Алиасы Ø Соединениуетаблицы с собой Для имён таблиц используются алиасы, они определяются в операторе SELECT в предложении FROM и действуют только в пределах этого оператора. Пример: найти все пары покупателей, имеющих одинаковый рейтинг. SELECT first. cname, second. cname, first. rating FROM Customers first, Customers second WHERE first. rating = second. rating; Реально происходит соединение таблицы с её же копией, => нужно иметь два имени для одной таблицы – алиасы. Предикат first. rating = second. rating симметричный, Þ каждая комбинация будет показана дважды: Hoffman 100 Pereira Hoffman 100 Hoffman Pereira 100 …………….
Алиасы Чтобы избежать этого дублирования, нужно использовать предикаты со знаками >, < и др. например: SELECT first. cname, second. cname, first. rating FROM Customers first, Customers second WHERE first. rating = second. rating AND first. cname < second. cname; Алиасы позволяют рассматривать одну таблицу как две независимые таблицы. Получив две копии таблицы Customers, SQL выполняет операцию JOIN как для двух разных таблиц. Алиасы можно также применять для альтернативного наименования таблиц: Customers – C, Salespeople – S.
Виды соединений (join) Ø Специальные операторы соединения (cross join) Прямое декартово произведение, эквивалентно соединению без предиката. SELECT Tab 1. a, Tab 2. b FROM Tab 1 CROSS JOIN Tab 2; или (тоже самое) SELECT Tab 1. a, Tab 2. b FROM Tab 1, Tab 2; Ø Явное соединение Условие соединения можно задавать не в WHERE, а в предложении FROM (два варианта): - ON – используется предикат, - USING – эквисоединение по столбцам с одинаковыми именами (перечисление столбцов).
Явное соединение С использованием ON: SELECT Customers. cname, Salespeople. sname, Salespeople. city FROM Customers, Salespeople ON Salespeople. city = Customers. city предикат соединения WHERE Salespeople. city in (‘Moscow’, ‘Kiev’); предикат фильтрации -------------------------------------С использованием USING: SELECT a. cnum, a. snum, onum, amt FROM Customers a, Orders b USING (cnum, snum);
Явное соединение SELECT Customers. cname, Salespeople. sname, Salespeople. city FROM Customers, Salespeople WHERE Salespeople. city in (‘Moscow’, ‘Kiev’) AND Salespeople. city = Customers. city; ON – форма: SELECT C. cname, S. sname, S. city FROM Customers С, Salespeople S ON S. city = C. city WHERE S. city IN (‘Moscow’, ‘Kiev’); USING – форма: SELECT C. cname, S. sname, S. city FROM Customers С, Salespeople S USING (city) WHERE S. city IN (‘Moscow’, ‘Kiev’);
Виды соединений (join) Ø Объединяющее соединение (union join) Пусть даны две таблицы А и В. Объединяющее соединение – это строки со всеми столбцами из А, дополненные столбцами из В с NULL-значениями, затем строки из В, дополненные столбцами из таблица А с NULL-значениями. UNION JOIN никак не сравнивает данные в соединяемых таблицах. SELECT a. snum, a. sname, b. snum, b. sname FROM Salespeople a UNION JOIN Customers b; 1001 Peel NULL 1002 Serres NULL 1004 Motika NULL. . . NULL 2001 Hoffman NULL 2002 Giovanni NULL 2003 Liu. . .
Таблицы для демонстрации оператора JOIN Табл. CUSTOMER CUS_CODE CUS_LNAME CUS_ZIP AGENT_CODE 1132445 Walker 32145 231 1217782 Adares 32145 125 1312243 Rakowski 34129 167 1321242 Rodriguez 37134 125 1542311 Smithson 37134 421 1657399 Vanloo 32145 231 Табл. AGENT_CODE AGENT_PHONE 125 6152439887 167 6153426778 231 6152431124 333 9041234445
Внутреннее соединение (inner JOIN) CUS_CODE CUS_LNAME CUS_ZIP AGENT_CODE AGENT_PHONE 1132445 Walker 32145 231 6152431124 1217782 Adares 32145 125 6152439887 1312243 Rakowski 34129 167 6153426778 1321242 Rodriguez 37134 125 6152439887 1657399 Vanloo 32145 231 6152431124 Особенности внутреннего соединения inner JOIN: если не существует совпадений между строками таблицы, то в новую таблицу несовпадающие строки не включаются. Не вошли строки с номером 421.
Внешнее соединение (= outer JOIN) Во внешнем соединении совпадающие остаются пары, а все несовпадающие значения в другой таблице должны принимать значение NULL. Рассмотрим внешнее соединение двух таблиц: CUSTOMER и AGENT. Возможны два сценария: 1. Левосторонее внешнее соединение – left outer JOIN 2. Правосторонее внешнее соединение – right outer JOIN 1. SELECT CUS_CODE, CUS_LNAME, CUS_ZIP, CUSTOMER. AGENT_CODE, AGETN_PHONE FROM CUSTOMER LEFT JOIN AGENT ON CUSTOMER. AGENT_CODE = AGENT_CODE; 2. SELECT CUS_CODE, . . . FROM CUSTOMER RIGHT JOIN AGENT ON CUSTOMER. AGENT_CODE = AGENT_CODE;
Левосторонее внешнее соединение (left outer JOIN) Выводит все строки CUSTOMER, включая те, которые не имеют совпадающих значений в таблице AGENT. CUS_CODE CUS_LNAME CUS_ZIP AGENT_CODE AGENT_PHONE 1132445 Walker 32145 231 6152431124 1217782 Adares 32145 125 6152439887 1312243 Rakowski 34129 167 6153426778 1321242 Rodriguez 37134 125 6152439887 1657399 Vanloo 32145 231 6152431124 1452311 Smithson 37134 421 NULL
Правосторонее внешнее соединение (right outer JOIN) Выводит все строки AGENT, включая те, которые не имеют совпадающих значений в таблице CUSTOMER. CUS_CODE CUS_LNAME CUS_ZIP AGENT_CODE AGENT_PHONE 1132445 Walker 32145 231 6152431124 1217782 Adares 32145 125 6152439887 1312243 Rakowski 34129 167 6153426778 1321242 Rodriguez 37134 125 6152439887 1657399 Vanloo 32145 231 6152431124 NULL 333 NULL
Полное внешнее соединение (full outer JOIN) Это комбинация левого и правого соединений, оно показывает строки из обеих таблиц: при совпадении – объединённые, в противном случае – с Null-значениями. Операторы INNER и различные виды OUTER JOIN определяют, что будет происходить при несовпадении строк.
Внутреннее и внешнее объединение таблиц (обзор) Ø INNER JOIN – внутреннее соединение, несовпадающие строки обеих таблиц исключаются. Ø OUTER JOIN – внешнее соединение, содержит несовпадающие строки из одной или обеих таблиц. § LEFT OUTER JOIN – содержит все строки из таблицы А, не обязательно совпадающие, вместе с совпадающими значениями из таблицы В. § RIGHT OUTER JOIN – содержит все строки из таблицы B, дополненные любыми совпадениями из таблицы А, при отсутствии совпадений – NULL-значениями. § FULL OUTER JOIN – показывает строки из обеих таблиц: при совпадении – объединённые, в противном случае – с NULL-значениями.
Вложенные запросы 1. SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE sname =‘Motika’); Как правило, внутренний запрос формирует значение, которое проверяется в предикате внешнего запроса. Могут использоваться =, >, <. 2. SELECT * FROM Orders WHERE amt > (SELECT AVG(amt) FROM Orders WHERE odate =’ 10/04/2012’); Будут найдены все заказы, стоимость которых > средней стоимости за 4 октября 2012 г. Запрос с GROUP BY может выдавать несколько значений, => недопустим в подзапросах данного вида.
Вложенные запросы 3. SELECT comm FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE city =‘London’); Будут определены комиссионные всех продавцов, обслуживающих покупателей из Лондона. => IN позволяет формировать подзапросы, выводящие любое количество строк. 4. SELECT rating, COUNT (DISTINCT cnum) FROM Customers GROUP BY rating HAVING ratung > (SELECT AVG(rating) FROM Customers WHERE city =’San-Jose’); Подсчитать покупателей, чей рейтинг > среднего рейтинга по Сан-Хосе. => В подзапросах можно применить HAVING.
Коррелированные подзапросы Можно ссылаться из внутреннего запроса на таблицу в предложении FROM внешнего запроса. Рассмотрим один из способов поиска всех покупателей, произведших запросы 03. 10. 2010. SELECT * FROM Customers outer 1 WHERE ’ 10/03/2010’ IN (SELECT odate FROM Orders inner 1 WHERE outer 1. cnum = inner 1. cnum); Т. к. значение в столбце cnum внешнего запроса меняется, внутренний запрос должен выполняться отдельно для каждой строки внешнего запроса. Строка внешнего запроса, для которой выполняется внутренний запрос, называется строкой – кандидатом.
Коррелированные подзапросы Последовательность действий: 1. Выбирается первая строка из Customers. 2. Эта строка имеет алиас outer 1 и эта строка – кандидат. 3. Выполняется подзапрос. Если outer 1. cnum совпадает с inner 1. cnum, то берётся всё множество полученных значений odate. 4. Это множество используется в предикате главного запроса, т. е. проверяется odate равна ’ 10/03/2010’. Если odate равна заданной дате, то строка – кандидат поступает в результирующее множество. 5. Затем в качестве строки – кандидата берётся следующая строка и выполняются п. 1 – п. 4.
Операторы подзапроса EXIST ANY (SOME) операторы подзапроса ALL Эти операторы принимают подзапросы в качестве аргументов. EXIST – предназначен для построения предиката, определяющего наличие данных в результатах подзапроса. EXIST выдаёт значение TRUE при наличии в подзапросе выходных данных, в противном случае – FALSE. EXIST «обращает внимание» только на факт наличия выходных данных и никак не использует сами данные.
Оператор подзапроса EXIST Запрос: извлечь данные из таблицы Customers Только в том случае, если один или более покупателей в этой таблице живут в Сан-Хосе. SELECT cnum, cname, city FROM Customers В данном случае WHERE EXIST обращает в (SELECT * TRUE предикат FROM Customers WHERE city =’San-Jose’); WHERE, поэтому внешний SELECT Результат: выведет три поля cnum cname city всех записей ……………. . London таблицы Customers. ……………. . Rome ……………. . San-Jose
Операторы подзапроса ANY (SOME) и ALL – также принимают подзапросы в качестве аргументов, но работают совместно с операторами отношений. Существует сходство с оператором IN, но используется только с подзапросами. Запрос: найти продавцов, имеющих покупателей в своих собственных городах. SELECT * FROM Salespeople WHERE city = ANY (SELECT city FROM Customers); ANY принимает все значения, выводимые подзапросом и обращается в TRUE, если хотя бы одно из них равно значению столбца city в текущей строке внешнего запроса.
Операторы подзапроса ANY (SOME) и ALL Запрос: найти только тех покупателей, чьи рейтинги > любого рейтинга покупателей в Риме. SELECT * FROM Customers WHERE rating > ALL (SELECT rating FROM Customers WHERE city = ‘Rome’); Предикат внешнего запроса, включающий оператор ALL, обращается в TRUE, когда каждое из значений, выбранных подзапросом, удовлетворяет условию этого предиката.
Объединение запросов UNION Ранее рассматривали размещение одних запросов внутри других. Сейчас рассмотрим слияние выходных данных двух и более запросов и представление их в виде одной таблицы. SELECT snum, sname FROM Salespeople WHERE city = ‘London’ UNION (SELECT cnum, cname FROM Customers WHERE city = ‘London’); Результат: 1 2 2006 Clemens 2001 Hoffman 1004 Motika 1001 Peel
Объединение запросов UNION Основной принцип UNION – совместимость данных по типам, возможно преобразование типов. По умолчанию UNION автоматически исключает повторяющиеся строки из выходных данных. Для отмены исключения дублирования применяется UNION ALL.
Представления (VIEW) Представления – это таблицы, содержимое которых заимствовано из других таблиц, сами по себе VIEW не содержат данных. Представление – это запрос, который выполняется каждый раз, когда в операторе встречается ссылка на данное представление. На представления можно ссылаться в операторах SELECT, INSERT и др. CREATE VIEW London_staff AS SELECT snum, sname, city, comm FROM Salespeople WHERE city = ‘London’; Никакие данные не выводятся, создается лишь новый объект базы данных. Теперь можем к нему обратиться: SELECT * FROM London_staff;
Скачать презентацию Язык SQL Соединение таблиц Запросы к нескольким
Л3_язык_SQL_Соединения.ppt