alan_beaulieu-learning_sql-ru (865932), страница 19
Текст из файла (страница 19)
Благодаря присваиванию разных псевдо нимов каждому экземпляру таблицы branch, сервер сможет определитьэкземпляр, на который делается ссылка, – соединенный с таблицей102Глава 5. Запрос к нескольким таблицамaccount или с таблицей employee. Таким образом, имеем пример запроса,в котором использование псевдонимов таблиц является обязательным.Рекурсивные соединенияМожно не только несколько раз включать одну и ту же таблицу в одинзапрос, фактически можно соединить таблицу с самой собой.
Поначалуэто может показаться странным, но для этого есть веские основания.В таблице employee, например, есть рекурсивный внешний ключ (selfreferencing foreign key). Это означает, что она включает столбец (superior_emp_id), указывающий на первичный ключ в рамках той же табли цы. Этот столбец указывает на начальника сотрудника (если толькоэто не сам босс – тогда столбец имеет значение null).
С помощью рекурсивного соединения (selfjoin) можно создать запрос, в результате вы полнения которого выводится список всех сотрудников с указаниемимен их начальников: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.01 sec)Этот запрос включает два экземпляра таблицы employee: из одного (подпсевдонимом e) извлекаются имена сотрудников, а из другого (под псев донимом e_mgr) – имена начальников.
Подблок on использует эти псев донимы для соединения таблицы employee с самой собой посредствомвнешнего ключа superior_emp_id. Это еще один пример запроса, для ко Сравнение эквисоединений с неэквисоединениями103торого псевдонимы таблиц являются обязательными. В противномслучае сервер не сможет определить, на кого делается ссылка – на со трудника или его начальника.Хотя в таблице employee 18 строк, по запросу было возвращено только 17.У президента банка, Майкла Смита (Michael Smith), нет начальника(его столбец superior_emp_id имеет значение null), поэтому для даннойстроки соединение не сработало. Чтобы включить Майкла Смита в ре зультирующий набор, необходимо использовать внешнее соединение,которое будет рассмотрено в главе 10.Сравнение эквисоединенийс неэквисоединениямиВсе запросы к нескольким таблицам, показанные до сих пор, исполь зовали эквисоединения (equijoins).
Это означает, что для обеспеченияуспешности соединения значения двух таблиц должны совпадать. Эк висоединение всегда использует знак равенства, например:ON e.assigned_branch_id = b.branch_idПодавляющее большинство запросов использует эквисоединения, номожно также соединять таблицы посредством диапазонов значений,называемых неэквисоединениями (nonequijoins). Вот пример запроса,осуществляющего соединение по диапазону значений:SELECT e.emp_id, e.fname, e.lname, e.start_dateFROM employee e INNER JOIN product pON e.start_date >= p.date_offeredAND e.start_date <= p.date_retiredWHERE p.name = 'nofee checking';Этот запрос соединяет две таблицы, между которыми нет взаимосвя зей по внешним ключам.
Задача – найти всех сотрудников, принятыхв банк в то время, когда предлагалась услуга беспроцентного текущеговклада. Таким образом, дата начала работы сотрудника должна нахо диться между датами начала и конца этой акции.Также может понадобиться рекурсивное неэквисоединение (selfnonequijoin), которое означает, что таблица соединяется сама с собой с ис пользованием неэквисоединения.
Например, управляющий операция ми решил провести шахматный турнир между всеми операциониста ми банка. Требуется создать список всех пар игроков. Можно попробо вать получить список всех операционистов (title = 'Teller'), соединивтаблицу employee с самой собой, и выбрать из него все строки с разнымизначениями emp_id (поскольку игрок не может составить пару с самимсобой):mysql> SELECT e1.fname, e1.lname, 'VS' vs, e2.fname, e2.lname> FROM employee e1 INNER JOIN employee e2104Глава 5. Запрос к нескольким таблицам> ON e1.emp_id != e2.emp_id> WHERE e1.title = 'Teller' AND e2.title = 'Teller';++++++| fname| lname| vs | fname| lname|++++++| Sarah| Parker | VS | Chris| Tucker || Jane| Grossman | VS | Chris| Tucker || Thomas | Ziegler | VS | Chris| Tucker || Samantha | Jameson | VS | Chris| Tucker || Cindy| Mason| VS | Chris| Tucker || Frank| Portman | VS | Chris| Tucker || Beth| Fowler | VS | Chris| Tucker || Rick| Tulman | VS | Chris| Tucker || Chris| Tucker | VS | Sarah| Parker || Jane| Grossman | VS | Sarah| Parker || Thomas | Ziegler | VS | Sarah| Parker || Samantha | Jameson | VS | Sarah| Parker || Cindy| Mason| VS | Sarah| Parker || Frank| Portman | VS | Sarah| Parker || Beth| Fowler | VS | Sarah| Parker || Rick| Tulman | VS | Sarah| Parker |...| Chris| Tucker | VS | Rick| Tulman || Sarah| Parker | VS | Rick| Tulman || Jane| Grossman | VS | Rick| Tulman || Thomas | Ziegler | VS | Rick| Tulman || Samantha | Jameson | VS | Rick| Tulman || Cindy| Mason| VS | Rick| Tulman || Frank| Portman | VS | Rick| Tulman || Beth| Fowler | VS | Rick| Tulman |++++++72 rows in set (0.01 sec)Мы на правильном пути, но проблема здесь в том, что для каждой па ры (например, Сара Паркер (Sarah Parker) против Криса Такера (ChrisTucker)) имеется «обратная» пара (т.
е. Крис Такер против Сары Пар кер). Один из способов достигнуть желаемого результата – использо вать условие соединения e1.emp_id < e2.emp_id, чтобы каждый опера ционист входил в пару только с теми, у кого ID сотрудника больше(можно также использовать e1.emp_id > e2.emp_id, если вам так большенравится):mysql> SELECT e1.fname, e1.lname, 'VS' vs, e2.fname, e2.lname> FROM employee e1 INNER JOIN employee e2> ON e1.emp_id < e2.emp_id> WHERE e1.title = 'Teller' AND e2.title = 'Teller';++++++| fname| lname| vs | fname| lname|++++++| Chris| Tucker | VS | Sarah| Parker || Chris| Tucker | VS | Jane| Grossman |Сравнение условий соединения и условий фильтрации105| Chris| Tucker | VS | Thomas | Ziegler || Chris| Tucker | VS | Samantha | Jameson || Chris| Tucker | VS | Cindy| Mason|| Chris| Tucker | VS | Frank| Portman || Chris| Tucker | VS | Beth| Fowler || Chris| Tucker | VS | Rick| Tulman || Sarah| Parker | VS | Jane| Grossman || Sarah| Parker | VS | Thomas | Ziegler || Sarah| Parker | VS | Samantha | Jameson || Sarah| Parker | VS | Cindy| Mason|| Sarah| Parker | VS | Frank| Portman || Sarah| Parker | VS | Beth| Fowler || Sarah| Parker | VS | Rick| Tulman || Jane| Grossman | VS | Thomas | Ziegler || Jane| Grossman | VS | Samantha | Jameson || Jane| Grossman | VS | Cindy| Mason|| Jane| Grossman | VS | Frank| Portman || Jane| Grossman | VS | Beth| Fowler || Jane| Grossman | VS | Rick| Tulman || Thomas | Ziegler | VS | Samantha | Jameson || Thomas | Ziegler | VS | Cindy| Mason|| Thomas | Ziegler | VS | Frank| Portman || Thomas | Ziegler | VS | Beth| Fowler || Thomas | Ziegler | VS | Rick| Tulman || Samantha | Jameson | VS | Cindy| Mason|| Samantha | Jameson | VS | Frank| Portman || Samantha | Jameson | VS | Beth| Fowler || Samantha | Jameson | VS | Rick| Tulman || Cindy| Mason| VS | Frank| Portman || Cindy| Mason| VS | Beth| Fowler || Cindy| Mason| VS | Rick| Tulman || Frank| Portman | VS | Beth| Fowler || Frank| Portman | VS | Rick| Tulman || Beth| Fowler | VS | Rick| Tulman |++++++36 rows in set (0.01 sec)Теперь у нас есть список из 36 пар.
Как раз столько, сколько должнобыть при наличии девяти участников.Сравнение условий соединенияи условий фильтрацииТеперь мы знаем, что условия соединения относятся к подблоку on, то гда как условия фильтрации располагаются в блоке where. Однако SQLне налагает жестких ограничений на размещение условий, поэтому соз давать запросы следует очень внимательно. Например, следующий за прос соединяет две таблицы с помощью одного блока соединения и од ного условия фильтрации в блоке where:106Глава 5.
Запрос к нескольким таблицамmysql> SELECT a.account_id, a.product_cd, c.fed_id> FROM account a INNER JOIN customer c> ON a.cust_id = c.cust_id> WHERE c.cust_type_cd = 'B';++++| account_id | product_cd | fed_id|++++|20 | CHK| 041111111 ||21 | BUS| 041111111 ||22 | BUS| 042222222 ||23 | CHK| 043333333 ||24 | SBL| 044444444 |++++5 rows in set (0.08 sec)Достаточно просто, но что произойдет, если по ошибке поместить усло вие фильтрации в подблок on, а не в блок where?mysql> SELECT a.account_id, a.product_cd, c.fed_id> FROM account a INNER JOIN customer c> ON a.cust_id = c.cust_id>AND c.cust_type_cd = 'B';++++| account_id | product_cd | fed_id|++++|20 | CHK| 041111111 ||21 | BUS| 041111111 ||22 | BUS| 042222222 ||23 | CHK| 043333333 ||24 | SBL| 044444444 |++++5 rows in set (0.00 sec)Как видите, второй вариант, в котором оба условия находятся в подбло ке on и нет блока where, обеспечивает аналогичные результаты.
А что ес ли оба условия помещены в блок where, но блок from по прежнему ис пользует ANSI синтаксис соединения?mysql> SELECT a.account_id, a.product_cd, c.fed_id> FROM account a INNER JOIN customer c> WHERE a.cust_id = c.cust_id> AND c.cust_type_cd = 'B';++++| account_id | product_cd | fed_id|++++|20 | CHK| 041111111 ||21 | BUS| 041111111 ||22 | BUS| 042222222 ||23 | CHK| 043333333 ||24 | SBL| 044444444 |++++5 rows in set (0.00 sec)Упражнения107Сервер MySQL снова сгенерировал тот же результирующий набор. Рас положить условия на соответствующих местах, чтобы запрос был пра вильно понят и обработан, – ваша задача.УпражненияСледующие упражнения призваны протестировать понимание внут ренних соединений.
Решения приведены в приложении С.5.1Заполните в следующем запросе пробелы (обозначенные как <число>),чтобы получить такие результаты:mysql> SELECT e.emp_id, e.fname, e.lname, b.name> FROM employee e INNER JOIN <1> b> ON e.assigned_branch_id = b.<2>;+++++| emp_id | fname| lname| name|+++++|1 | Michael | Smith| Headquarters ||2 | Susan| Barker| Headquarters ||3 | Robert | Tyler| Headquarters ||4 | Susan| Hawthorne | Headquarters ||5 | John| Gooding | Headquarters ||6 | Helen| Fleming | Headquarters ||7 | Chris| Tucker| Headquarters ||8 | Sarah| Parker| Headquarters ||9 | Jane| Grossman | Headquarters ||10 | Paula| Roberts | Woburn Branch ||11 | Thomas | Ziegler | Woburn Branch ||12 | Samantha | Jameson | Woburn Branch ||13 | John| Blake| Quincy Branch ||14 | Cindy| Mason| Quincy Branch ||15 | Frank| Portman | Quincy Branch ||16 | Theresa | Markham | So.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
