Оператор on в sql: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

Разработка любой базы данных подразумевает не только создание и наполнение таблиц разнообразной информацией, но и дальнейшую работу с данными. Для корректного выполнения разнообразных задач по выбору данных из таблиц и формированию отчетов, используется стандартная конструкция Select.

Выборки данных из таблиц

Если рассматривать задачу выбора данных или построения некоторого отчета, можно определить уровень сложности данной операции. Как правило, при работе с серьезными (по объему информации) базами данных, которые формируются, например, в интернет-магазинах или крупных компаниях, выборка данных не будет ограничиваться лишь одной таблицей. Как правило, выборки могут быть из довольно большого количества не только связанных между собой таблиц, но и вложенных запросов/подзапросов, которые составляет сам программист, в зависимости от поставленной перед ним задачи. Для выборки из одной таблицы можно использовать простейшую конструкцию:

где Person – имя таблицы, из которой необходимо сделать выборку данных.

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

Способы подключения дополнительных таблиц

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

1. Оператор Inner Join.
2. Left Join или, это второй способ записи, Left Outer Join.
3. Cross Join.
4. Full Join.

Использование операторов объединения таблиц на практике можно усвоить, рассмотрев применение оператора SQL — Inner Join. Пример его использования будет выглядеть следующим образом:

Язык SQL и оператор Join Inner Join можно использовать не только для объединения двух и более таблиц, но и для подключения иных подзапросов, что значительно облегчает работу администраторов базы данных и, как правило, может значительно ускорить выполнение определенных, сложных по структуре запросов.

Объединение данных в таблицах построчно

Если рассматривать подключение большого количества подзапросов и сборку данных в единую таблицу строка за строкой, то можно использовать также операторы Union, и Union All.

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

Описание оператора Inner Join

В большинстве случаев для объединения нескольких таблиц в языке SQL используется оператор Inner Join. Описание Inner Join в SQL довольно простое для понимания среднестатистического программиста, который только начинает разбираться в базах данных. Если рассмотреть описание механизма работы этой конструкции, то получим следующую картину. Логика оператора в целом построена на возможности пересечения и выборки только тех данных, которые есть в каждой из входящих в запрос таблиц.

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

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

Синтаксис оператора Inner Join

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

• Inner Join [Имя таблицы] on [ключевое поле из таблицы, к которой подключаем] = [Ключевому полю подключаемой таблицы].

Для связи в данном операторе используются главные ключи таблиц. Как правило, в группе таблиц, которые хранят информацию о сотрудниках, ранее описанные Person и Subdivision имеют хотя бы по одной похожей записи. Итак, рассмотрим подробнее оператор SQL Inner Join, пример которого был показан несколько ранее.

Пример и описание подключения к выборке одной таблицы

У нас есть таблица Person, где хранится информация обо всех сотрудниках, работающих в компании. Сразу отметим, что главным ключем данной таблицы является поле – Pe_ID. Как раз по нему и будет идти связка.

Вторая таблица Subdivision будет хранить информацию о подразделениях, в которых работают сотрудники. Она, в свою очередь, связана с помощью поля Su_Person с таблицей Person. О чем это говорит? Исходя из схемы данных можно сказать, что в таблице подразделений для каждой записи из таблицы «Сотрудники» будет информация об отделе, в котором они работают. Именно по этой связи и будет работать оператор Inner Join.

Для более понятного использования рассмотрим оператор SQL Inner Join (примеры его использования для одной и двух таблиц). Если рассматривать пример для одной таблицы, то тут все довольно просто:

Пример подключения двух таблиц и подзапроса

Оператор SQL Inner Join, примеры использования которого для выборки данных из нескольких таблиц можно организовать вышеуказанным образом, работает по чуть усложненному принципу. Для двух таблиц усложним задачу. Скажем, у нас есть таблица Depart, в которой хранится информация обо всех отделах в каждом из подразделений. В в эту таблицу записан номер подразделения и номер сотрудника и нужно дополнить выборку данных названием каждого отдела. Забегая вперед, стоит сказать, что для решения этой задачи можно воспользоваться двумя методами.

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

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

