Теория для лабораторной работы №2 - "Полное исследование команды SELECT"

В примерах используются таблицы EMP, DEPT пользователя SCOTT стандартно генерируемые Oracle при инсталляции. Их содержимое приведено в приложении.

 

	DISTINCT	Возвращает только одну пару дублирующихся строк из всего их множества.
	ALL	Возвращает все строки из всех таблиц, представлений, записанных в клаузе FROM.
	table.* view.*	показывает все колонки из указанной таблицы или представления.
	c_alias	обеспечивает другое имя (синоним) для названия колонки. Синоним может быть использован только в клаузе ORDER BY.
	schema	используется, если схема schema содержит выбранную таблицу или представление.
	table view	имя таблицы или представления из которых выбираются данные.
	dblink	полное или сокращённое имя связи с удалённой базой данных, в которой находится выбираемая таблица.
	subquery	подзапрос. Oracle выполняет подзапрос и использует строки, полученные в результате этого подзапроса как представление в клаузе FROM. Подзапрос не может запрашивать таблицу, которая используется в той же самой клаузе FROM, что и подзапрос.
	WITH READ ONLY	определяет, что подзапрос не может быть изменён.
	t_alias	обеспечивает синоним для таблицы, представления или подзапроса, который часто используется в коррелятивных запросах.
	WHERE	выбирает строки, для которых условие истинно. Если эта клауза опущена, то Oracle возвращает все строки из таблиц, представлений, указанных в клаузе FROM.
	START WITH CONNECT BY	возвращает строки в иерархическом порядке.
	GROUP BY	выполняет операцию expr для каждой строки и возвращает единственную строку с суммарной информацией для каждой группы.
HAVING		возвращает те группы строк, для которых заданное условие истинно.
UNION UNION ALL INTERSECT MINUS		комбинирует строки, возвращённые двумя операторами SELECT используя операции над множествами. Для ссылок на колонки должны использоваться синонимы этих колонок.
ORDER BY		сортировка строк.
		expr	сортирует строки по значениям тех столбцов, названия которых указаны в expr
		position	сортирует строки по значения тех столбцов, номера которых указаны в position
		ASC DESC	сортировка по возрастанию или убыванию. ASC – по умолчанию.
FOR UPDATE		запрещает другим пользователям изменять строки, возвращённые оператором SELECT.
		OF		замыкает только строки, указанной таблицы.

Агрегирующие функции

Агрегирующие функции предназначены для обработки групп строк в таблице.

Некоторые агрегирующие функции используют следующие опции:

DISTINCT – заставляет функцию рассматривать только уникальные значения аргумента.

ALL – заставляет функцию рассматривать все значения аргумента, включая дубликаты (задано по умолчанию).

Пример.

Вычислим среднее значение номеров отделов в таблице EMP, включая все повторяющиеся номера и только уникальные.

SELECT AVG(deptno) «Average» FROM EMP

Average

----------

22.142857

 

SELECT AVG(DISTINCT deptno) «Average» FROM EMP

Average

----------

20

 

Функция	Синтаксис	Пояснение	Пример
AVG	AVG([DISTINCT|ALL] n)	возвращает среднее арифметическое столбца n	SELECT AVG(sal) "Average" FROM emp      Average ----------------- 2077.21429
MAX	MAX([DISTINCT|ALL] expr)	возвращает максимальное значение выражения expr	SELECT MAX(sal) "Maximum" FROM emp      Maximum -----------------    5004
MIN	MIN([DISTINCT|ALL] expr)	возвращает минимальное значение выражения expr	SELECT MIN(hiredate) "Minimum Date"            FROM emp   Minimum Date ---------------------- 17-DEC-80
COUNT	COUNT({* |                     [DISTINCT|ALL] expr})	возвращает количество строк	1) SELECT COUNT(*) "Total" FROM emp           Total ---------------           18 2) SELECT COUNT(job) "Count" FROM emp   Count ---------- 14 3) SELECT COUNT(DISTINCT job) "Jobs" FROM emp   Jobs ---------- 5
STDDEV	STDDEV([DISTINCT| ALL] x)	возвращает стандартное отклонение x	SELECT STDDEV(sal) "Deviation" FROM emp   Deviation ----------------- 1182.50322
SUM	SUM([DISTINCT|ALL] n)	возвращает сумму значений столбца n	SELECT SUM(sal) "Total" FROM emp        Total -------------      29081
VARIANCE	VARIANCE(              [DISTINCT|ALL]x)	возвращает рассогласование x, используя следующую формулу:	SELECT VARIANCE(sal) "Variance" FROM emp     Variance ----------------- 1389313.87

 

