W Cat - SQL за 24 часа
311549902 STEPHENS TEAM LEADER
213764555 GLASS TEAM LEADER
313782439 GLASS TEAM LEADER
220984332 WALLACE TEAM LEADER
443679012 SPURGEON TEAM LEADER
311549902 STEPHENS SALES MANAGER
442346889 PLEW SALES MANAGER
313782439 GLASS SALES MANAGER
220984332 WALLACE SALES MANAGER
443679012 SPURGEON SALES MANAGER
3115499C2 STEPHENS SALESMAN
442346889 PLEW SALESMAN
213764555 GLASS SALESMAN
220984332 WALLACE SALESMAN
443679012 SPURGEON SALESMAN
311549902 STEPHENS SHIPPER
442346889 PLEW SHIPPER
213764555 GLASS SHIPPER
313782439 GLASS SHIPPER
30 строк выбраны.
Вы, наверное, удивитесь, что в данном случае из базы данных были извлечены 30 строк данных, хотя в таблице имеется всего 6 записей Объяснение этому следующее. Каждой записи из таблицы EMPLOYEE_TBL соответствует запись из таблицы EMPLOYEE_PAY_TBL. Но по причине связывания по неравенству каждой записи из первой таблицы сопоставляются все записи второй таблицы, за исключением той самой единственной соответствующей записи Значит, каждая строка первой таблицы оказывается связанной с пятью строками второй. Записей всего 6, поэтому в результате мы получаем 30 строк.
В отличие от данного случая, в предыдущем разделе при связывании по равенству каждая строка таблицы связывалась с единственной соответствующей строкой второй таблицы, поэтому там в выводе получалось 6 строк.
При связывании по неравенству обычно в выводе присутствует ряд строк, которые на самом деле оказываются ненужными, так что результаты запроса, в котором используется связывание по неравенству, всегда требуют дополнительной проверки
Внешнее связывание
Внешнее связывание (OUTER JOIN) используется, когда вывод должен содержать все записи одной таблицы, даже если некоторые из ее записей не имеют соответствующих записей в другой таблице. В запросе для задания внешнего связывания используется (+). Символ (+) помещается в конце имени таблицы в выражении ключевого слова WHERE. Таблицей с символами ( + ) должна быть та таблица, записям которой нет соответствующих записей из другой таблицы. Во многих реализациях языка внешнее связывание разбито на левое внешнее связывание (LEFT OUTER JOIN), правое внешнее связывание (RIGHT OUTER JOIN) и полное внешнее связывание (FULL OUTER JOIN). В таких реализациях внешнее связывание (OUTER JOIN) обычно является опцией.
Обязательно проверьте правила использования внешнего связывания и синтаксис соответствующих операторов по документации той реализации языка, с которой вы работаете Символы ( + ) используются в большинстве основных реализаций SQL, но стандартом они не задаются
Общий синтаксис следующий.
FROM таблица1
{RIGHT | LEFT | FULL} [OUTER] JOIN
ON таблица2
В Oracle используется следующий синтаксис.
FROM таблица1, таблица2 [,таблицаЗ ]
WHERE таблица1.имя_столбца[(+)] = таблица2.имя_столбца[(+)]
[ AND таблица!.имя_столбца[(+)] = таблицаЗ.имя_столбца[(+)]]
Внешнее связывание можно задать только для,одной из связываемых сторон, но зато внешнее связывание можно указать для нескольких столбцов одной таблицы.
Использование внешнего связывания поясняется следующими примерами. В первом примере выбираются описание товара и его количество, причем эти значения извлекаются из двух разных таблиц. Здесь нужно помнить о том, что не для каждого товара имеется соответствующая запись в таблице ORDERS_TBL. Сначала используем связывание по равенству:
SELECT P.PROD_DESC, О. QTY
FROM PRODUCTS_TBL P,
ORDERS_TBL О
WHERE P.PROD_ID = O.PROD_ID;
PRODJ3ESC QTY
КОСТЮМ ВЕДЬМЫ 1
ПЛАСТИКОВЫЕ ТЫКВЫ 25
ПЛАСТИКОВЫЕ ТЫКВЫ 2
ФОНАРИ 10
ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ 20
ЦЕПОЧКА ДЛЯ КЛЮЧЕЙ 1
6 строк выбраны.
Здесь оказались выбранными всего 6 строк, хотя различных товаров более 10. Но вы хотели бы увидеть список всех товаров, независимо от того, имеются ли на них заказы или нет.
В следующем примере эта задача решается с помощью внешнего связывания. Для соответствующего оператора используется синтаксис Oracle.
SELECT P.PROD_DESC, О.QTY
FROM PRODUCTS_TBL P,
ORDERS_TBL О
WHERE P . PROD_ID = О . PROD_ID (+);
PROD_DESC QTY
КОСТЮМ ВЕДЬМЫ 1
МАСКИ В АССОРТИМЕНТЕ
ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ 20
КОСТЮМЫ В АССОРТИМЕНТЕ
ПЛАСТИКОВЫЕ ТЫКВЫ 25
ПЛАСТИКОВЫЕ ТЫКВЫ 2
ТЫКВЕННЫЕ КОНФЕТЫ
ПЛАСТИКОВЫЕ ПАУКИ
СЛАДКАЯ КУКУРУЗА
ФОНАРИ 10
ЦЕПОЧКА ДЛЯ КЛЮЧЕЙ 1
ПОЛОЧКА ИЗ ДУБА
12 строк выбраны.
Теперь запрос возвращает список всех товаров, включая те, для которых не указано количество заказов в таблице. Внешняя связь предполагает включение в вывод всех строк данных из таблицы PRODUCTSJTBL независимо от того, существуют для них соответствующие записи в таблице ORDERSJTBL или нет.
Рекурсивное связывание
Рекурсивное связывание (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
