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

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

SELECT e.fname, e.lname, d.name
> FROM employee e INNER JOIN department d
> ON e.dept_id = d.dept_id;
+
+
+
+
| fname | lname | name |
+
+
+
+
| Susan | Hawthorne | Operations |
| Helen | Fleming | Operations |
| Chris | Tucker | Operations |
| Sarah | Parker | Operations |
| Jane | Grossman | Operations |
| Paula | Roberts | Operations |
| Thomas | Ziegler | Operations |
| Samantha | Jameson | Operations |
| John | Blake | Operations |
| Cindy | Mason | Operations |
| Frank | Portman | Operations |
| Theresa | Markham | Operations |
| Beth | Fowler | Operations |
| Rick | Tulman | Operations |
| John | Gooding | Loans |
| Michael | Smith | Administration |
| Susan | Barker | Administration |
| Robert | Tyler | Administration |
+
+
+
+
18 rows in set (0.00 sec)
Если тип соединения не задан, сервер по умолчанию проведет внутрен нее соединение. Однако, как выяснится в главе 10, есть несколько ти пов соединений, поэтому указание точного типа требуемого соедине ния должно войти в привычку.
Если имена столбцов, используемых для соединения двух таблиц, сов падают (что имеет место в предыдущем запросе), можно вместо под блока on применить подблок using:
mysql> SELECT e.fname, e.lname, d.name
> FROM employee e INNER JOIN department d
> USING (dept_id);
+
+
+
+

Что такое соединение?
95
| fname | lname | name |
+
+
+
+
| Susan | Hawthorne | Operations |
| Helen | Fleming | Operations |
| Chris | Tucker | Operations |
| Sarah | Parker | Operations |
| Jane | Grossman | Operations |
| Paula | Roberts | Operations |
| Thomas | Ziegler | Operations |
| Samantha | Jameson | Operations |
| John | Blake | Operations |
| Cindy | Mason | Operations |
| Frank | Portman | Operations |
| Theresa | Markham | Operations |
| Beth | Fowler | Operations |
| Rick | Tulman | Operations |
| John | Gooding | Loans |
| Michael | Smith | Administration |
| Susan | Barker | Administration |
| Robert | Tyler | Administration |
+
+
+
+
18 rows in set (0.01 sec)
Поскольку using – сокращенная запись, которая может использовать ся только в определенной ситуации, во избежание путаницы я всегда предпочитаю подблок on.
ANSI синтаксис соединения
Нотация, используемая в данной книге для соединения таблиц, была введена в версии SQL92 стандарта ANSI SQL. Во всех основных СУБД
(Oracle Database, Microsoft SQL Server, MySQL, IBM DB2 Universal Da tabase, Sybase Adaptive Server) принят синтаксис соединения SQL92.
Поскольку многие серверы существовали еще до выхода специфика ции SQL92, все они включают и старый синтаксис соединения. Напри мер, всем этим серверам был бы понятен такой вариант предыдущего запроса:
mysql> SELECT e.fname, e.lname, d.name
> FROM employee e, department d
> WHERE e.dept_id = d.dept_id;
+
+
+
+
| fname | lname | name |
+
+
+
+
| Susan | Hawthorne | Operations |
| Helen | Fleming | Operations |
| Chris | Tucker | Operations |
| Sarah | Parker | Operations |
| Jane | Grossman | Operations |
| Paula | Roberts | Operations |
| Thomas | Ziegler | Operations |

96
Глава 5. Запрос к нескольким таблицам
| Samantha | Jameson | Operations |
| John | Blake | Operations |
| Cindy | Mason | Operations |
| Frank | Portman | Operations |
| Theresa | Markham | Operations |
| Beth | Fowler | Operations |
| Rick | Tulman | Operations |
| John | Gooding | Loans |
| Michael | Smith | Administration |
| Susan | Barker | Administration |
| Robert | Tyler | Administration |
+
+
+
+
18 rows in set (0.01 sec)
Этот старый метод описания соединений не включает подблок on. Таб лицы указаны в блоке from через запятую, а условия соединения вклю чены в блок where. Хотя можно игнорировать синтаксис SQL92 в поль зу старого синтаксиса соединений, у синтаксиса ANSI есть следующие преимущества:
•
Условия соединения и фильтрации разнесены в два разных блока
(подблок on и блок where соответственно), что упрощает понимание запроса.
•
Условия соединения для каждой пары таблиц содержатся в собст венном блоке on, что уменьшает вероятность ошибочного исключе ния части соединения.
•
Запросы, использующие синтаксис соединения SQL92, портируют ся на разные серверы БД, тогда как старый синтаксис немного от личается для каждого сервера.
Преимущества синтаксиса соединения SQL92 заметнее в сложных за просах, включающих как условия соединения, так и условия фильт рации. Рассмотрим следующий запрос, по которому возвращаются все счета, открытые опытными операционистами (нанятыми до 2003 го да), в настоящее время приписанными к отделению Woburn:
mysql> SELECT a.account_id, a.cust_id, a.open_date, a.product_cd
> FROM account a, branch b, employee e
> WHERE a.open_emp_id = e.emp_id
> AND e.start_date <= '2003 01 01'
> AND e.assigned_branch_id = b.branch_id
> AND (e.title = 'Teller' OR e.title = 'Head Teller')
> AND b.name = 'Woburn Branch';
+
+
+
+
+
| account_id | cust_id | open_date | product_cd |
+
+
+
+
+
| 1 | 1 | 2000 01 15 | CHK |
| 2 | 1 | 2000 01 15 | SAV |
| 3 | 1 | 2004 06 30 | CD |
| 4 | 2 | 2001 03 12 | CHK |
| 5 | 2 | 2001 03 12 | SAV |