Символьные операторы

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

Оператор	Пояснение	Пример
||	конкатенация строк	SELECT 'Name is ' || ename         FROM emp

Результатом конкатенации двух символьных строк является также символьная строка. Если конкатенируемые строки имеют тип CHAR, то результирующая строка также будет иметь тип CHAR и соответственно её длина лимитируется 255 символами. Если хотя бы одна из конкатенируемых строк имеет тип VARCHAR2, то результирующая строка будет иметь тип VARCHAR2 и её длина лимитируется 2000 символами.

Операторы сравнения

Операторы сравнения используются в условиях, которые сравнивают одно выражение с другим. Результатом сравнения является одно из значений: TRUE, FALSE, или UNKNOWN.

Оператор	Пояснение	Пример
=	проверка на равенство	SELECT  * FROM emp   WHERE sal = 1500
!=	проверка на неравенство	SELECT  * FROM emp   WHERE sal != 1500
>  <	проверка на «больше чем» и «меньше чем»	SELECT * FROM emp WHERE sal > 1500   SELECT * FROM emp WHERE sal < 1500
>= <=	проверка на «больше или равно» и «меньше или равно»	SELECT * FROM emp WHERE sal >= 1500   SELECT * FROM emp WHERE sal =< 1500
IN	проверка на «равенство любому значению из». Эквивалент «=ANY»	SELECT * FROM emp WHERE job IN ('CLERK','ANALYST')   SELECT * FROM emp WHERE sal IN               (SELECT sal        FROM emp WHERE deptno = 30)
NOT IN	Эквивалент «!=ANY». Вычисляет значение FALSE, если сравниваемый операнд не входит в заданное множество.	SELECT * FROM emp WHERE job NOT IN ('CLERK','ANALYST')   SELECT * FROM emp WHERE sal NOT IN               (SELECT sal     FROM emp         WHERE deptno = 30)
ANY	Сравнивает значение с каждым значением в заданном списке, если хотя бы одно из значений из списка удовлетворяет заданному отношению, то возвращает эту строку. Используется вместе с операторами !=, >, <, <=, >=	SELECT *          FROM emp WHERE sal = ANY (SELECT sal FROM emp WHERE deptno = 30)
ALL	Сравнивает значение с каждым значением в заданном списке, если все значения из списка удовлетворяют заданному отношению, то возвращает эту строку. Используется вместе с операторами !=, >, <, <=, >=	SELECT *          FROM emp WHERE sal> = ANY (SELECT sal FROM emp WHERE deptno = 30)
[NOT] BETWEEN x AND y	проверка [не] принадлежности заданному интервалу	SELECT *          FROM emp WHERE sal          BETWEEN 2000               AND 3000
EXISTS	TRUE, если подзапрос вернул хотя бы одну строку	SELECT dname, deptno FROM dept WHERE EXISTS (SELECT * FROM emp WHERE dept.deptno = emp.deptno)
IS [NOT] NULL	проверка на [не] NULL	SELECT dname, deptno FROM emp WHERE comm IS NULL

 

Оператор LIKE

Оператор LIKE используется в операторах сравнения символьных строк для создания шаблонов.

Синтаксис:

где

char1 –сравниваемая строка. Имеет тип CHAR или VARCHAR2;

NOT – логический оператор отрицания;