Стоит отметить, что такая конструкция не всегда может ускорить работу запроса. Иногда бывают случаи, когда приходится использовать дополнительно выборку данных во временную таблицу (если их объем слишком большой), а потом ее объединять с основной выборкой.

Пример использования оператора Inner Join для выборок из большого количества таблиц

Построение сложных запросов подразумевает использование для выборки данных значительного количества таблиц и подзапросов, связанных между собой. Этим требованиям может удовлетворить SQL Inner Join синтаксис. Примеры использования оператора в данном случаем могут усложняться не только выборками из многих мест хранения данных, но и с большого количества вложенных подзапросов. Для конкретного примера можно взять выборку данных из системных таблиц (оператор Inner Join SQL). Пример — 3 таблицы — в этом случае будет иметь довольно сложную структуру.

В данном случае подключено (к основной таблице) еще три дополнительно и введено несколько условий выбора данных.

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

Заключение

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

SQL оператор JOINS — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать SQL JOINS с синтаксисом и примерами.

Описание

SQL JOINS используются для извлечения данных из нескольких таблиц. SQL JOIN выполняется всякий раз, когда две или более таблицы перечислены в операторе SQL.

Существует 4 различных типа соединений SQL:

Итак, давайте обсудим синтаксис SQL JOIN, рассмотрим наглядные иллюстрации SQL JOINS и рассмотрим несколько примеров.

SQL INNER JOIN (простое соединение)

Скорее всего, вы уже писали SQL запрос, который использует SQL INNER JOIN. Это наиболее распространенный тип соединения SQL. INNER JOIN возвращает все строки из нескольких таблиц, где выполняется условие соединения.

Синтаксис

Синтаксис INNER JOIN в SQL:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Рисунок.

На этом рисунке SQL INNER JOIN возвращает затененную область:

SQL INNER JOIN будет возвращать записи, где пересекаются table1 и table2.

Пример

Давайте рассмотрим пример использования INNER JOIN в запросе.

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

И таблица orders со следующими данными:

Выполним следующий SQL оператор:

SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id;

Будет выбрано 4 записи. Вот результаты, которые вы должны получить:

В этом примере будут возвращены все строки из таблиц customers и orders, в которых совпадают значения поля customer_id в обоих таблицах.

Строки, где значение customer_id равен 6000 и 9000 в таблице customers, будут опущены, поскольку они не существуют в обеих таблицах. Строка, в которой значение order_id равно 5 из таблицы orders, будет опущена, поскольку customer_id со значением NULL не существует в таблице customers.

SQL LEFT OUTER JOIN

Другой тип соединения называется LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).

Синтаксис

Синтаксис для LEFT OUTER JOIN в SQL:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных ключевое слово OUTER опущено и записывается просто как LEFT JOIN.

Рисунок

На этом рисунке SQL LEFT OUTER JOIN возвращает затененную область:

SQL LEFT OUTER JOIN возвращает все записи из table1 и только те записи из table2, которые пересекаются с table1.

Пример

Теперь давайте рассмотрим пример, который показывает, как использовать LEFT OUTER JOIN в операторе SELECT.

Используя ту же таблицу customers, что и в предыдущем примере:

И таблицу orders со следующими данными:

Введите следующий SQL оператор:

SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id;

Будет выбрано 6 записей. Вот результаты, которые вы получите:

Этот пример LEFT OUTER JOIN вернул бы все строки из таблицы customers и только те строки из таблицы orders, в которых объединенные поля равны.

Если значение customer_id в таблице customers не существует в таблице orders, все поля таблицы orders будут отображаться как NULL в наборе результатов. Как вы можете видеть, строки, где customer_id равен 6000 и 9000, будут включены в LEFT OUTER JOIN, но поля order_id и order_date отображают NULL.

