изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

• Название: Она позволяет решать многошаговые задачи одним выражением
• Дата: 09.02.2018
• Размер: 1.6 Mb.
• Имя файла: изучаем SQL.pdf
• Тип: Документы; #36127
• страница: 22 из 31

199
Внешнее соединение включает все строки одной таблицы и вводит дан ные второй таблицы только в случае обнаружения соответствующих строк. В данном случае в результат вошли все строки таблицы account,
поскольку задано left outer join (левостороннее внешнее соединение)
и таблица account находится в левой части описания соединения. Стол бец name имеет значение null для всех строк, кроме четырех строк кли ентов юридических лиц (cust_id 10, 11, 12 и 13). Вот аналогичный за прос с внешним соединением, но с таблицей individual вместо business:
mysql> SELECT a.account_id, a.cust_id, i.fname, i.lname
> FROM account a LEFT OUTER JOIN individual i
> ON a.cust_id = i.cust_id;
+
+
+
+
+
| account_id | cust_id | fname | lname |
+
+
+
+
+
| 1 | 1 | James | Hadley |
| 2 | 1 | James | Hadley |
| 3 | 1 | James | Hadley |
| 4 | 2 | Susan | Tingley |
| 5 | 2 | Susan | Tingley |
| 6 | 3 | Frank | Tucker |
| 7 | 3 | Frank | Tucker |
| 8 | 4 | John | Hayward |
| 9 | 4 | John | Hayward |
| 10 | 4 | John | Hayward |
| 11 | 5 | Charles | Frasier |
| 12 | 6 | John | Spencer |
| 13 | 6 | John | Spencer |
| 14 | 7 | Margaret | Young |
| 15 | 8 | Louis | Blake |
| 16 | 8 | Louis | Blake |
| 17 | 9 | Richard | Farley |
| 18 | 9 | Richard | Farley |
| 19 | 9 | Richard | Farley |
| 20 | 10 | NULL | NULL |
| 21 | 10 | NULL | NULL |
| 22 | 11 | NULL | NULL |
| 23 | 12 | NULL | NULL |
| 24 | 13 | NULL | NULL |
+
+
+
+
+
24 rows in set (0.00 sec)
Этот запрос, по сути, противоположен предыдущему: выводятся име на и фамилии физических лиц, тогда как для юридических лиц эти столбцы имеют значение null.
Сравнение левосторонних и правосторонних
внешних соединений
В предыдущем разделе в примерах внешних соединений было задано left outer join. Ключевое слово left свидетельствует о том, что табли

200
Глава 10. И снова соединения ца, находящаяся в левой части блока from, отвечает за определение числа строк в результирующем наборе, а таблица в правой части пре доставляет значения столбцов в случае обнаружения соответствия.
Рассмотрим следующий пример:
mysql> SELECT c.cust_id, b.name
> FROM customer c LEFT OUTER JOIN business b
> ON c.cust_id = b.cust_id;
+
+
+
| cust_id | name |
+
+
+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | Chilton Engineering |
| 11 | Northeast Cooling Inc. |
| 12 | Superior Auto Body |
| 13 | AAA Insurance Inc. |
+
+
+
13 rows in set (0.00 sec)
Блок from определяет левостороннее внешнее соединение. Таким обра зом, в результирующий набор входят все 13 строк таблицы customer,
а таблица business поставляет значения во второй столбец результи рующего набора для четырех клиентов юридических лиц. Если вы полнить такой же запрос, но указать правостороннее внешнее соедине ние, будет получен следующий результат:
mysql> SELECT c.cust_id, b.name
> FROM customer c RIGHT OUTER JOIN business b
> ON c.cust_id = b.cust_id;
+
+
+
| cust_id | name |
+
+
+
| 10 | Chilton Engineering |
| 11 | Northeast Cooling Inc. |
| 12 | Superior Auto Body |
| 13 | AAA Insurance Inc. |
+
+
+
4 rows in set (0.00 sec)
Теперь число строк результирующего набора определяется количест вом строк таблицы business. Вот почему в этом множестве всего четыре строки.

