Многотабличные запросы на чтение и объединение таблиц

Декартово произведение Многотабличные запросы Псевдонимы таблиц

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

Рисунок 1 Таблицы, связанные отношением «первичный ключ/внешний ключ» Основой многотабличного запроса является соединение таблиц, получающееся в результате формирования пар строк путем…

Декартово произведение

SQL> SELECT d.deptno, dname, e.deptno, ename FROM dept d, emp e; DEPTNO DNAME DEPTNO ENAME --------- -------------- --------- ----------

Многотабличные запросы

SELECT [ALL | DISTINCT] список_выбора

[WHERE условие_соединения] [ORDER BY {столбец | псевдоним | позиция} [ASC | DESC]; Для корректного построения многотабличного запроса необходимо задать условие соединения таблиц (реляционная операция…

Псевдонимы таблиц

SQL> SELECT d.deptno, d.dname, e.deptno, e.ename FROM dept d, emp e 2 WHERE d.deptno=e.deptno; DEPTNO DNAME DEPTNO ENAME

Чтение всех столбцов

Многотабличный запрос можно использовать для чтения всех столбцов соединяемых таблиц.

SELECT * FROM таблица1, таблица2 WHERE таблица1.столбец= таблица2.столбец

возвращает все столбцы из обеих таблиц

SELECT таблица1.*, таблица2.столбец FROM таблица1, таблица2

WHERE таблица1.столбец= таблица2.столбец

возвращает все столбцы из первой таблицы и заданные столбцы из второй таблицы

SQL> SELECT * FROM dept d, emp e WHERE d.deptno=e.deptno AND d.deptno>=30;

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------- ------- --------- ------ -------- --------- ----- --------- ----- ------ -------

30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

30 SALES CHICAGO 7900 JAMES CLERK 7698 03-DEC-81 950 30

30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

6 rows selected.

SQL> SELECT d.*, e.ename FROM dept d, emp e

2 WHERE d.deptno=e.deptno AND d.deptno>=30;

DEPTNO DNAME LOC ENAME

--------- -------------- ------------- ----------

30 SALES CHICAGO ALLEN

30 SALES CHICAGO BLAKE

30 SALES CHICAGO MARTIN

30 SALES CHICAGO JAMES

30 SALES CHICAGO TURNER

30 SALES CHICAGO WARD

6 rows selected.

Порядок выполнения многотабличного запроса

2. Условие соединения, заданное в предложении WHERE, применяется ко всем строкам декартова произведения таблиц. Строки, для которых условие… 3. Для каждой из оставшихся строк вычисляется каждое значение в списке… 4. Если задано ключевое слово DISTINCT, удаляются повторяющиеся строки.

Эквисоединения

Соединение таблиц на основе точного равенства между значениями двух столбцов называется эквисоединением или соединением по равенству. Обычно эти столбцы являются компонентами первичного и внешнего ключа

SELECT таблица1.столбец, …, таблица2.столбец, …

FROM таблица1, таблица2

WHERE таблица1.столбец = таблица2.столбец

SQL> SELECT d.deptno, d.dname, e.ename

2 FROM dept d, emp e

3 WHERE d.deptno=e.deptno;

DEPTNO DNAME ENAME

--------- -------------- ----------

10 ACCOUNTING CLARK

10 ACCOUNTING KING

10 ACCOUNTING MILLER

20 RESEARCH SMITH

20 RESEARCH ADAMS

20 RESEARCH FORD

20 RESEARCH SCOTT

20 RESEARCH JONES

30 SALES ALLEN

30 SALES BLAKE

30 SALES MARTIN

30 SALES JAMES

30 SALES TURNER

30 SALES WARD

14 rows selected.

Многотабличный запрос возвращает все строки, соответствующие условию соединения. Если Вы хотите сузить результаты запроса, необходимо комбинировать условия соединения с другими поисковыми условиями. Дополнительное условие поиска добавляется с помощью оператора AND.

SQL> SELECT d.deptno, d.dname, e.ename

2 FROM dept d, emp e

3 WHERE d.deptno=e.deptno AND d.deptno<30;

DEPTNO DNAME ENAME

--------- -------------- ----------

10 ACCOUNTING CLARK

10 ACCOUNTING KING

10 ACCOUNTING MILLER

20 RESEARCH SMITH

20 RESEARCH ADAMS

20 RESEARCH FORD

20 RESEARCH SCOTT

20 RESEARCH JONES

8 rows selected.

Не-эквисоединения

Рисунок 2 Несвязанные таблицы SQL> SELECT e.ename, e.job, e.sal, s.grade

Соединение таблицы с самой собой

Рисунок 3 Рекурсивная связь внутри таблицы SQL> SELECT m.ename || ' is manager of ' || e.ename " "

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

SELECT таблица1.столбец, таблица2.столбец

FROM таблица1, таблица2

WHERE таблица1.столбец = таблица2.столбец(+)

SELECT таблица1.столбец, таблица2.столбец

FROM таблица1, таблица2

WHERE таблица1.столбец(+) = таблица2.столбец

SQL> SELECT * FROM dept;

DEPTNO DNAME LOC

--------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> SELECT d.deptno, d.dname, e.ename

2 FROM dept d, emp e

3 WHERE d.deptno=e.deptno AND d.deptno>=30;

DEPTNO DNAME ENAME

--------- -------------- ----------

30 SALES ALLEN

30 SALES BLAKE

30 SALES MARTIN

30 SALES JAMES

30 SALES TURNER

30 SALES WARD

6 rows selected.

SQL> SELECT d.deptno, d.dname, e.ename

2 FROM dept d, emp e

3 WHERE d.deptno=e.deptno(+) AND d.deptno>=30;

DEPTNO DNAME ENAME

--------- -------------- ----------

30 SALES ALLEN

30 SALES BLAKE

30 SALES MARTIN

30 SALES JAMES

30 SALES TURNER

30 SALES WARD

40 OPERATIONS

7 rows selected.

Операторы множеств

Иногда возникает необходимость объединить строки, возвращаемые несколькими запросами, в одно результирующее множество, не сравнивая содержимое соответствующих столбцов. SQL поддерживает такую возможность с помощью операторов множеств. Операторы множеств комбинируют строки, возвращаемые двумя запросами в одно результирующее множество. Количество и типы данных колонок в объединяемых с помощью операторов множеств запросах должны совпадать. Длины и имена колонок могут быть разными. Если имена колонок отличаются, то имена колонок результирующего множества будут совпадать с именами колонок первой таблицы.

§ UNION – возвращает все строки, выбранные первым и вторым запросами (реляционная операция «Объединение»)

§ UNION ALL – возвращает все строки, включая повторяющиеся, выбранные первым и вторым запросами

§ INTERSECT – возвращает строки, выбранные и первым, и вторым запросами (реляционная операция «Пересечение»)

§ MINUS - возвращает строки, выбранные первым запросом, из которых исключены строки, выбранные вторым запросом (реляционная операция «Вычитание»)

SELECT [ALL | DISTINCT] список_выбора FROM таблица1 [WHERE условие1]

{UNION | UNION ALL | INTERSECT | MINUS}

SELECT [ALL | DISTINCT] список_выбора FROM таблица2 [WHERE условие2]

[ORDER BY {столбец | псевдоним | позиция} [ASC | DESC]];

SQL> SELECT * FROM emp_bonus;

ENAME JOB BONUS

---------- --------- ---------

SCOTT ANALYST 3000

FORD ANALYST 3000

STONE ADVISER 2000

SQL> SELECT ename, job, sal FROM emp

2 WHERE job<='CLERK';

ENAME JOB SAL

---------- --------- ---------

SMITH CLERK 800

SCOTT ANALYST 3000

ADAMS CLERK 1100

JAMES CLERK 950

FORD ANALYST 3000

MILLER CLERK 1300

6 rows selected.

SQL> SELECT * FROM emp_bonus

2 UNION

3 SELECT ename, job, sal FROM emp WHERE job<='CLERK';

ENAME JOB BONUS

---------- --------- ---------

ADAMS CLERK 1100

FORD ANALYST 3000

JAMES CLERK 950

MILLER CLERK 1300

SCOTT ANALYST 3000

SMITH CLERK 800

STONE ADVISER 2000

7 rows selected.

SQL> SELECT * FROM emp_bonus

2 UNION ALL

3 SELECT ename, job, sal FROM emp WHERE job<='CLERK';

ENAME JOB BONUS

---------- --------- ---------

SCOTT ANALYST 3000

FORD ANALYST 3000

STONE ADVISER 2000

SMITH CLERK 800

SCOTT ANALYST 3000

ADAMS CLERK 1100

JAMES CLERK 950

FORD ANALYST 3000

MILLER CLERK 1300

9 rows selected.

SQL> SELECT * FROM emp_bonus

2 INTERSECT

3 SELECT ename, job, sal FROM emp WHERE job<='CLERK';

ENAME JOB BONUS

---------- --------- ---------

FORD ANALYST 3000

SCOTT ANALYST 3000

SQL> SELECT * FROM emp_bonus

2 MINUS

3 SELECT ename, job, sal FROM emp

4 WHERE job<='CLERK';

ENAME JOB BONUS

---------- --------- ---------

STONE ADVISER 2000

SQL> SELECT * FROM emp_bonus

2 UNION

3 SELECT ename, job, sal FROM emp WHERE job<='CLERK'

4 ORDER BY 1;

ENAME JOB BONUS

---------- --------- ---------

ADAMS CLERK 1100

FORD ANALYST 3000

JAMES CLERK 950

MILLER CLERK 1300

SCOTT ANALYST 3000

SMITH CLERK 800

STONE ADVISER 2000

7 rows selected.

Порядок выполнения запроса с использованием операторов множеств

1. Выполняется каждый оператор SELECT, участвующий в объединении.

§ Формируется декартово произведение таблиц, перечисленных в предложении FROM.

§ Условие соединения, заданное в предложении WHERE, применяется ко всем строкам декартова произведения таблиц. Строки, для которых условие соединения не выполняется, отбрасываются.

§ Для каждой из оставшихся строк вычисляется каждое значение в списке SELECT.

§ Если задано ключевое слово DISTINCT, удаляются повторяющиеся строки.

2. Выполняется объединение, заданное оператором множеств.

3. Если задано предложение ORDER BY, результат запроса сортируется.

Многотабличные запросы и стандарт SQL2

Результатом соединения, реализованного в предложении FROM, является порожденная таблица, которая и должна обрабатываться остальными операторами… Для всех соединений, исключая перекрестные соединения и соединения… тип_соединения = INNER | {LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]}

Литература

1. Мартин Грабер. Справочное руководство по SQL: пер. с англ. – М.: Издательство «Лори», 1997.

2. Джеймс Р. Грофф, Пол Н. Вайнберг SQL: полное руководство: пер. с англ. - -К.: Издательская группа BHV, 1998.