SQL RIGHT OUTER JOIN JOIN

Другой тип соединения называется SQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).

Синтаксис

Синтаксис для RIGHT OUTER JOIN в SQL:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных ключевое слово OUTER опущено и записывается просто как RIGHT JOIN.

Рисунок

На этом рисунке SQL RIGHT OUTER JOIN возвращает затененную область:

SQL RIGHT OUTER JOIN возвращает все записи из table2 и только те записи из table1, которые пересекаются с table2.

Пример

Теперь давайте рассмотрим пример, который показывает, как использовать RIGHT OUTER JOIN в операторе SELECT.

И таблицу orders со следующими данными:

Введите следующий SQL оператор:

SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id;

Будет выбрано 5 записей. Вот результаты, которые вы должны получить:

Этот пример RIGHT OUTER JOIN вернул бы все строки из таблицы orders и только те строки из таблицы customers, где объединенные поля равны.

Если значение customer_id в таблице orders не существует в таблице customers, то все поля в таблице customers будут отображаться как NULL в наборе результатов. Как видите, строка, где order_id равен 5, будет включена в RIGHT OUTER JOIN, но в поле customer_id отображается NULL.

SQL FULL OUTER JOIN

Другой тип объединения называется SQL FULL OUTER JOIN. Этот тип объединения возвращает все строки из LEFT таблицы и RIGHT таблицы со значениями NULL в месте, где условие соединения не выполняется.

Синтаксис

Синтаксис для SQL FULL OUTER JOIN:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных ключевое слово OUTER опускается и записывается просто как FULL JOIN.

Рисунок

На этом рисунке SQL FULL OUTER JOIN возвращает затененную область:

SQL FULL OUTER JOIN возвращает все записи из таблиц table1 и table2.

Пример

Давайте рассмотрим пример, который показывает, как использовать FULL OUTER JOIN в операторе SELECT.

Используя ту же таблицу customers, что и в предыдущем примере:

И таблицу orders со следующими данными:

Введите следующий SQL оператор:
SELECT customers.customer_id,
orders.order_id,
orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

Будет выбрано 7 записей. Вот результаты, которые вы получите:

Это пример FULL OUTER JOIN будет возвращать все строки из таблицы orders и все строки из таблицы customers. Всякий раз, когда условие соединения не выполняется, значение NULL будет распространяться на эти поля в наборе результатов. Это означает, что если значение customer_id в таблице customers не существует в таблице orders, то все поля в таблице orders будут отображаться в наборе результатов как NULL Кроме того, если значение customer_id в таблице orders не существует в таблице customers, то все поля в таблице customers будут отображаться в наборе результатов как NULL.

Как видите, строки, где customer_id равен 6000 и 9000, будут включены, но поля order_id и order_date для этих записей содержат значение NULL. Строка, где order_id равен 5, также будет включена, но поле customer_id для этой записи имеет значение NULL.

SQL JOIN — соединение таблиц базы данных

Оператор языка SQL JOIN предназначен для соединения двух или более таблиц базы данных по совпадающему условию. Этот оператор существует только в реляционных базах данных. Именно благодаря JOIN реляционные базы данных обладают такой мощной функциональностью, которая позволяет вести не только хранение данных, но и их, хотя бы простейший, анализ с помощью запросов. Разберём основные нюансы написания SQL-запросов с оператором JOIN, которые являются общими для всех СУБД (систем управления базами данных). Для соединения двух таблиц оператор SQL JOIN имеет следующий синтаксис:

SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ

После одного или нескольких звеньев с оператором JOIN может следовать необязательная секция WHERE или HAVING, в которой, также, как в простом SELECT-запросе, задаётся условие выборки. Общим для всех СУБД является то, что в этой конструкции вместо JOIN может быть указано INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN (или, как вариант, запятая).

Запрос с оператором INNER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON.

То же самое делает и просто JOIN. Таким образом, слово INNER — не обязательное.