char2 – шаблон. Имеет тип CHAR или VARCHAR2 и может содержать специальные символы % и _;

ESCAPE – определяет единственный символ как ESC-символ.

Пример.

Найти зарплату служащих, имена которых начинаются с букв ‘SM’:

SELECT sal

      FROM emp

      WHERE ename LIKE 'SM%'

Специальные символы, используемые Oracle в шаблонах:

·  _  - знак подчёркивания. Используется для определения любого одного символа.

·  % - знак процента. Определяет множество любых символов либо не одного.

Опция ESCAPE определяет ESC-символ. Если ESC-символ появляется в шаблоне перед специальными символами ‘%’ или ‘_’, то Oracle интерпретирует специальный символ как простой символ.

Пример.

Найти служащих, используя шаблон ‘A_B’:

SELECT ename

      FROM emp

      WHERE ename LIKE '%A\_B%' ESCAPE '\'

Логические операторы

 

Оператор	Пояснение	Пример
NOT	логическое отрицание	SELECT * FROM emp WHERE NOT (job IS NULL)   SELECT * FROM emp WHERE NOT (sal BETWEEN 1000 AND 2000)
AND	логическое умножение	SELECT * FROM emp  WHERE job = 'CLERK'     AND deptno = 10
OR	логическое сложение	SELECT *  FROM emp WHERE job = 'CLERK'     OR deptno = 10

 

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

 

Оператор	Результат
UNION	все строки, возвращаемые каждым из запросов
UNION ALL	все строки, возвращаемые каждым из запросов, включая все повторения
INTERSECT	все строки, которые встречаются как в первом запросе, так и во втором
MINUS	все строки, которые встречаются только в  первом запросе и не встречаются во втором

 

Примеры.

В примерах будут использованы следующие таблицы:

Таблица ORDERS_LIST1:   PART                             ----------                         SPARKPLUG                    FUEL PUMP                     FUEL PUMP                     TAILPIPE

Таблица ORDERS_LIST2:   PART ---------- CRANKSHAFT TAILPIPE TAILPIPE

 

Пример. UNION

Следующий пример комбинирует результаты с помощью оператора UNION, который не дублирует повторяющиеся строки:

SELECT part

      FROM orders_list1

UNION

SELECT part

      FROM orders_list2

 

PART

----------

SPARKPLUG

FUEL PUMP

TAILPIPE

CRANKSHAFT

 

Необходимым условием использования операторов со множествами является равенство схем операндов. Покажем как можно обойти это ограничение на примере команды UNION.

Рассмотрим таблицы LIST1 и LIST2:

Таблица LIST1:   PART                           PARTNUM              ----------                       ---------------  SPARKPLUG                    123 FUEL PUMP                       34 TAILPIPE                          129

Таблица LIST2:   PART                              DATE_IN ----------                          -------------    CRANKSHAFT                10/24/98 TAILPIPE                             9/9/99

Как видно из определения этих таблиц, они имеют разные столбцы. Для того, чтобы полностью объединить эти две таблицы, необходимо в команде объединения:

1)  для таблицы LIST1 создать фиктивный столбец DATE_IN типа DATE. Это достигается командой TO_DATE(NULL);

2)  для таблицы LIST2 создать фиктивный столбец PART_NUM типа NUMBER. Это достигается командой TO_NUMBER(NULL).

Следующий пример показывает как можно обойти ограничение на равенство схем операндов объединения:

SELECT part, partnum, to_date(null) «date_in»

      FROM list1

UNION

SELECT part, to_null(null)»partnum», date_in

      FROM list2

 

PART	PARTNUM	DATE_IN
SPARKPLUG	123
FUEL PUMP	34
TAILPIPE	129
TAILPIPE		09/09/99
CRANKSHAFT		10/24/98

 

Пример. UNION ALL

Следующий пример комбинирует результаты с помощью оператора UNION ALL, который дублирует повторяющиеся строки:

SELECT part

      FROM orders_list1

UNION ALL SELECT part

      FROM orders_list2

 

PART

----------

