Внешние соединения

До сих пор ни в одном из приведенных примеров, включающих запросы к нескольким таблицам, не поднимался вопрос о том, что не все строки таблицы могут соответствовать условиям соединения. Например, при соединении таблицы account с таблицей customer ничего не было сказа_ но о возможности отсутствия для значения столбца cust_id таблицы account соответствующего значения в столбце cust_id таблицы customer. Если бы такое случилось, некоторые строки одной из таблиц не вошли бы в результирующий набор.

На всякий случай давайте проверим данные таблицы. Вот столбцы ac_ count_id и cust_id таблицы account:

mysql> SELECT account_id, cust_id

_> FROM account;

+____________+_________+ | account_id | cust_id | +____________+_________+

|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|

				Глава 10. И снова соединения
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|

+____________+_________+ 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;

Внешние соединения

+____________+_________+ | account_id | cust_id | +____________+_________+

|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|
|		|		|

+____________+_________+ 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 | +____________+_________+________________________+

|		|		|	Chilton	Engineering	|
|		|		|	Chilton	Engineering	|

| 22 | 11 | Northeast Cooling Inc. |

|		|		|	Superior Auto	Body	|
|		|		|	AAA Insurance	Inc.	|

+____________+_________+________________________+ 5 rows in set (0.00 sec)

Теперь в результирующем наборе только пять строк вместо 24. Загля_ нем в таблицу business, чтобы понять, почему так произошло:

mysql> SELECT cust_id, name

_> FROM business;

				Глава 10. И снова соединения
+_________+________________________+
| cust_id |	name	|
+_________+________________________+
|		|	Chilton Engineering	|
|		|	Northeast Cooling Inc. |
|		|	Superior Auto Body	|
|		|	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 | +____________+_________+________________________+

|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| NULL	|
|		|		| Chilton Engineering	|
|		|		| Chilton Engineering	|

| 22 | 11 | Northeast Cooling Inc. |

|		|		|	Superior Auto	Body	|
|		|		|	AAA Insurance	Inc.	|

+____________+_________+________________________+ 24 rows in set (0.00 sec)

Внешние соединения

Внешнее соединение включает все строки одной таблицы и вводит дан_ ные второй таблицы только в случае обнаружения соответствующих строк. В данном случае в результат вошли все строки таблицы 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 | +____________+_________+__________+_________+

|		|		| James	| Hadley	|
|		|		| James	| Hadley	|
|		|		| James	| Hadley	|
|		|		| Susan	| Tingley |
|		|		| Susan	| Tingley |
|		|		| Frank	| Tucker	|
|		|		| Frank	| Tucker	|
|		|		| John	| Hayward |
|		|		| John	| Hayward |
|		|		| John	| Hayward |
|		|		| Charles	| Frasier |
|		|		| John	| Spencer |
|		|		| John	| Spencer |
|		|		| Margaret	| Young	|
|		|		| Louis	| Blake	|
|		|		| Louis	| Blake	|
|		|		| Richard	| Farley	|
|		|		| Richard	| Farley	|
|		|		| Richard	| Farley	|
|		|		| NULL	| NULL	|
|		|		| NULL	| NULL	|
|		|		| NULL	| NULL	|
|		|		| NULL	| NULL	|
|		|		| NULL	| NULL	|

+____________+_________+__________+_________+ 24 rows in set (0.00 sec)

Этот запрос, по сути, противоположен предыдущему: выводятся име_ на и фамилии физических лиц, тогда как для юридических лиц эти столбцы имеют значение null.

Дата добавления: 2015-08-17