Выборка данных из нескольких таблиц

1. Выборка данных из нескольких таблиц

А.М. Гудов

2.

Цели занятия
• Команды SELECT для выборки данных из более, чем
одной таблицы с помощью эквисоединений и прочих
видов соединений.
• Использование внешних соединений для просмотра
данных, не удовлетворяющих обычным условиям
соединения.
• Соединение таблицы с собой.
А.М. Гудов

3.

Что такое соединение?
• Соединение используется для запроса данных из
более, чем одной таблицы.
• Строки соединяются с помощью общих значений как правило, значений первичных и внешних
ключей.
• Способы соединения:
– Эквисоединение;
– Не-эквисоединение
– Внешнее соединение;
– Соединение таблицы с собой;
– Операторы множеств.
А.М. Гудов

4.

Отношения между таблицами
S_EMP Table
ID LAST_NAME
-- --------------1 Velasquez
2 Ngao
3 Nagayama
4 Quick-To-See
5 Ropeburn
6 Urguhart
7 Menchu
8 Biri
9 Catchpole
10 Havel
11 Magee
12 Giljum
13 Sedeghi
14 Nguyen
15 Dumas
16 Maduro
DEPT_ID
------50
41
31
S_DEPT Table
ID NAME
REGION_ID
10
-- ----------------------50
30 Finance41
1
31 Sales 42
1
32 Sales 43
2
43 Operations
44 S_REGION Table 3
50 Administration
1
45 ID NAME
31 -- --------------------32
1 North America
33
2 South America
34
3 Africa / Middle East
35
4 Asia
41
5 Europe
А.М. Гудов

5.

Декартово произведение
• Декартово произведения образуется , если:
– Опущено условие соединения.
– Условие соединения недействительно.
– Все строки первой таблицы соединяются со
всеми строками второй таблицы.
• Во избежание получения декартова произведения
предложение WHERE всегда должно включать
допустимое условие соединения.
А.М. Гудов

6.

Простой запрос с соединением
SELECT
FROM
table.column, table.column
table1, table2
WHERE
table1.column1 = table2.column2;
• В предложении WHERE указывается допустимое
условие соединения.
• Читать предложение легче, если каждому имени
столбца предшествует имя таблицы.
• Если столбцы с одинаковыми именами имеются
более, чем в одной таблице, имя таблицы перед
именем столбца обязательно.
А.М. Гудов

7.

Эквисоединение: пример
Сервер
S_EMP
LAST_NAME DEPT_ID
--------- ------Velasquez
50
Ngao
41
Nagayama
31
Ropeburn
50
Urguhart
41
Menchu
42
Biri
43
Havel
45
...
S_DEPT
ID NAME
-- --------------50 Administration
41 Operations
31 Sales
50 Administration
41 Operations
42 Operations
43 Operations
45 Operations
...
А.М. Гудов

8.

Различение столбцов с одинаковыми именами
• Для различения одноименных столбцов из разных
таблиц используются префиксы в виде имен
таблиц.
• Использование префиксов в виде имен таблиц
увеличивает производительность.
• Одноименные столбцы из разных таблиц можно
различать по их псевдонимам.
А.М. Гудов

9.

Дополнительные условия поиска и оператор AND
Сервер
S_EMP
LAST_NAME DEPT_ID
--------- ------Velasquez
50
Ngao
41
Nagayama
31
Ropeburn
50
Urguhart
41
Menchu
42
Biri
43
Havel
45
...
S_DEPT
ID NAME
-- --------------50 Administration
41 Operations
31 Sales
50 Administration
41 Operations
42 Operations
43 Operations
45 Operations
...
Дополнительное
условие
А.М. Гудов

10.

Псевдонимы таблиц
• Перед именами столбцов рекомендуется указывать
псевдонимы таблиц.
• Псевдонимы таблиц действительны только для
данной команды SELECT.
• Если псевдоним таблицы создан, перед ссылкой на
столбец следует указывать его, а не имя таблицы.
SQL>
2
3
4
5
SELECT
c.name "Customer Name",
c.region_id "Region ID",
r.name "Region Name"
FROM
s_customer c, s_region r
WHERE
c.region_id = r.id;
А.М. Гудов

11.