SPARKPLUG

FUEL PUMP

FUEL PUMP

TAILPIPE

CRANKSHAFT

TAILPIPE

TAILPIPE

 

Пример. INTERSECT

Следующий пример комбинирует результаты с помощью оператора INTERSECT, который возвращает только те строки, которые встречаются в обоих запросах:

SELECT part

      FROM orders_list1

INTERSECT

SELECT part

      FROM orders_list2

 

PART

----------

TAILPIPE

 

Пример. MINUS

Следующий пример комбинирует результаты с помощью оператора MINUS, который возвращает только те строки, которые возвращаются только первым запросом и не возвращаются вторым.

SELECT part

      FROM orders_list1

 

MINUS SELECT part

      FROM orders_list2

 

PART

----------

SPARKPLUG

FUEL PUMP

Иерархические запросы

Если таблица содержит иерархические данные, можно просматривать строки в иерархическом порядке, используя следующие клаузы:

 

START WITH	указывает корневую строку(строки) иерархии.
CONNECT BY	специфицирует отношение между родительскими строками и дочерними.
WHERE	ограничивает строки, возвращаемые запросом без воздействия  на другие строки иерархии.

Oracle для построения иерархии использует следующие шаги:

1.  Oracle выбирает корневую строку(строки) иерархии. Эти строки указываются в клаузе START WITH.

2.  Oracle выбирает дочерние строки для каждой корневой строки. Каждая дочерняя строка должна удовлетворять условию в клаузе CONNECT BY и может иметь не более одной корневой строки.

3.  Oracle последовательно генерирует дочерние строки. Oracle сначала выбирает потомков строк, возвращённых во 2^м шаге, затем потомков этих потомков и т.д. Oracle всегда выбирает потомков, анализируя условие в клаузе CONNECT BY.

4.  Если запрос содержит клаузу WHERE, Oracle удаляет все строки из иерархии, которые не удовлетворяют условию в этой клаузе.

5.  Oracle возвращает строки в порядке, показанном на рисунке:

ROOT

 

 

 

Ограничения на построения иерархий:

·  оператор SELECT, выполняющий иерархический запрос не может также выполнять соединение.

·  если в иерархическом запросе используется клауза ORDER BY, Oracle сортирует строки в порядке, отличном показанном на рисунке.

Клауза START WITH

Идентифицирует строку(ки), используемую в качестве корня(ней) иерархического запроса. Эта клауза определяет условие, которому должны удовлетворять корни. Если опустить эту клаузу, то Oracle использует все строки в таблице как корневые. Условие в этой клаузе может содержать подзапрос.

Клауза CONNECT BY

Определяет отношение между родительскими и дочерними строками. Эта клауза содержит условие, определяющее это отношение. Это условие должно использовать оператор PRIOR для ссылки на родительскую строку.

Две формы использования оператора PRIOR:

      PRIOR expr comparison_operaror expr

      expr comparison_operaror PRIOR expr

Для нахождения потомка родительской строки, Oracle вычисляет expr, стоящее перед PRIOR, для родительской строки и другой expr для каждой строки в таблице. Строки, для которых условие истинно – потомки родительской строки. Клауза CONNECT BY может содержать другие условия для дальнейшей фильтрации строк, возвращённых запросом. Клауза CONNECT BY не может содержать подзапрос.

Oracle выдаёт ошибку, если одна и та же строка является одновременно как родителем, так и потомком другой строки.

Пример.

Следующая клауза CONNECT BY определяет иерархическое отношение, в котором значение номера служащего (EMPNO) родительской строки равно значению номера руководителя (MGR) дочерней строки:

CONNECT BY PRIOR empno=mgr

Пример.

В следующей клаузе CONNECT BY, оператор PRIOR применяется только к значению  EMPNO. Для вычисления этого условия, Oracle вычисляет  значения EMPNO для родительской строки и MGR,SAL, и COMM – значения для дочерней строки:

CONNECT BY PRIOR empno=mgr AND sal>comm