Соединение трех и более таблиц
97
| 14 | 7 | 2004 01 12 | CD |
| 22 | 11 | 2004 03 22 | BUS |
+
+
+
+
+
7 rows in set (0.01 sec)
В этом запросе не так просто определить, какие условия блока where являются условиями соединения, а какие – условиями фильтрации.
Также не вполне очевидно, какой тип соединения используется (для установления типа соединения необходимо внимательно рассмотреть условия соединения в блоке where – нет ли там каких либо специаль ных символов), и сложно определить, не были ли упущены какие либо условия соединения. Вот тот же запрос, записанный с использованием синтаксиса соединения 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 <= '2003 01 01'
> AND (e.title = 'Teller' OR e.title = 'Head Teller')
> AND b.name = 'Woburn Branch';
+
+
+
+
+
| account_id | cust_id | open_date | product_cd |
+
+
+
+
+
| 1 | 1 | 2000 01 15 | CHK |
| 2 | 1 | 2000 01 15 | SAV |
| 3 | 1 | 2004 06 30 | CD |
| 4 | 2 | 2001 03 12 | CHK |
| 5 | 2 | 2001 03 12 | SAV |
| 14 | 7 | 2004 01 12 | CD |
| 22 | 11 | 2004 03 22 | 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_id

98
Глава 5. Запрос к нескольким таблицам
> WHERE c.cust_type_cd = 'B';
+
+
+
| account_id | fed_id |
+
+
+
| 20 | 04 1111111 |
| 21 | 04 1111111 |
| 22 | 04 2222222 |
| 23 | 04 3333333 |
| 24 | 04 4444444 |
+
+
+
5 rows in set (0.06 sec)
Этот запрос, возвращающий ID счета и идентификационный номер фе дерального налога для всех бизнес счетов, на данный момент должен быть абсолютно понятным. Однако если добавить в запрос таблицу emp loyee для получения имени операциониста, открывшего каждый из сче тов, он примет следующий вид:
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 | 04 1111111 | Theresa | Markham |
| 21 | 04 1111111 | Theresa | Markham |
| 22 | 04 2222222 | Paula | Roberts |
| 23 | 04 3333333 | Theresa | Markham |
| 24 | 04 4444444 | 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 | 04 1111111 | Theresa | Markham |

Соединение трех и более таблиц
99
| 21 | 04 1111111 | Theresa | Markham |
| 22 | 04 2222222 | Paula | Roberts |
| 23 | 04 3333333 | Theresa | Markham |
| 24 | 04 4444444 | John | Blake |
+
+
+
+
+
5 rows in set (0.00 sec)
Теперь таблица customer стоит первой, за ней идут таблицы account и employee. Поскольку подблоки on не изменились, результаты такие же.
Запрос, использующий три или более таблиц, можно представить как снежный ком, катящийся с горы. Первые две таблицы запускают этот ком, а каждая последующая таблица вносит в него свою лепту. Снеж ный ком можно рассматривать как промежуточный результирующий
набор
(intermediate result set), который подбирает все больше и больше столбцов по мере соединения с последующими таблицами. Поэтому таблица employee в действительности была соединена не с таблицей ac count
, а с промежуточным результирующим набором, созданным при соединении таблиц customer и account. (Если интересно, откуда взялся снежный ком: я писал эту главу глубокой новоанглийской зимой –
уже выпало 110 дюймов и завтра будет еще. Вот радость то!)
Применение подзапросов в качестве таблиц
Вы уже посмотрели несколько примеров запросов с тремя таблицами,
но хочется особо отметить одну из разновидностей таких запросов. Что делать, если некоторые таблицы формируются подзапросами? Подза просам посвящена глава 9, но концепция подзапроса в блоке from уже была представлена в предыдущей главе. Вот другая версия приведен ного ранее запроса (выбор всех счетов, открытых опытными операцио нистами, в настоящее время работающими в отделении Woburn), в ко тором таблица account соединяется с подзапросами к таблицам branch
(отделение) и employee:
1 SELECT a.account_id, a.cust_id, a.open_date, a.product_cd
2 FROM account a INNER JOIN
3 (SELECT emp_id, assigned_branch_id
4 FROM employee
5 WHERE start_date <= '2003 01 01'
6 AND (title = 'Teller' OR title = 'Head Teller')) e
7 ON a.open_emp_id = e.emp_id
8 INNER JOIN
9 (SELECT branch_id
10 FROM branch
11 WHERE name = 'Woburn Branch') b
12 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 <= '2003 01 01'
> 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. Благодаря присваиванию разных псевдо нимов каждому экземпляру таблицы branch, сервер сможет определить экземпляр, на который делается ссылка, – соединенный с таблицей

102
Глава 5. Запрос к нескольким таблицам account или с таблицей employee. Таким образом, имеем пример запроса,
в котором использование псевдонимов таблиц является обязательным.
Рекурсивные соединения
Можно не только несколько раз включать одну и ту же таблицу в один запрос, фактически можно соединить таблицу с самой собой. Поначалу это может показаться странным, но для этого есть веские основания.
В таблице employee, например, есть рекурсивный внешний ключ (self
referencing foreign key
). Это означает, что она включает столбец (supe rior_emp_id
), указывающий на первичный ключ в рамках той же табли цы. Этот столбец указывает на начальника сотрудника (если только это не сам босс – тогда столбец имеет значение null). С помощью рекур
сивного соединения
(self join) можно создать запрос, в результате вы полнения которого выводится список всех сотрудников с указанием имен их начальников:
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. Это еще один пример запроса, для ко

Сравнение эквисоединений с неэквисоединениями