alan_beaulieu-learning_sql-ru (865932), страница 36

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 36)

||23 | CHK| NULL| Superior Auto Body||24 | SBL| NULL| AAA Insurance Inc.|+++++24 rows in set (0.00 sec)В этом варианте запроса внешнее соединение таблицы individual с таблицей account осуществляется в подзапросе account_ind, результаты которого затем путем внешнего соединения соединяются с таблицей buВнешние соединения203siness. Таким образом, каждый запрос (подзапрос и основной запрос)использует только одно внешнее соединение. При работе с другой БД(не с MySQL) эта стратегия может пригодиться для осуществлениявнешнего соединения с более чем одной таблицей.Рекурсивные внешние соединенияВ главе 5 была представлена концепция рекурсивного соединения, прикотором таблица соединяется сама с собой. Вот пример рекурсивногосоединения из главы 5, в котором таблица employee соединяется самас собой для формирования списка сотрудников и их руководителей:mysql> SELECT e.fname, e.lname,> e_mgr.fname mgr_fname, e_mgr.lname mgr_lname> FROM employee e INNER JOIN employee e_mgr> ON e.superior_emp_id = e_mgr.emp_id;+++++| fname| lname| mgr_fname | mgr_lname |+++++| Susan| Barker| Michael | Smith|| Robert | Tyler| Michael | Smith|| Susan| Hawthorne | Robert| Tyler|| John| Gooding | Susan| Hawthorne || Helen| Fleming | Susan| Hawthorne || Chris| Tucker| Helen| Fleming || Sarah| Parker| Helen| Fleming || Jane| Grossman | Helen| Fleming || Paula| Roberts | Susan| Hawthorne || Thomas | Ziegler | Paula| Roberts || Samantha | Jameson | Paula| Roberts || John| Blake| Susan| Hawthorne || Cindy| Mason| John| Blake|| Frank| Portman | John| Blake|| Theresa | Markham | Susan| Hawthorne || Beth| Fowler| Theresa | Markham || Rick| Tulman| Theresa | Markham |+++++17 rows in set (0.02 sec)Этот запрос функционирует нормально за исключением одной маленькой неувязки: в результирующий набор не включаются сотрудники,у которых нет начальника.

Однако после замены внутреннего соединения на внешнее в результирующий набор попадают все сотрудники,даже те, у которых нет руководителя:mysql> SELECT e.fname, e.lname,> e_mgr.fname mgr_fname, e_mgr.lname mgr_lname> FROM employee e LEFT OUTER JOIN employee e_mgr> ON e.superior_emp_id = e_mgr.emp_id;+++++| fname| lname| mgr_fname | mgr_lname |204Глава 10. И снова соединения+++++| Michael | Smith| NULL| NULL|| Susan| Barker| Michael | Smith|| Robert | Tyler| Michael | Smith|| Susan| Hawthorne | Robert| Tyler|| John| Gooding | Susan| Hawthorne || Helen| Fleming | Susan| Hawthorne || Chris| Tucker| Helen| Fleming || Sarah| Parker| Helen| Fleming || Jane| Grossman | Helen| Fleming || Paula| Roberts | Susan| Hawthorne || Thomas | Ziegler | Paula| Roberts || Samantha | Jameson | Paula| Roberts || John| Blake| Susan| Hawthorne || Cindy| Mason| John| Blake|| Frank| Portman | John| Blake|| Theresa | Markham | Susan| Hawthorne || Beth| Fowler| Theresa | Markham || Rick| Tulman| Theresa | Markham |+++++18 rows in set (0.00 sec)Результирующий набор теперь включает Майкла Смита (Michael Smith),который является президентом банка, следовательно, начальника у него нет.

Для формирования списка всех сотрудников и их начальников,если таковые имеются, запрос использует левостороннее внешнее соединение. Если сделать внешнее соединение правосторонним, будутполучены следующие результаты:mysql> SELECT e.fname, e.lname,>e_mgr.fname mgr_fname, e_mgr.lname mgr_lname> FROM employee e RIGHT OUTER JOIN employee e_mgr> ON e.superior_emp_id = e_mgr.emp_id;+++++| fname| lname| mgr_fname | mgr_lname |+++++| Susan| Barker| Michael | Smith|| Robert | Tyler| Michael | Smith|| NULL| NULL| Susan| Barker|| Susan| Hawthorne | Robert| Tyler|| John| Gooding | Susan| Hawthorne || Helen| Fleming | Susan| Hawthorne || Paula| Roberts | Susan| Hawthorne || John| Blake| Susan| Hawthorne || Theresa | Markham | Susan| Hawthorne || NULL| NULL| John| Gooding || Chris| Tucker| Helen| Fleming || Sarah| Parker| Helen| Fleming || Jane| Grossman | Helen| Fleming || NULL| NULL| Chris| Tucker|| NULL| NULL| Sarah| Parker|Перекрестные соединения205| NULL| NULL| Jane| Grossman || Thomas | Ziegler | Paula| Roberts || Samantha | Jameson | Paula| Roberts || NULL| NULL| Thomas| Ziegler || NULL| NULL| Samantha | Jameson || Cindy| Mason| John| Blake|| Frank| Portman | John| Blake|| NULL| NULL| Cindy| Mason|| NULL| NULL| Frank| Portman || Beth| Fowler| Theresa | Markham || Rick| Tulman| Theresa | Markham || NULL| NULL| Beth| Fowler|| NULL| NULL| Rick| Tulman|+++++28 rows in set (0.00 sec)По этому запросу выбираются все руководители (попрежнему третийи четвертый столбцы) вместе со всеми их подчиненными.