В этом примере потомок должен иметь номер руководителя (MGR), равным номеру служащего (EMPNO) родительской строки и должен иметь оклад (SAL) выше, чем его комиссионные (COMM).

Псевдостолбец LEVEL

Оператор SELECT, выполняющий иерархический запрос может использовать псевдостолбец LEVEL, возвращающий значение 1 для корня дерева, 2 для потомка корня дерева и т.д.

Число уровней, возвращаемых иерархическим запросом  ограничивается размером доступной памяти.

Пример.

Следующий оператор SELECT показывает всех служащих в иерархическом порядке. Корневая строка определена для служащих, работающих на должности ‘PRESIDENT’. Дочерние строки определены для тех служащих, которые имеют номер служащего родительской строки такой же как и номер их руководителя.

SELECT LPAD(‘’,2*(LEVEL-1)) || ename org_chart,empno,mgr,job

                         FROM emp

                     START WITH job=’PRESIDENT’

                     CONNECT BY PRIOR empno=mgr

Оператор LPAD применяется для сдвига дочерней записи относительно её родительской.

 

ORG_CHART	EMPNO	MGR	JOB
KING	7839		PRESIDENT
JONES	7566	7839	MANAGER
SCOTT	7788	7566	ANALYST
ADAMS	7876	7788	CLERK
FORD	7902	7566	ANALYST
SMITH	7369	7902	CLERK
BLAKE	7698	7839	MANAGER
ALLEN	7499	7698	SALESMAN
WARD	7521	7698	SALESMAN
MARTIN	7654	7698	SALESMAN
TURNER	7844	7698	SALESMAN
JAMES	7900	7698	CLERK
CLARK	7782	7839	MANAGER
MILLER	7934	7782	CLERK

 

Следующий оператор аналогичный предыдущему за исключением того, что он не выводит служащих с должностью ‘ANALYST’.

SELECT LPAD(‘’,2*(LEVEL-1)) || ename org_chart,empno,mgr,job

                         FROM emp

                    WHERE  job!=’ANALYST’

                     START WITH job=’PRESIDENT’

                     CONNECT BY PRIOR empno=mgr

 

ORG_CHART	EMPNO	MGR	JOB
KING	7839		PRESIDENT
JONES	7566	7839	MANAGER
ADAMS	7876	7788	CLERK
SMITH	7369	7902	CLERK
BLAKE	7698	7839	MANAGER
ALLEN	7499	7698	SALESMAN
WARD	7521	7698	SALESMAN
MARTIN	7654	7698	SALESMAN
TURNER	7844	7698	SALESMAN
JAMES	7900	7698	CLERK
CLARK	7782	7839	MANAGER
MILLER	7934	7782	CLERK

 

Oracle не выводит аналитиков SCOTT и FORD, однако он выводит служащих, подчиняющихся им.

 

Следующий оператор подобен первому, за исключением того, что он использует псевдостолбец для выбора первых двух уровней иерархии:

SELECT LPAD(‘’,2*(LEVEL-1)) || ename org_chart,empno,mgr,job

                         FROM emp

                     START WITH job=’PRESIDENT’

                     CONNECT BY PRIOR empno=mgr AND LEVEL<=2

 

ORG_CHART	EMPNO	MGR	JOB
KING	7839		PRESIDENT
JONES	7566	7839	MANAGER
BLAKE	7698	7839	MANAGER
CLARK	7782	7839	MANAGER

 

Клауза GROUP BY

Используется для группировки выбранных строк и возврата единственной строки с результирующей информацией. Oracle группирует строки в соответствии со значением выражения, указанного в этой клаузе.

Если оператор SELECT использует клаузу GROUP BY, то список вывода может только содержать следующие типы выражений:

·  константы

·  аггрегирующие функции

·  функции USER, UID и SYSDATE

·  выражения, идентичные указанным в клаузе GROUP BY

Выражения в этой клаузе могут содержать любые столбцы в таблицах и представлениях, указанных в клаузе FROM не считая тех столбцов, которые указаны в списке вывода.

