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

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

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

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

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

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

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

7. Эквисоединение: пример Сервер S_EMP S_DEPT LAST_NAME DEPT_ID ID NAME--------- ------- -- ---------------Velasquez 50 50 AdministrationNgao 41 41 OperationsNagayama 31 31 SalesRopeburn 50 50 AdministrationUrguhart 41 41 OperationsMenchu 42 42 OperationsBiri 43 43 OperationsHavel 45 45 Operations... ... А.М. Гудов

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

9. Сервер Дополнительные условия поиска и оператор AND S_EMP S_DEPT LAST_NAME DEPT_ID ID NAME--------- ------- -- ---------------Velasquez 50 50 AdministrationNgao 41 41 OperationsNagayama 31 31 SalesRopeburn 50 50 AdministrationUrguhart 41 41 OperationsMenchu 42 42 OperationsBiri 43 43 OperationsHavel 45 45 Operations... ... Дополнительное условие А.М. Гудов

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

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

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

13. Внешние соединения Сервер S_CUSTOMER S_EMP SALES_LAST_NAME ID REP_ID NAME--------- -- ------ --------------Magee 11 11 WomansportMagee 11 11 Beisbol Si!Magee 11 11 Ojibway RetailGiljum 12 12 UnisportsGiljum 12 12 Futbol SonoraSedeghi 13 13 Hamada SportDumas 15 15 Sportique Sweet Rock Sports Клиент “Sweet Rock Sportsне имеет торгового представителя А.М. Гудов

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

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

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