Есть база данных портала объявлений — 2. В ней есть таблица Categories (категории объявлений) и Parts (части, или иначе — рубрики, которые и относятся к категориям). Например, части Квартиры, Дачи относятся к категории Недвижимость, а части Автомобили, Мотоциклы — к категории Транспорт.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Скрипт для создания базы данных портала объявлений — 2, её таблиц и заполения таблиц данными — в файле по этой ссылке.

Таблицы этой базы данных с заполненными данными имеют следующий вид.

Таблица Categories:

Таблица Parts:

Заметим, что в таблице Parts Книги имеют Cat — ссылку на категорию, которой нет в таблице Categories, а в таблице Categories Техника имеет номер категории Catnumb — значение, ссылки на которое нет в таблице Parts.

Пример 1. Требуется соединить данные этих двух таблиц так, чтобы в результирующей таблице были поля Part (Часть), Cat (Категория) и Price (Цена подачи объявления) и чтобы данные полностью пересекались по условию. Условие — совпадение номера категории (Catnumb) в таблице Categories и ссылки на категорию в таблице Parts. Для этого пишем следующий запрос:

SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price FROM Parts INNER JOIN Categories ON Parts.Cat = Categories.Catnumb

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

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

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

Есть база данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 2. Определить самого востребованного актёра за последние 5 лет.

Оператор JOIN использовать 2 раза. Использовать COUNT(), CURDATE(), LIMIT 1.

Правильное решение и ответ.

Пример 3. Вывести список актеров, которые в одном спектакле играют более одной роли, и количество их ролей.

Оператор JOIN использовать 1 раз. Использовать HAVING, GROUP BY.

Подсказка. Оператор HAVING применяется к числу ролей, подсчитанных агрегатной функцией COUNT.

Правильное решение и ответ.

Запрос с оператором LEFT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой по порядку (левой) таблицы, даже если они не соответствуют условию. У записей левой таблицы, которые не соответствуют условию, значение столбца из правой таблицы будет NULL (неопределённым).

Пример 4. База данных и таблицы — те же, что и в примере 1.

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

SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price FROM Parts LEFT OUTER JOIN Categories ON Parts.Cat = Categories.Catnumb

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

В результирующей таблице, в отличие от таблицы из примера 1, есть Книги, но значение столбца Цены (Price) у них — NULL, так как эта запись имеет идентификатор категории, которой нет в таблице Categories.

Запрос с оператором RIGHT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из второй по порядку (правой) таблицы, даже если они не соответствуют условию. У записей правой таблицы, которые не соответствуют условию, значение столбца из левой таблицы будет NULL (неопределённым).

Пример 5. База данных и таблицы — те же, что и в предыдущих примерах.

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

SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price FROM Parts RIGHT OUTER JOIN Categories ON Parts.Cat = Categories.Catnumb

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

В результирующей таблице, в отличие от таблицы из примера 1, есть запись с категорией 45 и ценой 65,00, но значение столбца Части (Part) у неё — NULL, так как эта запись имеет идентификатор категории, на которую нет ссылок в таблице Parts.

Запрос с оператором FULL OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой (левой) и второй (правой) таблиц, даже если они не соответствуют условию. У записей, которые не соответствуют условию, значение столбцов из другой таблицы будет NULL (неопределённым).

Пример 6. База данных и таблицы — те же, что и в предыдущих примерах.

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

SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price FROM Parts FULL OUTER JOIN Categories ON Parts.Cat = Categories.Catnumb

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

В результирующей таблице есть записи Книги (из левой таблицы) и с категорией 45 (из правой таблицы), причём у первой из них неопределённая цена (столбец из правой таблицы), а у второй — неопределённая часть (столбец из левой таблицы).