Пример.

Вывести минимальный и максимальный размер жалования для каждого департамента.

SELECT deptno,MIN(sal),MAX(sal)

                FROM emp

                 GROUP BY deptno

 

DEPTNO	MIN(SAL)	MAX(SAL)
10	1300	5000
20	800	3000
30	950	2850

 

Пример.

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

SELECT deptno,MIN(sal),MAX(sal)

                FROM emp

                 WHERE job=’CLERK’

                 GROUP BY deptno

 

DEPTNO	MIN(SAL)	MAX(SAL)
10	1300	1300
20	800	1100
30	950	950

 

Клауза HAVING

Используется для ограничения вывода групп строк, определённых в клаузе GROUP BY. Oracle обрабатывает клаузы WHERE, GROUP BY и HAVING следующим образом:

1.  Если оператор содержит клаузу WHERE, Oracle удаляет все строки, не удовлетворяющих условию в этой клаузе.

2.  Oracle вычисляет и формирует группы в соответствии с клаузой GROUP BY.

3.  Oracle удаляет все группы, не удовлетворяющих условию в клаузе HAVING.

Пример.

Показать минимальный и максимальный размер жалования для клерков в каждом департаменте, в котором минимальное жалование меньше $1000:

SELECT deptno,MIN(sal),MAX(sal)

                FROM emp

                WHERE job=’CLERK’   

                GROUP BY deptno

                 HAVING MIN(sal)<1000   

 

DEPTNO	MIN(SAL)	MAX(SAL)
20	800	1100
30	950	950

 

Клауза ORDER BY

Без клаузы ORDER BY нельзя гарантировать, что один и тот же запрос, выполненный несколько раз возвратит строки в одном и том же порядке. Эта клауза используется для упорядочивания выбранных в запросе строк. Клауза специфицирует выражения, позиции и синонимы выражений в списке вывода оператора. Oracle возвращает строки в соответствии с их значениями для этих выражений.

Можно указывать несколько выражений в клаузе ORDER BY. Oracle сначала сортирует строки в соответствии с их значениями для первого выражения. Строки с одинаковыми значениями для первого выражения затем сортируются в соответствии с их значениями для второго выражения и т.д.

Пример.

Выбрать всю информацию о продавцах (SALESMAN)  в порядке убывания их комиссионных:

SELECT *

           FROM emp

           WHERE job=’SALESMAN’

           ORDER BY comm DESC

Пример.

Выбрать служащих из таблицы EMP в порядке возрастания номера департамента и убывания их жалования.

SELECT ename,deptno,sal

FROM emp

           ORDER BY 2 ASC, 3 DESC

ENAME	DEPTNO	SAL
MILLER	10	13000
KING	10	5000
CLARK	10	2450
SCOTT	20	3000
FORD	20	3000
JONES	20	2975
ADAMS	20	1100
SMITH	20	800
BLAKE	30	2850
ALLEN	30	1600
TURNER	30	1500
WARD	30	1250
MARTIN	30	1250
JAMES	30	950

 

Клауза FOR UPDATE

Клауза FOR UPDATE «замыкает» выбранные запросом строки. Если пользователь выбрал строку для изменения, то другие пользователи не смогут замкнуть или изменить её в текущей транзакции этого пользователя. Клауза FOR UPDATE сигнализирует, что пользователь намерен вставить, изменить или удалить строки, возвращённые запросом.

Клауза FOR UPDATE не может использоваться вместе с конструкциями:

·  DISTINCT

·  GROUP BY

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

·  агрегирующими функциями

Таблицы, замкнутые в клаузе FOR UPDATE должны находиться в этой же базе данных.

Если строка, выбранная для изменения в данный момент замкнута другим пользователем, Oracle ожидает её освобождения, затем замыкает её и передаёт управление текущему пользователю. Можно использовать опцию NOWAIT, для того чтобы сообщить Oracle, что необходимо прервать оператор без ожидания если строка всегда замкнута.