ПоэтомуМайкл Смит появляется дважды – как начальник Сьюзен Баркер (Susan Barker) и Роберта Тайлера (Robert Tyler). Сьюзен Баркер появляется один раз, она никем не руководит (значения null в первом и второмстолбцах). Все 18 сотрудников появляются в третьем и четвертом столбцах, по крайней мере, один раз. Некоторые появляются несколько раз,если у них в подчинении несколько сотрудников. Таким образом, в результирующем наборе 28 строк.

Этот результат очень отличается отрезультата предыдущего запроса, а обеспечен он изменением всего одного ключевого слова (left на right). Следовательно, при использовании внешнего соединения необходимо тщательно продумывать, какимоно должно быть – левосторонним или правосторонним.Перекрестные соединенияВ главе 5 была представлена концепция декартова произведения, которое, в сущности, является результатом соединения нескольких таблиц без указания какихлибо условий соединения. Декартово произведение довольно часто используется в результате случайности (т. е. когда разработчики просто забывают добавить в блок from условие соединения), но на самом деле не так уж широко распространено.

Однакоесли действительно требуется получить декартово произведение двухтаблиц, должно быть задано перекрестное соединение:mysql> SELECT pt.name, p.product_cd, p.name> FROM product p CROSS JOIN product_type pt;++++| name| product_cd | name|++++| Customer Accounts| AUT| auto loan|| Customer Accounts| BUS| business line of credit || Customer Accounts| CD| certificate of deposit |206Глава 10.

И снова соединения| Customer Accounts| CHK| checking account|| Customer Accounts| MM| money market account|| Customer Accounts| MRT| home mortgage|| Customer Accounts| SAV| savings account|| Customer Accounts| SBL| small business loan|| Insurance Offerings| AUT| auto loan|| Insurance Offerings| BUS| business line of credit || Insurance Offerings| CD| certificate of deposit || Insurance Offerings| CHK| checking account|| Insurance Offerings| MM| money market account|| Insurance Offerings| MRT| home mortgage|| Insurance Offerings| SAV| savings account|| Insurance Offerings| SBL| small business loan|| Individual and Business Loans | AUT| auto loan|| Individual and Business Loans | BUS| business line of credit || Individual and Business Loans | CD| certificate of deposit || Individual and Business Loans | CHK| checking account|| Individual and Business Loans | MM| money market account|| Individual and Business Loans | MRT| home mortgage|| Individual and Business Loans | SAV| savings account|| Individual and Business Loans | SBL| small business loan|++++24 rows in set (0.00 sec)Этот запрос формирует декартово произведение таблиц product и product_type.

В результате получаем 24 строки (8 строк product умножаются на 3 строки product_type). Но теперь, когда известно, что такое перекрестное соединение и как оно задается, надо определиться с тем, зачем оно используется. Большинство книг по SQL описывают, что такоеперекрестное соединение, и затем говорят, что используется оно редко.

Но мне бы хотелось поделиться с читателем ситуациями, в которых я нахожу перекрестное соединение довольно полезным.В главе 9 обсуждалось, как использовать подзапросы для создания таблиц. Используемый пример показывал, как построить таблицу, включающую три строки, которая могла быть соединена с другими таблицами. Вот таблица из того примера:mysql> SELECT 'Small Fry' name, 0 low_limit, 4999.99 high_limit> UNION ALL> SELECT 'Average Joes' name, 5000 low_limit, 9999.99 high_limit> UNION ALL> SELECT 'Heavy Hitters' name, 10000 low_limit, 9999999.99 high_limit;++++| name| low_limit | high_limit |++++| Small Fry|0 |4999.99 || Average Joes |5000 |9999.99 || Heavy Hitters |10000 | 9999999.99 |++++3 rows in set (0.00 sec)207Перекрестные соединенияХотя эта таблица является именно тем, что требовалось для разделенияклиентов на три группы на основании их совокупного остатка на счете,эта стратегия слияния однострочных таблиц с помощью оператораunion all не очень подходит, если требуется соорудить большую таблицу.Например, требуется создать запрос, формирующий строку для каждого дня 2004 года, но в БД нет таблицы, содержащей строки для всехдней.

Используя стратегию из примера главы 9, можно было бы сделать чтото вроде этого:SELECT '20040101'UNION ALLSELECT '20040102'UNION ALLSELECT '20040103'UNION ALL.........SELECT '20041229'UNION ALLSELECT '20041230'UNION ALLSELECT '20041231'dtdtdtdtdtdtСоздавать запрос, соединяющий результаты 366 запросов, немного утомительно, поэтому, наверное, нужна другая стратегия. Что если сгенерировать таблицу с 366 строками (2004 год был високосным) и однимстолбцом, содержащим число от 0 до 366, и затем добавлять это числодней к 1 января 2004? Вот одна из возможных методик формированияподобной таблицы:mysql>>>>>>>>>>>>>>>>>>>>SELECT ones.num + tens.num + hundreds.numFROM(SELECT 0 num UNION ALLSELECT 1 num UNION ALLSELECT 2 num UNION ALLSELECT 3 num UNION ALLSELECT 4 num UNION ALLSELECT 5 num UNION ALLSELECT 6 num UNION ALLSELECT 7 num UNION ALLSELECT 8 num UNION ALLSELECT 9 num) onesCROSS JOIN(SELECT 0 num UNION ALLSELECT 10 num UNION ALLSELECT 20 num UNION ALLSELECT 30 num UNION ALLSELECT 40 num UNION ALLSELECT 50 num UNION ALLSELECT 60 num UNION ALL208Глава 10.

Характеристики

Список файлов ответов (шпаргалок)