Не-эквисоединения: пример
• Не-эквисоединение возникает в случае, если ни один
столбец одной таблицы не соответствует точно
столбцу другой таблицы.
• Условие соединения содержит оператор, не
являющийся оператором равенства (=)
SQL> SELECT
2
3
FROM
WHERE
e.ename, e.job, e.sal, s.grade
emp e, salgrade s
e.sal BETWEEN s.losal AND s.hisal;
А.М. Гудов

12.

Внешние соединения
• Внешнее соединение используется для выборки
строк, не удовлетворяющих обычным условиям
соединения.
• Оператором внешнего соединения является знак
плюс заключенный в скобки (+).
• Этот оператор указывается с той стороны, где нет
значения, по которому можно было бы произвести
соединение.
SELECT
table.column, table.column
FROM
table1, table2
WHEREtable1.column(+) = table2.column;
А.М. Гудов

13.

Внешние соединения
Сервер
S_CUSTOMER
S_EMP
Клиент “Sweet
Rock Sports
не имеет
торгового
представителя
LAST_NAME
--------Magee
Magee
Magee
Giljum
Giljum
Sedeghi
Dumas
ID
-11
11
11
12
12
13
15
SALES_
REP_ID
-----11
11
11
12
12
13
15
NAME
-------------Womansport
Beisbol Si!
Ojibway Retail
Unisports
Futbol Sonora
Hamada Sport
Sportique
Sweet Rock Sports
А.М. Гудов

14.

Внешние соединения: пример
Вывод имени торгового представителя и названия каждой фирмыклиента, включая тех, кто не имеет торгового представителя.
SQL>
2
3
4
SELECT
FROM
WHERE
ORDER BY
e.last_name, e.id, c.name
s_emp e, s_customer c
e.id (+) = c.sales_rep_id
e.id;
• Оператор внешнего соединения может использоваться
только на одной стороне выражения.
• Условие, предполагающее внешнее соединение, не может:
– Использовать оператор IN.
– Быть связанным с другими условиями с помощью
оператора OR.
А.М. Гудов

15.

Соединение таблицы с собой
Сервер
S_EMP (WORKER)
S_EMP (MANAGER)
LAST_NAME MANAGER_ID
ID
--------- ----------Ngao
1
1
Nagayama
1
1
Ropeburn
1
1
Urguhart
2
2
Menchu
2
2
Biri
2
2
Magee
3
3
Giljum
3
3
...
...
LAST_NAME
---------Velasquez
Velasquez
Velasquez
Ngao
Ngao
Ngao
Nagayma
Nagayma
А.М. Гудов

16.

Соединение таблицы с собой
• Строки таблицы соединяются со строками этой же
самой таблицы.
• В предложении FROM наличие двух таблиц
имитируется путем использования двух псевдонимов
таблицы.
SQL> SELECT
worker.last_name||' works for '||
2 manager.last_name
3
4
FROM
WHERE
s_emp worker, s_emp manager
worker.manager_id = manager.id;
А.М. Гудов

17.

Заключение
• Имеется несколько способов соединения таблиц:
– Эквисоединение
– Не-эквисоединение
– Внешнее соединение
– Соединение с собой
• Отсутствие предложения WHERE приводит к
возникновению декартова произведения таблиц.
• Использование псевдонимов таблиц ускоряет доступ к
базе данных.
• Для соединения таблицы с собой использование
псевдонимов обязательно.
А.М. Гудов

18.

Обзор практического занятия
• Соединение таблиц с помощью
эквисоединений.
• Выполнение внешних соединений и
соединений таблицы с собой.
• Включение дополнительных условий.
А.М. Гудов

19.

Задания для практического занятия
Для выполнения заданий используются таблицы S_EMP, S_DEPT, S_CUSTOMER,
S_REGION, S_ORD, S_ITEM, S_PRJDUCT.
1. Вывести отчет, содержащий номер отдела, фамилию и название отдела для
каждого служащего.
2. Вывести фамилию и название отдела для служащего по фамилии Smith.
3. Вывести наименование, номер и заказанное количество товара по всем позициям
заказа с номером 101.
4. Для всех заказчиков и всех их заказов вывести номер заказчика, его
наименование и номер его заказа. Если клиент не сделал заказов, его номер и
наименование должны быть в списке все-равно.
5. Вывести фамилии и номера всех служащих вместе с фамилиями и номерами их
менеджеров.
6. Для каждого заказчика, сумма заказа которого превышает 100 000, вывести его
наименование, заказанные им товары и их количество.
А.М. Гудов