Лекция 5: Выборка данных. Общее построение предложения SELECT и фразы FROM и WHERE

Пример

Пример использования для краткости формулировки:

SELECT ename, dname
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
;

Выше этот же запрос приводился без псевдонимов, с указанием фактических имен таблиц во фразе WHERE.

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

SELECT employee.ename, department.dname
FROM   emp employee, dept department
WHERE  employee.deptno = department.deptno
;

Когда псевдонимы обязательны

Есть случаи, когда использование синонимов представляет собой не вопрос удобства, а меру необходимости.

"Выдать список сотрудников, принятых на работу позже президента":

SELECT subordinate.ename
FROM   emp subordinate, emp topmanager
WHERE  topmanager.job = 'PRESIDENT'
AND    subordinate.hiredate > topmanager.hiredate
;

Использование во фразе FROM для ссылки на источник несколько раз одной и той же таблицы (выступающей содержательно в разных качествах; здесь — как перечень руководящих лиц и перечень сотрудников) требует употребления псевдонимов. Они-то и позволят сослаться в выражениях на поля строк таблицы в нужном смысле.

Подзапрос в качестве источника данных

Начиная с версии 8 во фразе FROM в качестве источника данных вместо таблицы может быть указан вложенный запрос:

SELECT ename, dname
FROM   emp e, ( SELECT * FROM dept WHERE loc <> 'NEW YORK' ) d
WHERE  e.deptno = d.deptno
;

Чтобы в выражениях сослаться на поля строк результата подзапроса (что на практике очень вероятно), подзапрос обязан наделяться псевдонимом.

Подзапрос в качестве источника данных разрешен не только в предложении SELECT, но и в INSERT, UPDATE и DELETE.

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

Обычно указанный во фразе FROM источник поставляет для последующей обработки полное множество своих строк. Версия 11 позволила указать для таблицы-источника неполную выборку строк. В этом случае в работу поступят не все строки, а случайная их выборка. В стандарте SQL:2003 подобная возможность указывается через уточнение TABLESAMPLE, и в Oracle она воспроизведена с некоторыми вольностями.

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

Отбор примерно 10% всех имеющихся сотрудников:

SELECT ename, sal FROM emp SAMPLE ( 10 );
SELECT COUNT ( * ) FROM emp SAMPLE ( 10 );

Упражнение. Проверьте, что оба запроса выше будут возвращать новые и новые результаты (на деле они будут заметно повторяться, но только в силу небольшого размера таблицы EMP).

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

Если по каким-то причинам желательно выборку зафиксировать и сделать одинаковой при повторениях запроса (допустим, неполную выборку строк желательно воспроизводить без изменений), в текст запроса добавляется конкретная "затравка" (seed) для вычисления внутренней псевдослучайной последовательности, например:

SELECT ename, sal FROM emp SAMPLE ( 10 ) SEED ( 3 );

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

SELECT COUNT ( * ) FROM emp SAMPLE BLOCK ( 85 );

В качестве источника может выступать не обязательно фактическая таблица, но и представление данных (view; см. ниже), однако когда его формулировка не содержит обобщений. Частичную выборку не запрещено указать и в многотабличных запросах:

SELECT ename, dname
FROM   emp SAMPLE ( 10 ), dept
WHERE  emp.deptno = dept.deptno
;

Указание частичной выборки похоже на выборку с отсевом строк WHERE ROWNUM <= n (см. ниже), притом что последняя менее трудоемка. Однако при употреблении условия с ROWNUM речь идет об отборе произвольных n строк ("квотированной выборке"), а в запросе с SAMPLE — о примерной доле общего количества. Вдобавок отбор с SAMPLE в состоянии дать более представительную выборку строк.

Специальный синтаксис для запроса-соединения

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

Физический адрес ROWID строки в таблице

Если во фразе FROM в качестве источника данных приведена хранимая таблица, то на этапе извлечения ее строк СУБД определяет для них физические адреса. Они становятся доступны в остальных частях запроса посредством "псевдостолбца" (на деле — функции без параметров) ROWID.

Физический адрес ROWID представлен типом ROWID, под которым может сохраняться в БД. Он содержит четыре компоненты: номер объекта (используется для таблицы из объектов), номер файла, номер блока и номер строки в блоке. Он расходует 10 байт, но SQL*Plus выдает его значение в формате BASE64 18-ю символами:

SQL> COLUMN dump(rowid) FORMAT A40
SQL> SELECT ROWID, DUMP ( ROWID ), loc FROM dept;
ROWID              DUMP(ROWID)                              LOC
------------------ ---------------------------------------- ---------
AAAR3qAAEAAAACHAAA Typ=69 Len=10: 0,1,29,234,1,0,0,135,0,0  NEW YORK
AAAR3qAAEAAAACHAAB Typ=69 Len=10: 0,1,29,234,1,0,0,135,0,1  DALLAS
AAAR3qAAEAAAACHAAC Typ=69 Len=10: 0,1,29,234,1,0,0,135,0,2  CHICAGO
AAAR3qAAEAAAACHAAD Typ=69 Len=10: 0,1,29,234,1,0,0,135,0,3  BOSTON

Хранение физического адреса в БД должно быть исключительной мерой, так как в общем он ненадежен. В результате изменений в БД одно и то же значение ROWID может со временем указывать на совсем иную строку (объект) даже в другой таблице, чем это было первоначально. При этом ROWID находит себе место в некоторых технических запросах, например, в одном из способов убрать из таблицы повторяющиеся строки.

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