Intersect corresponding BY (worker_id)

(SELECT * FROM ASSIGNMENT

WHERE START_DATE = '09.10')

Результат:

WORKER ID

Мы выделили часть SQL-выражения, важную для нашего обсуждения. Две фразы SELECT определяют две таблицы, которые явно не являются объ­единительно-совместимыми. Однако мы можем взять их пересечение, огово­рив, что рассматриваются только те столбцы, которые есть в обеих таблицах. Мы указываем, какие столбцы рассматривать, в команде CORRESPONDING BY. В нашем случае рассматривается только столбец WORKER_ID. Система оставит из результатов выполнения команд SELECT только значения в столбце WORKER_ID, Затем она возьмет пересечение этих двух множеств, и это будет результат запроса. Таким образом, в результате получится список ИД тех работников, которые имеют специальность «штукатур» и получили работу, которая должна начинаться 9 октября - в точности это и требовалось.

Такой же подход используется для операторов UNION и EXCEPT. Фраза CORRESPONDING BY следует за оператором и перечисляет столбцы, общие для обеих рассматриваемых таблиц. Рассмотрим еще несколько примеров:

Запрос: Какие здания являются зданиями офисов или же на них назна­чен рабочий 14127

(SELECT * FROM BUILDING

WHERE TYPE = 'Офис')

UNION CORRESPONDING BY (BLDG_ID)

(SELECT * FROM ASSIGNMENT

WHERE WORKER_ID = 1412)

Результат:

BLDG_ID

Запрос: На какие здания офисов не назначен рабочий 1235?

(SELECT * FROM BUILDING

WHERE TYPE = 'Офис')

EXCEPT CORRESPONDING BY (BLDG_ID)

(SELECT * FROM ASSIGNMENT

WHERE WORKER_ID = 1235)

Результат:

BLDG ID

Вы, вероятно, заметили, что эти запросы относительно легко сформули­ровать. Мы просто определяем подходящие подмножества строк из двух таб­лиц, а затем применяем соответствующий оператор UNION, INTERSECT или EXCEPT. Это существенно расширяет практические возможности языка, поскольку многие люди считают, что подобные запросы проще формулировать, пользуясь именно таким подходом. Например, применим к последнему за­просу «старый» подход SQL:

 

SELECT BLDG_ID

FROM BUILDING

WHERE TYPE = 'Офис' AND

NOT EXISTS (SELECT *

FROM ASSIGNMENT

WHERE BUILDING.BLDG_ID = ASSIGNMENT.BLDG_ID

AND WORKER_ID = 1235)

В этом решении использован коррелированный подзапрос и оператор NOT EXISTS. Естественно предположить, что большинству людей значи­тельно сложнее сформулировать такое решение, чем решение с оператором EXCEPT. Тем не менее, обратите внимание, что запрос можно было бы реа­лизовать с помощью старого синтаксиса SQL. Это не так просто.

Оператор JOIN (соединение). SQL2 содержит несколько операторов со­единения в явной форме: естественное соединение, внутреннее соединение, несколько внешних соединений, объединительное соединение и кросс-соеди­нение. Мы рассмотрим только естественное и внутреннее соединения.

Естественное соединение. Соответственно названию, естественное соеди­нение имеет то же значение в SQL, что и в реляционной алгебре. Предполо­жим, что мы хотим соединить таблицы WORKER и ASSIGNMENT. Мы мо­жем использовать такой синтаксис:

 

WORKER NATURAL JOIN ASSIGNMENT

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

 

SELECT P.WORKER_ID, WORKER_NAME, HRLY_RATE, SKILL_TYPE,

SUPV_ID, WORKER_ID, BLDG_ID, START_DATE, NUM_DAYS

FROM WORKER, ASSIGNMENT

WHERE P.WORKER_ID = ASSIGNMENT.WORKER_ID,

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

NATURAL JOIN. Оператор, соединяющий таблицы в том случае, если общие столбцы имеют равные значения.

 

JOIN USING. Предположим, у нас есть две таблицы А и В, и у них есть общие столбцы К, L, М и N. Предположим, что мы хотим соединить таб­лицы не по всем общим столбцам, а только по столбцам L и N. Тогда мы можем воспользоваться следующим выражением:

A JOIN В USING (L, N)

Это выражение даст тот же результат, что и SELECT-выражение, в кото­ром в команде SELECT будут сначала перечислены столбцы L и N, за кото­рыми следуют оставшиеся столбцы А и оставшиеся столбцы В. Фраза WHERE в таком выражении должна иметь вид

WHERE A.L = B.L AND A.N = B.N

 

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

JOIN ON. Если мы хотим задать более общее условие соединения двух таблиц, то можем пользоваться этой формой. Например, предположим, что мы хотим присоединить таблицу WORKER к ней самой, связав WORKER ID с SUPV_ID, чтобы получить информацию о менеджере каждого работника. Воспользуемся следующим выражением:

 