В предыдущих запросах мы указывали с названиями извлекаемых столбцов из разных таблиц полные имена этих таблиц. Такие запросы выглядят громоздко: одно и то же слово повторяется несколько раз. Нельзя ли как-то упростить конструкцию? Оказывается, можно. Для этого следует использовать псевдонимы таблиц — их сокращённые имена. Псевдоним может состоять и из одной буквы. Возможно любое количество букв в псевдониме, главное, чтобы запрос после сокращения был понятен Вам самим. Общее правило: в секции запроса, определяющей соединение, то есть вокруг слова JOIN нужно указать полные имена таблиц, а за каждым именем должен следовать псевдоним таблицы.

Пример 7. Переписать запрос из примера 1 с использованием псевдонимов соединяемых таблиц.

Запрос будет следующим:

SELECT P.Part, C.Catnumb AS Cat, C.Price FROM Parts P INNER JOIN Categories C ON P.Cat = C.Catnumb

Запрос вернёт то же самое, что и запрос в примере 1, но он гораздо компактнее.

Реляционные базы данных должны подчиняться требованиям целостности и неизбыточности данных, в связи с чем данные об одном бизнес-процессе могут содержаться не только в одной, двух, но и в трёх и более таблицах. В этих случаях для анализа данных используются цепочки соединённых таблиц: например, в одной (первой) таблице содержится некоторый количественный показатель, вторую таблицу с первой и третьей связывают внешние ключи — данные пересекаются, но только третья таблица содержит условие, в зависимости от которого может быть выведен количественный показатель из первой таблицы. И таблиц может быть ещё больше. При помощи оператора SQL JOIN в одном запросе можно соединить большое число таблиц. В таких запросах за одной секцией соединения следует другая, причём каждый следующий JOIN соединяет со следующей таблицей таблицу, которая была второй в предыдущем звене цепочки. Таким образом, синтаксис SQL запроса для соединения более двух таблиц следующий:

SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ JOIN ИМЯ_ТАБЛИЦЫ_3 ON УСЛОВИЕ … JOIN ИМЯ_ТАБЛИЦЫ_M ON УСЛОВИЕ

Пример 8. База данных — та же, что и в предыдущих примерах. К таблицам Categories и Parts в этом примере добавится таблица Ads, содержащая данные об опубликованных на портале объявлениях. Приведём фрагмент таблицы Ads, в котором среди записей есть записи о тех объявлениях, срок публикации которых истекает 2018-04-02.

Представим, что сегодня ‘2018-04-02’, то есть это значение принимает функция CURDATE() — текущая дата. Требуется узнать, к каким категориям принадлежат объявления, срок публикации которых истекает сегодня. Названия категорий есть только в таблице CATEGORIES, а даты истечения срока публикации объявлений — только в таблице ADS. В таблице PARTS — части категорий (или проще, подкатегории) опубликованных объявлений. Но внешним ключом Cat_ID таблица PARTS связана с таблицей CATEGORIES, а таблица ADS связана внешним ключом Part_ID с таблицей PARTS. Поэтому соединяем в одном запросе три таблицы и этот запрос можно с максимальной корректностью назвать цепочкой.

Запрос будет следующим:

SELECT C.Cat_name FROM Categories C JOIN Parts P ON P.Cat=C.Catnumb JOIN ads A ON A.Part_id=P.Part_id WHERE A.Date_end=CURDATE()

Результат запроса — таблица, содержащая названия двух категорий — «Недвижимость» и «Транспорт»:

Использование оператора SQL CROSS JOIN в наиболее простой форме — без условия соединения — реализует операцию декартова произведения в реляционной алгебре. Результатом такого соединения будет сцепление каждой строки первой таблицы с каждой строкой второй таблицы. Таблицы могут быть записаны в запросе либо через оператор CROSS JOIN, либо через запятую между ними.

Пример 9. База данных — всё та же, таблицы — Categories и Parts. Реализовать операцию декартова произведения этих двух таблиц.

Запрос будет следующим:

SELECT (*) Categories CROSS JOIN Parts

Или без явного указания CROSS JOIN — через запятую:

