alan_beaulieu-learning_sql-ru (865932), страница 18
Текст из файла (страница 18)
Вот тот же запрос, записанный с использованиемсинтаксиса соединения SQL92:mysql> SELECT a.account_id, a.cust_id, a.open_date, a.product_cd> FROM account a INNER JOIN employee e> ON a.open_emp_id = e.emp_id> INNER JOIN branch b> ON e.assigned_branch_id = b.branch_id> WHERE e.start_date <= '20030101'> AND (e.title = 'Teller' OR e.title = 'Head Teller')> AND b.name = 'Woburn Branch';+++++| account_id | cust_id | open_date | product_cd |+++++|1 |1 | 20000115 | CHK||2 |1 | 20000115 | SAV||3 |1 | 20040630 | CD||4 |2 | 20010312 | CHK||5 |2 | 20010312 | SAV||14 |7 | 20040112 | CD||22 |11 | 20040322 | BUS|+++++7 rows in set (0.36 sec)Надеюсь, все согласятся, что понятнее версия, использующая синтак сис SQL92.Соединение трех и более таблицСоединение трех таблиц аналогично соединению двух, но есть неболь шая хитрость.
При соединении двух таблиц имеются две таблицыи один тип соединения в блоке from, а также единственный подблок on,определяющий, как соединяются таблицы. При соединении трех таб лиц присутствуют три таблицы и два типа соединения в блоке from,а также два подблока on. Вот еще один пример запроса с соединениемдвух таблиц:mysql> SELECT a.account_id, c.fed_id> FROM account a INNER JOIN customer c> ON a.cust_id = c.cust_id98Глава 5. Запрос к нескольким таблицам> WHERE c.cust_type_cd = 'B';+++| account_id | fed_id|+++|20 | 041111111 ||21 | 041111111 ||22 | 042222222 ||23 | 043333333 ||24 | 044444444 |+++5 rows in set (0.06 sec)Этот запрос, возвращающий ID счета и идентификационный номер фе дерального налога для всех бизнес счетов, на данный момент долженбыть абсолютно понятным.
Однако если добавить в запрос таблицу employee для получения имени операциониста, открывшего каждый из сче тов, он примет следующий вид:mysql> SELECT a.account_id, c.fed_id, e.fname, e.lname> FROM account a INNER JOIN customer c> ON a.cust_id = c.cust_id> INNER JOIN employee e> ON a.open_emp_id = e.emp_id> WHERE c.cust_type_cd = 'B';+++++| account_id | fed_id| fname | lname |+++++|20 | 041111111 | Theresa | Markham ||21 | 041111111 | Theresa | Markham ||22 | 042222222 | Paula | Roberts ||23 | 043333333 | Theresa | Markham ||24 | 044444444 | John| Blake |+++++5 rows in set (0.03 sec)Теперь имеется три таблицы, два типа соединений и два подблока on,перечисленные в блоке from. Таким образом, все немного усложняется.На первый взгляд из за порядка перечисления таблиц может пока заться, что таблица employee соединяется с таблицей customer, посколь ку таблица account указана первой.
Однако если изменить порядок рас положения таблиц, результаты будут абсолютно аналогичными:mysql> SELECT a.account_id, c.fed_id, e.fname, e.lname> FROM customer c INNER JOIN account a> ON a.cust_id = c.cust_id> INNER JOIN employee e> ON a.open_emp_id = e.emp_id> WHERE c.cust_type_cd = 'B';+++++| account_id | fed_id| fname | lname |+++++|20 | 041111111 | Theresa | Markham |Соединение трех и более таблиц99|21 | 041111111 | Theresa | Markham ||22 | 042222222 | Paula | Roberts ||23 | 043333333 | Theresa | Markham ||24 | 044444444 | John| Blake |+++++5 rows in set (0.00 sec)Теперь таблица customer стоит первой, за ней идут таблицы accountи employee.
Поскольку подблоки on не изменились, результаты такие же.Запрос, использующий три или более таблиц, можно представить какснежный ком, катящийся с горы. Первые две таблицы запускают этотком, а каждая последующая таблица вносит в него свою лепту. Снеж ный ком можно рассматривать как промежуточный результирующийнабор (intermediate result set), который подбирает все больше и большестолбцов по мере соединения с последующими таблицами. Поэтомутаблица employee в действительности была соединена не с таблицей account, а с промежуточным результирующим набором, созданным присоединении таблиц customer и account.
(Если интересно, откуда взялсяснежный ком: я писал эту главу глубокой новоанглийской зимой –уже выпало 110 дюймов и завтра будет еще. Вот радость то!)Применение подзапросов в качестве таблицВы уже посмотрели несколько примеров запросов с тремя таблицами,но хочется особо отметить одну из разновидностей таких запросов. Чтоделать, если некоторые таблицы формируются подзапросами? Подза просам посвящена глава 9, но концепция подзапроса в блоке from ужебыла представлена в предыдущей главе. Вот другая версия приведен ного ранее запроса (выбор всех счетов, открытых опытными операцио нистами, в настоящее время работающими в отделении Woburn), в ко тором таблица account соединяется с подзапросами к таблицам branch(отделение) и employee:1 SELECT a.account_id, a.cust_id, a.open_date, a.product_cd2 FROM account a INNER JOIN3(SELECT emp_id, assigned_branch_id4FROM employee5WHERE start_date <= '20030101'6AND (title = 'Teller' OR title = 'Head Teller')) e7ON a.open_emp_id = e.emp_id8INNER JOIN9(SELECT branch_id10FROM branch11WHERE name = 'Woburn Branch') b12 ON e.assigned_branch_id = b.branch_id;Первый подзапрос, начинающийся в строке 3 и имеющий псевдоним e,находит всех опытных операционистов.
Второй подзапрос, начинаю щийся в строке 9 и имеющий псевдоним b, выбирает ID отделения Wo burn. Сначала таблица account соединяется с подзапросом по опытным100Глава 5. Запрос к нескольким таблицамоперационистам с помощью ID сотрудников, а затем результирующаятаблица соединяется с подзапросом по отделению Woburn на основеID филиала. Результаты аналогичны предыдущей версии запроса (по пробуйте и убедитесь сами), но на вид запросы очень отличаются другот друга.На самом деле здесь нет ничего необычного, но осознать происходящееможно не сразу. Обратите внимание, например, на отсутствие блокаwhere в основном запросе. Поскольку все условия фильтрации касаютсятаблиц employee и branch, они находятся в подзапросах.
В основном за просе никакие условия фильтрации не нужны. Единственный способпроиллюстрировать происходящее – выполнить подзапросы и посмот реть на результирующие наборы. Вот результаты выполнения первогоподзапроса к таблице employee:mysql> SELECT emp_id, assigned_branch_id> FROM employee> WHERE start_date <= '20030101'> AND (title = 'Teller' OR title = 'Head Teller');+++| emp_id | assigned_branch_id |+++|8 |1 ||9 |1 ||10 |2 ||11 |2 ||13 |3 ||14 |3 ||16 |4 ||17 |4 ||18 |4 |+++9 rows in set (0.03 sec)Таким образом, результирующий набор состоит из набора ID сотруд ников и ID соответствующих им отделений. Соединив таблицу accountпосредством столбца emp_id, имеем промежуточный результирующийнабор, содержащий все строки таблицы account и дополнительныйстолбец с ID отделения, в котором работает сотрудник, открывший каж дый из счетов. Вот результаты второго подзапроса к таблице branch:mysql> SELECT branch_id> FROM branch> WHERE name = 'Woburn Branch';++| branch_id |++|2 |++1 row in set (0.02 sec)Соединение трех и более таблиц101По этому запросу возвращается одна единственная строка с однимстолбцом: ID отделения Woburn.
Эта таблица соединена со столбцомassigned_branch_id промежуточного результирующего набора, что по зволяет отсеять из окончательного результирующего набора все счета,открытые несотрудниками отделения Woburn.Повторное использование таблицыПри соединении нескольких таблиц может обнаружиться, что требует ся неоднократно соединять одну и ту же таблицу. В нашем примере БДвнешние ключи к таблице branch присутствуют как в таблице account(отделение, в котором был открыт счет), так и в таблице employee (отде ление, в котором работает сотрудник). Если в результирующий набордолжны войти оба отделения, таблицу branch можно включить в блокfrom дважды, в первый раз соединив ее с таблицей employee, а второйраз – с таблицей account.
Это сработает, если каждому экземпляру таб лицы branch присвоить свой псевдоним, чтобы сервер знал, на какойэкземпляр делается ссылка:mysql> SELECT a.account_id, e.emp_id,> b_a.name open_branch, b_e.name emp_branch> FROM account a INNER JOIN branch b_a> ON a.open_branch_id = b_a.branch_id> INNER JOIN employee e> ON a.open_emp_id = e.emp_id> INNER JOIN branch b_e> ON e.assigned_branch_id = b_e.branch_id> WHERE a.product_cd = 'CHK';+++++| account_id | emp_id | open_branch | emp_branch|+++++|8 |1 | Headquarters | Headquarters ||12 |1 | Headquarters | Headquarters ||17 |1 | Headquarters | Headquarters ||1 |10 | Woburn Branch | Woburn Branch ||4 |10 | Woburn Branch | Woburn Branch ||6 |13 | Quincy Branch | Quincy Branch ||11 |16 | So.
NH Branch | So. NH Branch ||15 |16 | So. NH Branch | So. NH Branch ||20 |16 | So. NH Branch | So. NH Branch ||23 |16 | So. NH Branch | So. NH Branch |+++++10 rows in set (0.07 sec)Этот запрос показывает, кто открыл каждый текущий счет, в каком от делении это произошло и к какому отделению приписан в настоящеевремя сотрудник, открывший счет. Таблица branch включена дваждыпод псевдонимами b_a и b_e.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
