SQL за 24 часа

Рекурсивное связывание (SELF JOIN) предполагает связывание таблицы с ней же самой, как будто бы это были две таблицы, применяя временное переименование таблицы в операторе SQL. Синтаксис такого оператора следующий.

SELECT А.имя_столбца, В.имя_столбца [, С.имя_столбца ]

FROM таблица1 А, таблица2 В [, таблицаЗ С ]

WHERE А.имя_столбца = В.имя_столбца

[ AND А.имя_столбца = С.имя_столбца ]

Например,

SELECT A.LASTJJAME, B.LAST_NIME, A.FIRST_NAME

FROM EMPLOYEE_TBL A,

EMPLOYEEJTBL В

WHERE A.LAST_NAME = B.LAST_NAME;

Этот оператор SQL возвратит имена всех служащих с одинаковыми фамилиями из таблицы EMPLOYEEJTBL. Рекурсивное связывание удобно использовать, когда все необходимые данные размещаются в одной таблице, но требуется каким-то образом сравнить одни записи таблицы с другими.

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

SELECT FROM EMP;

ID NAME MNGR_ID

1 JOHN 0

2 MARY 1

3 STEVE 1

4 JACK 2

5 SUE 2

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

SELECT El.NAME, E2.NAME

FROM EMP El, EMP E2

WHERE E1.MGR_ID = E2.ID;

NAME NAME

MARY JOHN

STEVE JOHN

JACK MARY

SUE MARY

Связывание по нескольким ключам

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

Рассмотрим для примера следующие таблицы.

SQL> desc prod

Имя NULL? Тип данных

SERIAL_NUMBER NOT NULL NUMBER(10)

VENDOR_NUMBER NOT NULL NUMBER(10)

PRODUCT_NAME NOT NULL VARCHAR2OO)

^COST NOT NULL NITMBER (8,2)

SQL> desc prod NULL? Тип данных

ORD_NO NOT NULL NUMBER(10)

SERIAL_NUMBER NOT NULL NUMBER(10}

VENDOKJMUMBER NOT NULL NUMBER(10)

QUANTITY NOT NULL NUMBERt5)

ORD_DATE NOT NULL DATE

Ключом в таблице PROD является комбинация столбцов SERTAL_NUMBER и VENDOR_NUMBER Верояшо, в данной торговой компании некоторые товары имеют один серийный номер, но различаются по коду производителя

Внешним ключом в таблице ORD тоже является комбинация столбцов

SERIAL_NUMBER И VENDOR_NUMBER.

При выборе данных из обеих таблиц (PROD и ORD) оператор связывания может выглядеть следующим образом:

SELECT Р.PRODUCT_NAME, O.ORD_DATE, О.QUANTITY

FROM PROD P, ORD О

WHERE P.SERIAL_NUMBER = 0.SER1AL_NUMBER

AND P.VENDOR_NUMBER = О.VENDOR_NUMBER;

Вопросы связывания

Прежде чем использовать связывание, следует получить ответы на ряд важных вопросов Например, какие из столбцов необходимо связать, имеются ли общие столбцы вообще, а также вопросы оптимизации Вопросы оптимизации будут рассматриваться в ходе урока 18, "Управление доступом к базе данных".

Использование связующей таблицы

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

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

Для примера использования связующей таблицы рассмотрим следующие три таблицы.

CUSTOMER_TBL

CUST_IDVARCHAR2(10) NOT NULL Ключевое поле

CUST_NAME VARCHAR2(30) NOT NULL

CUST_ADDRESS VARCHAR2(20) NOT NULL

CUST_CITY VARCHAR2U5) NOT NULL

CUST_STATE CHAR(2) NOT NULL

CUST_ZIP NUMBER(5) NOT NULL

CUST_PHONE NUMBER(10)

CUST_FAX NUMBER(10)

ORDERS_TBL

ORD_NUMVARCHAR2(10) NOT NULL Ключевое поле

CUST_ID VARCHAR2(10) NOT NULL

PROD_ID VARCHAR2(10) NOT NULL

QTY NUMBER(6) NOT NULL

ORD_DATE DATE

PRODUCTS_TBL

PROD_ID VARCHAR2UO) NOT NULL Ключевое поле

PROD_DESC VARCHAR2(40) NOT NULL

COST NUMBER (6,2) NOT NULL

Необходимо использовать таблицы CUSTOMERJTBL и pfi ODUCTS_TBL. У этих таблиц нет общих столбцов, по которым можно было бы выполнить связывание. Приходится использовать таблицу ORDERS_TBL, в которой имеется поле CUST_ID, присутствующее и в таблице CUSTOMER_TBL, и в таблице PRODUCTS_TBL. Условия связывания и результаты выглядят следующим образом.

SELECT C.CUST_NAME, P.PROD_DESC FROM CUSTOMER_TBL С,

PRODUCTS_TBL P,

ORDERS_TBL О