FOR UPDATE OF

Выражение в клаузе OF только определяет какие строки таблиц замыкаются. Если опустить эту клаузу, то Oracle замкнёт строки из всех таблиц в запросе.

Пример.

Следующий оператор замыкает строки в таблице EMP с клерками, работающими в Нью-Йорке и замыкает строки в таблице DEPT с отделами в Нью-Йорке,  в которых работают клерки:

SELECT empno,sal,comm

                FROM emp,dept

                WHERE job=’CLERK’  

                AND loc=’NEW YORK’

                FOR UPDATE

Пример.

Следующий оператор замыкает только строки в таблице EMP с клерками, работающими в Нью-Йорке и не замыкает строки в таблице DEPT:

SELECT empno,sal,comm

                FROM emp,dept

                WHERE job=’CLERK’ AND loc=’NEW YORK’

                FOR UPDATE OF emp

Соединения

Соединение – это запрос, который комбинирует строки из двух или более таблиц или представлений. Oracle выполняет соединение всякий раз, когда много таблиц появляется в клаузе FROM. Список вывода может содержать любые столбцы из любых этих таблиц. Если любые две из этих таблиц имеют общий столбец, необходимо определить все ссылки на этот столбец через запрос с синонимами имени таблицы для избежания двусмысленности.

Условие соединения

Большинство запросов соединения содержат  условие в клаузе WHERE, которое сравнивает два столбца, каждый из которых находится в разных таблицах. Для выполнения соединения, Oracle комбинирует пары строк,  для которых условие соединения истинно.

Для соединения трёх и более таблиц, Oracle сначала соединяет две таблицы, которые удовлетворяют условию соединения, затем соединяет результирующую таблицу с другой таблицей в соответствии с условием соединения, содержащим столбцы присоединяемой таблицы и новой. Oracle продолжает этот процесс до тех пор пока все таблицы не войдут в результирующую.

Екви-соединения

Екви-соединения – соединения с условием, содержащим оператор равенства. Екви-соединения комбинируют строки, которые имеют равные значения для определённого столбца.

Пример.

Это соединение возвращает имя и занимаемую должность каждого служащего и номер и название отдела в котором он работает:

SELECT ename,job,dept.deptno,dname

               FROM emp,dept 

               WHERE  emp.deptno=dept.deptno

ENAME	JOB	DEPTNO	DNAME
SMITH	CLERK	20	RESEARCH
ALLEN	SALESMAN	30	SALES
WARD	SALESMAN	30	SALES
JONES	MANAGER	20	RESEARCH
MARTIN	SALESMAN	30	SALES
BLAKE	MANAGER	30	SALES
CLARK	MANAGER	10	ACCOUNTING
SCOTT	ANALYST	20	RESEARCH
KING	PRESIDENT	10	ACCOUNTING
TURNER	SALESMAN	30	SALES
ADAMS	CLERK	20	RESEARCH
JAMES	CLERK	30	SALES
FORD	ANALYST	20	RESEARCH
MILLER	CLERK	10	ACCOUNTING

 

Пример.

Следующее экви-соединение возвращает имя, должность, номер отдела и имя отдела для всех клерков:               

SELECT ename,job,dept.deptno,dname

               FROM emp,dept

               WHERE  emp.deptno=dept.deptno

                     AND job=’CLERK’

 

ENAME	JOB	DEPTNO	DNAME
SMITH	CLERK	20	RESEARCH
ADAMS	CLERK	20	RESEARCH
JAMES	CLERK	30	SALES
MILLER	CLERK	10	ACCOUNTING

 

Авто соединения

Авто соединение – соединение таблицы само в себя. Эта таблица дважды появляется в клаузе FROM и определяется синонимами, которые используются для определения столбцов в условии соединения. Для выполнения этого соединения Oracle комбинирует и возвращает строки таблицы, которые удовлетворяют условию соединения.

Пример.

Показать имена каждого служащего вместе с именем его руководителя:

