alan_beaulieu-learning_sql-ru (865932), страница 35
Текст из файла (страница 35)
Продолжайте экспериментироватьс подзапросами и вскоре обнаружите, что при написании каждого не обычного SQL выражения очень полезно рассматривать возможностьиспользования подзапроса.УпражненияЭти упражнения ориентированы на проверку понимания подзапросов.Решения приведены в приложении С.9.1Создайте запрос к таблице account, использующий условие фильтра ции с несвязанным подзапросом к таблице product для поиска всех кре дитных счетов (product.product_type_cd = 'LOAN'). Должны быть выбра ны ID счета, код счета, ID клиента и доступный остаток.9.2Переработайте запрос из упражнения 9.1, используя связанный подза прос к таблице product для получения того же результата.9.3Соедините следующий запрос с таблицей employee, чтобы показать уро вень квалификации каждого сотрудника:SELECT 'trainee' name, '20040101' start_dt, '20051231' end_dtUNION ALLSELECT 'worker' name, '20020101' start_dt, '20031231' end_dtUNION ALLSELECT 'mentor' name, '20000101' start_dt, '20011231' end_dtДайте подзапросу псевдоним levels (уровни) и включите ID сотрудни ка, имя, фамилию и квалификацию (levels.name).
(Совет: в условии со единения определяйте диапазон, в который попадает столбец employee.start_date, с помощью условия неравенства.)9.4Создайте запрос к таблице employee для получения ID, имени и фами лии сотрудника вместе с названиями отдела и отделения, к которымон приписан. Не используйте соединение таблиц.И снова соединенияНа данный момент читатель должен владеть концепцией внутреннегосоединения, представленной в главе 5. Основное внимание в этой главеуделено другим способам соединения таблиц, включая внешнее и пере крестное соединения.Внешние соединенияДо сих пор ни в одном из приведенных примеров, включающих запросык нескольким таблицам, не поднимался вопрос о том, что не все строкитаблицы могут соответствовать условиям соединения.
Например, присоединении таблицы account с таблицей customer ничего не было сказа но о возможности отсутствия для значения столбца cust_id таблицыaccount соответствующего значения в столбце cust_id таблицы customer.Если бы такое случилось, некоторые строки одной из таблиц не вошлибы в результирующий набор.На всякий случай давайте проверим данные таблицы. Вот столбцы account_id и cust_id таблицы account:mysql> SELECT account_id, cust_id> FROM account;+++| account_id | cust_id |+++|1 |1 ||2 |1 ||3 |1 ||4 |2 ||5 |2 ||6 |3 ||7 |3 ||8 |4 ||9 |4 |196Глава 10. И снова соединения|10 |4 ||11 |5 ||12 |6 ||13 |6 ||14 |7 ||15 |8 ||16 |8 ||17 |9 ||18 |9 ||19 |9 ||20 |10 ||21 |10 ||22 |11 ||23 |12 ||24 |13 |+++24 rows in set (0.04 sec)Имеется 24 счета 13 разных клиентов с ID клиента от 1 до 13, по край ней мере по одному счету на каждого.
Вот множество клиентских IDтаблицы customer:mysql> SELECT cust_id> FROM customer;++| cust_id |++|1 ||2 ||3 ||4 ||5 ||6 ||7 ||8 ||9 ||10 ||11 ||12 ||13 |++13 rows in set (0.02 sec)В таблице customer 13 строк с ID от 1 до 13. Таким образом, каждый IDклиента включен в таблицу account, по крайней мере, один раз.
Следо вательно, при соединении двух таблиц по столбцу cust_id можно ожи дать, что в результирующий набор будут включены все 24 строки (еслинет других условий фильтрации):mysql> SELECT a.account_id, c.cust_id> FROM account a INNER JOIN customer c> ON a.cust_id = c.cust_id;Внешние соединения197+++| account_id | cust_id |+++|1 |1 ||2 |1 ||3 |1 ||4 |2 ||5 |2 ||6 |3 ||7 |3 ||8 |4 ||9 |4 ||10 |4 ||11 |5 ||12 |6 ||13 |6 ||14 |7 ||15 |8 ||16 |8 ||17 |9 ||18 |9 ||19 |9 ||20 |10 ||21 |10 ||22 |11 ||23 |12 ||24 |13 |+++24 rows in set (0.00 sec)Как и ожидалось, в результирующем наборе представлены все 24 сче та. Но что произойдет, если соединить таблицу account с одной из спе циализированных таблиц клиентов, например таблицей business?mysql> SELECT a.account_id, b.cust_id, b.name> FROM account a INNER JOIN business b> ON a.cust_id = b.cust_id;++++| account_id | cust_id | name|++++|20 |10 | Chilton Engineering||21 |10 | Chilton Engineering||22 |11 | Northeast Cooling Inc.
||23 |12 | Superior Auto Body||24 |13 | AAA Insurance Inc.|++++5 rows in set (0.00 sec)Теперь в результирующем наборе только пять строк вместо 24. Загля нем в таблицу business, чтобы понять, почему так произошло:mysql> SELECT cust_id, name> FROM business;198Глава 10. И снова соединения+++| cust_id | name|+++|10 | Chilton Engineering||11 | Northeast Cooling Inc. ||12 | Superior Auto Body||13 | AAA Insurance Inc.|+++4 rows in set (0.01 sec)Из 13 строк таблицы клиентов только четыре относятся к юридиче ским лицам.
И поскольку у одного из юридических лиц два счета, в об щей сложности с юридическими лицами связаны пять строк таблицыaccount.Но что делать, если требуется, чтобы запрос возвращал все счета, нопри этом включал название фирмы, только если счет связан с юриди ческим лицом? Это пример, когда необходимо внешнее соединение(outer join) таблиц account и business:mysql> SELECT a.account_id, a.cust_id, b.name> FROM account a LEFT OUTER JOIN business b> ON a.cust_id = b.cust_id;++++| account_id | cust_id | name|++++|1 |1 | NULL||2 |1 | NULL||3 |1 | NULL||4 |2 | NULL||5 |2 | NULL||6 |3 | NULL||7 |3 | NULL||8 |4 | NULL||9 |4 | NULL||10 |4 | NULL||11 |5 | NULL||12 |6 | NULL||13 |6 | NULL||14 |7 | NULL||15 |8 | NULL||16 |8 | NULL||17 |9 | NULL||18 |9 | NULL||19 |9 | NULL||20 |10 | Chilton Engineering||21 |10 | Chilton Engineering||22 |11 | Northeast Cooling Inc.
||23 |12 | Superior Auto Body||24 |13 | AAA Insurance Inc.|++++24 rows in set (0.00 sec)Внешние соединения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 outerjoin 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.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