SELECT (*) Categories, Parts

Запрос вернёт таблицу из 5 * 5 = 25 строк, фрагмент которой приведён ниже:

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

Но для CROSS JOIN можно задать условие соединения! Результат будет совсем иным. При использовании оператора «запятая» вместо явного указания CROSS JOIN условие соединения задаётся не словом ON, а словом WHERE.

Пример 10. Та же база данных портала объявлений, таблицы Categories и Parts. Используя перекрестное соединение, соединить таблицы так, чтобы данные полностью пересекались по условию. Условие — совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts.

Запрос будет следующим:

SELECT P.Part, C.Catnumb AS Cat, C.Price FROM Parts P, Categories C WHERE P.Cat = C.Cat_ID

Запрос вернёт то же самое, что и запрос в примере 1:

И это совпадение не случайно. Запрос c перекрестным соединением по условию соединения полностью аналогичен запросу с внутренним соединением — INNER JOIN — или, учитывая, что слово INNER — не обязательное, просто JOIN.

Таким образом, какой вариант запроса использовать — вопрос стиля или даже привычки специалиста по работе с базой данных. Возможно, перекрёстное соединение с условием для двух таблиц может представляться более компактным. Но преимущество перекрестного соединения для более чем двух таблиц (это также возможно) весьма спорно. В этом случае WHERE-условия пересечения перечисляются через слово AND. Такая конструкция может быть громоздкой и трудной для чтения, если в конце запроса есть также секция WHERE с условиями выборки.

Поделиться с друзьями

Реляционные базы данных и язык SQL

FROM <таблица 1>
 [INNER]
 {{LEFT | RIGHT | FULL } [OUTER]} JOIN <таблица 2>
[ON <предикат>]

id   name          id   sum
--   ----          --   ----
1    bmw           1    250
2    opel          5    450
3    kia           3    300
4    audi          6    400

SELECT * FROM 'Auto'
INNER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id

id   name          id   sum
--   ----          --   ----
1    bmw           1    250
3    kia           3    300

SELECT * FROM 'Auto'
FULL OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id

id   name          id   sum
--   ----          --   ----
1    bmw           1    250
2    opel        NULL   NULL
3    kia           3    300
4    audi        NULL   NULL
NULL NULL          5    450
NULL NULL          6    400

SELECT * FROM 'Auto'
LEFT OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id

id   name          id   sum
--   ----          --   ----
1    bmw           1    250
2    opel        NULL   NULL
3    kia           3    300
4    audi        NULL   NULL

SELECT * FROM 'Auto'
LEFT OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id
WHERE 'Selling'.id IS null

id   name          id   sum
--   ----          --   ----
2    opel        NULL   NULL
4    audi        NULL   NULL

SELECT * FROM 'Auto'
FULL OUTER JOIN 'Selling'
ON 'Auto'.id = 'Selling'.id
WHERE 'Auto'.id IS null
OR 'Selling'.id IS null

id   name          id   sum
--   ----          --   ----
2    opel        NULL   NULL
4    audi        NULL   NULL
NULL NULL          5    450
NULL NULL          6    400

SELECT поля_таблиц
FROM таблица_1 
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2 
    ON условие_соединения
[[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_n 
    ON условие_соединения]

SELECT поля_таблиц
FROM таблица_1 
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2[ JOIN таблица_n] 
    ON условие_соединения [AND условие_соединения]

SELECT *
FROM Payments 
    JOIN FamilyMembers ON family_member = member_id;

SELECT *
FROM Payments, FamilyMembers
WHERE family_member = member_id;

SELECT *
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;

SELECT * 
FROM левая_таблица 
LEFT JOIN правая_таблица 
    ON правая_таблица.ключ = левая_таблица.ключ

UNION ALL

SELECT * 
FROM левая_таблица 
RIGHT JOIN правая_таблица 
    ON правая_таблица.ключ = левая_таблица.ключ
WHERE левая_таблица.key IS NULL