Внешние соединения
201
Помните, что оба запроса осуществляют внешние соединения. Ключе вые слова left и right просто сообщают оптимизатору БД, какая табли ца может иметь пробелы в данных. Если нужно провести внешнее со единение таблиц А и В таким образом, чтобы в результирующий набор входили все строки из А и те строки из В, для которых есть соответст вующие данные, можно задать или A left outer join B, или B right outer join
A.
Трехсторонние внешние соединения
В некоторых случаях может потребоваться провести внешнее соедине ние одной таблицы с двумя другими таблицами. Например, нужен список всех счетов с указанием или имени и фамилии физического ли ца, или названия фирмы для юридического лица:
mysql> SELECT a.account_id, a.product_cd,
> CONCAT(i.fname, ' ', i.lname) person_name,
> b.name business_name
> FROM account a LEFT OUTER JOIN individual i
> ON a.cust_id = i.cust_id
> LEFT OUTER JOIN business b
> ON a.cust_id = b.cust_id;
+
+
+
+
+
| account_id | product_cd | person_name | business_name |
+
+
+
+
+
| 1 | CHK | James Hadley | NULL |
| 2 | SAV | James Hadley | NULL |
| 3 | CD | James Hadley | NULL |
| 4 | CHK | Susan Tingley | NULL |
| 5 | SAV | Susan Tingley | NULL |
| 6 | CHK | Frank Tucker | NULL |
| 7 | MM | Frank Tucker | NULL |
| 8 | CHK | John Hayward | NULL |
| 9 | SAV | John Hayward | NULL |
| 10 | MM | John Hayward | NULL |
| 11 | CHK | Charles Frasier | NULL |
| 12 | CHK | John Spencer | NULL |
| 13 | CD | John Spencer | NULL |
| 14 | CD | Margaret Young | NULL |
| 15 | CHK | Louis Blake | NULL |
| 16 | SAV | Louis Blake | NULL |
| 17 | CHK | Richard Farley | NULL |
| 18 | MM | Richard Farley | NULL |
| 19 | CD | Richard Farley | NULL |
| 20 | CHK | NULL | Chilton Engineering |
| 21 | BUS | NULL | Chilton Engineering |
| 22 | BUS | NULL | Northeast Cooling Inc. |
| 23 | CHK | NULL | Superior Auto Body |
| 24 | SBL | NULL | AAA Insurance Inc. |
+
+
+
+
+
24 rows in set (0.00 sec)

202
Глава 10. И снова соединения
Результаты включают все 24 строки таблицы account, а также имена клиентов или названия фирм, поступающие из двух других таблиц в результате внешнего соединения.
Мне не известны ограничения в MySQL, касающиеся количества таб лиц, с которыми можно осуществлять внешнее соединение. Но чтобы сократить число соединений в запросе, всегда можно воспользоваться подзапросами. Например, предыдущий пример можно переписать так:
mysql> SELECT account_ind.account_id, account_ind.product_cd,
> account_ind.person_name,
> b.name business_name
> FROM
> (SELECT a.account_id, a.product_cd, a.cust_id,
> CONCAT(i.fname, ' ', i.lname) person_name
> FROM account a LEFT OUTER JOIN individual i
> ON a.cust_id = i.cust_id) account_ind
> LEFT OUTER JOIN business b
> ON account_ind.cust_id = b.cust_id;
+
+
+
+
+
| account_id | product_cd | person_name | business_name |
+
+
+
+
+
| 1 | CHK | James Hadley | NULL |
| 2 | SAV | James Hadley | NULL |
| 3 | CD | James Hadley | NULL |
| 4 | CHK | Susan Tingley | NULL |
| 5 | SAV | Susan Tingley | NULL |
| 6 | CHK | Frank Tucker | NULL |
| 7 | MM | Frank Tucker | NULL |
| 8 | CHK | John Hayward | NULL |
| 9 | SAV | John Hayward | NULL |
| 10 | MM | John Hayward | NULL |
| 11 | CHK | Charles Frasier | NULL |
| 12 | CHK | John Spencer | NULL |
| 13 | CD | John Spencer | NULL |
| 14 | CD | Margaret Young | NULL |
| 15 | CHK | Louis Blake | NULL |
| 16 | SAV | Louis Blake | NULL |
| 17 | CHK | Richard Farley | NULL |
| 18 | MM | Richard Farley | NULL |
| 19 | CD | Richard Farley | NULL |
| 20 | CHK | NULL | Chilton Engineering |
| 21 | BUS | NULL | Chilton Engineering |
| 22 | BUS | NULL | Northeast Cooling Inc. |
| 23 | CHK | NULL | Superior Auto Body |
| 24 | SBL | NULL | AAA Insurance Inc. |
+
+
+
+
+
24 rows in set (0.00 sec)
В этом варианте запроса внешнее соединение таблицы individual с таб лицей account осуществляется в подзапросе account_ind, результаты ко торого затем путем внешнего соединения соединяются с таблицей bu

Внешние соединения
203
siness
. Таким образом, каждый запрос (подзапрос и основной запрос)
использует только одно внешнее соединение. При работе с другой БД
(не с 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)
По этому запросу выбираются все руководители (по прежнему третий и четвертый столбцы) вместе со всеми их подчиненными. Поэтому
Майкл Смит появляется дважды – как начальник Сьюзен Баркер (Su san 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 и prod uct_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 '2004 01 01' dt
UNION ALL
SELECT '2004 01 02' dt
UNION ALL
SELECT '2004 01 03' dt
UNION ALL
SELECT '2004 12 29' dt
UNION ALL
SELECT '2004 12 30' dt
UNION ALL
SELECT '2004 12 31' dt
Создавать запрос, соединяющий результаты 366 запросов, немного уто мительно, поэтому, наверное, нужна другая стратегия. Что если сгене рировать таблицу с 366 строками (2004 год был високосным) и одним столбцом, содержащим число от 0 до 366, и затем добавлять это число дней к 1 января 2004? Вот одна из возможных методик формирования подобной таблицы:
mysql>