WORKER W JOIN WORKER SUPV

ON W.SUPV_ID = SUPV.WORKER_ID

В этом примере мы создали две копии таблицы WORKER и дали им псевдонимы W и SUPV, соответственно. Фраза ON содержит условие равен­ства SUPV_ID копии W и WORKER_ID копии SUPV таблицы WORKER.

 

JOIN ON. Операция, связывающая таблицы при выполнении условия.

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

 

Запрос: Выдать данные о назначении и работнике для каждого штука­тура, начинающего работу 9 октября.

(SELECT * FROM WORKER WHERE SKILL_TYPE = 'Штукатур')

NATURAL JOIN

(SELECT * FROM ASSIGNMENT WHERE START_DATE = '09.10')

Возможно, вы заметили, что этот запрос почти идентичен тому, который использовался ранее для иллюстрации работы оператора INTERSECT. Раз­ница состоит в том, что вместо того, чтобы просто получить список WORKER_ID, мы получим всю связанную с работником информацию, по­скольку кортежи таблицы WORKER будут связаны с кортежами таблицы ASSIGNMENT.

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

 

(SELECT * FROM WORKER WHERE SKILL_TYPE = 'Штукатур')

NATURAL JOIN

(SELECT * FROM ASSIGNMENT WHERE START_DATE = '09.10')

NATURAL JOIN

(SELECT * FROM BUILDING WHERE TYPE - 'Жилой дом')

В этом случае WORKER_ID и BLDG_ID - столбцы, по которым выпол­няется соединение - будут стоять в таблице результатов первыми, за ними будут следовать оставшиеся столбцы таблиц WORKER, ASSIGNMENT, BUILDING (именно в таком порядке). Если в результате мы получаем избы­точную информацию, то можем поместить все это выражение внутрь выра­жения SELECT FROM, указывающего нужные столбцы:

SELECT WORKER_ID, START_DATE, BLDG_ID, ADDRESS

FROM (SELECT * FROM WORKER WHERE SKILL_TYPE = 'Штукатур')

NATURAL JOIN

(SELECT * FROM ASSIGNMENT WHERE START_DATE = '09.10')

NATURAL JOIN

(SELECT * FROM BUILDING WHERE TYPE = 'Жилой дом')

 

9. Операции изменения данных

 

В SQL есть операции изменения данных INSERT, UPDATE и DELETE, позволяющие, соответственно, добавлять строки, изменять значения в стро­ках и удалять строки определенной таблицы в базе данных. Каждую из опе­раций мы обсудим отдельно.

INSERT. Операция INSERT (вставить) позволяет вводить в таблицу как отдельные строки путем указания значений каждого столбца, так и множе­ства строк путем формулировки запроса, определяющего вводимые строки.

INSERT INTO ASSIGNMENT (WORKER_ID, BLDG_ID, START DATE)

VALUES (1284, 485, 13.05)

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

Предположим, что мы создали новую таблицу с именем BUILDING_2, состоящую из столбцов BLDG_ID, TYPE и QLTY_LEVEL (УРОВ_КАЧЕСТВА), и хотим заполнить эту таблицу строками из таблицы BUILDING, имеющими STATUS 2. Тогда мы воспользуемся второй формой команды INSERT:

INSERT INTO BUILDING_2

SELECT BLDG_ID, TYPE, QLTY_LEVEL

FROM BUILDING

WHERE STATUS = 2

INSERT. Операция, добавляющая строки к таблице.

UPDATE. Операция UPDATE (изменить) всегда применяется ко всем строкам, удовлетворяющим условию выражения WHERE. Если мы хотим повысить на 5 процентов ставку каждого подчиненного начальника 1520, то нам потребуется следующее выражение:

UPDATE WORKER

SET HRLY_RATE = 1.05 * HRLY_RATE

WHERE SUPV_ID = 1520 -

Если фраза WHERE отсутствует, то операция применяется к каждой строке таблицы. Например, если бы мы хотели повысить на 5 процентов ставку каждого работника, то мы должны были бы просто опустить команду WHERE в выражении UPDATE.

UPDATE. Операция, изменяющая значения столбцов в строке.

DELETE. Операция DELETE (удалить) также применяется ко всем стро­кам, удовлетворяющим условию WHERE. Если фраза WHERE отсутствует. то удаляются все строки таблицы. Допустим, что все рабочие, чей начальник имеет индекс 1520, были уволены, и мы хотим удалить соответствующие строки из базы данных. Это сделает следующее выражение:

DELETE FROM WORKER

WHERE SUPV ID = 1520

DELETE. Операция, удаляющая строки из таблицы.