SELECT e1.ename||' работает на '||e2.ename"Служащие и их Менеджеры"

       FROM emp e1,emp e2

       WHERE e1.mgr=e2.empno

Служащие и их Менеджеры

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

SMITH работает на FORD

ALLEN работает на ВLAКЕ

WARD работает на BLAKE

JONES работает на KING

MARTIN работает на BLAKE

BLAKE работает на KING

CLARK работает на KING

SCOTT работает на JONES

TURNER работает на BLAKE

ADAMS работает на SCOTT

JAMES работает на BLAKE

FORD работает на JONES

MILLER работает на CLARK

 

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

Если две таблицы в запросе соединения не имеют условия соединения, Oracle возвращает декартово произведение их строк: комбинация каждой строки из одной таблицы с каждой строкой другой.

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

Внешние соединения расширяют результат простого соединения. Внешнее соединение возвращает все строки, которые удовлетворяют условию соединения и те строки из одной таблицы, для которых нет строк из другой, удовлетворяющие условию соединения. Для написания запроса, который выполняет внешнее соединение таблиц A и B и возвращает все строки из А применяют оператор внешнего соединения (+) ко всем столбцам таблицы B в условии соединения. Для всех строк в А, не имеющих пары в B, Oracle возвращает NULL.

Пример.

Показать имя, должность, номер отдела и имя отдела для всех клерков, используя внешнее соединение.

SELECT ename,job,dept.deptno,dname

             FROM emp,dept

             WHERE emp.deptno(+)=dept.deptno

ENAME	JOB	DEPTNO	DNAME
CLARK	MANAGER	10	ACCOUNTING
KING	PRESIDENT	10	ACCOUNTING
MILLER	CLERK	10	ACCOUNTING
SMITH	CLERK	20	RESEARCH
ADAMS	CLERK	20	RESEARCH
FORD	ANALYST	20	RESEARCH
SCOTT	ANALYST	20	RESEARCH
JONES	MANAGER	20	RESEARCH
ALLEN	SALESMAN	30	SALES
BLAKE	MANAGER	30	SALES
MARTIN	SALESMAN	30	SALES
JAMES	CLERK	30	SALES
TURNER	SALESMAN	30	SALES
WARD	SALESMAN	30	SALES
		40	OPERATIONS

В этом внешнем соединении Oracle возвращает строку, содержащую отдел OPERATIONS даже, если в нём никто не работает. Oracle возвращает NULL для остальных столбцов.

 

Подзапросы

Подзапрос – это форма команды SELECT, которая появляется внутри другого SQL-оператора.Оператор, содержащий подзапрос называется родительским. Строки, возвращённые подзапросом используются родительским оператором.

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

Подзапрос также может содержать в себе подзапрос. Oracle не ограничивает уровень вложенности подзапросов.

Пример.

Определить служащих, работающих в одном отделе вместе с Тейлором:

SELECT ename,deptno

             FROM ename

             WHERE   deptno=(SELECT deptno

                                                       FROM emp

                                                        WHERE ename=’TAYLOR’ )

 

Коррелированные подзапросы

Коррелированный подзапрос – подзапрос, который вычисляется для каждой строки, сгенерированной родительским оператором.

Используется для ответа на запросы, содержащие несколько частей, ответы на которые зависят от значения в каждой строке, обрабатываемой родительским оператором. Например, коррелированный подзапрос можно использовать для определения какие служащие зарабатывают больше, чем  средняя зарплата для его отдела. В этом случае, коррелированный подзапрос вычисляет среднюю зарплату для каждого отдела:

SELECT deptno,ename,sal

                FROM emp x

                WHERE sal>(SELECT AVG(sal)

                                                FROM emp

                                                 WHERE x.deptno=deptno)

                ORDER BY deptno    

 

Для каждой строки таблицы EMP, родительский оператор использует коррелированный подзапрос для вычисления средней зарплаты для каждого департамента.

Oracle выполняет коррелированный подзапрос, если имеется ссылка в подзапросе на столбец таблицы, содержащейся в родительском операторе.