В этом учебном материале вы узнаете, как использовать SQL JOINS с синтаксисом и примерами.
Описание
SQL JOINS используются для извлечения данных из нескольких таблиц. SQL JOIN выполняется всякий раз, когда две или более таблицы перечислены в операторе SQL.
Существует 4 различных типа соединений SQL:
- SQL INNER JOIN (иногда называется простым соединением)
- SQL LEFT OUTER JOIN (иногда называется LEFT JOIN)
- SQL RIGHT OUTER JOIN (иногда называется RIGHT JOIN)
- SQL FULL OUTER JOIN (иногда называется FULL JOIN)
Итак, давайте обсудим синтаксис SQL JOIN, рассмотрим наглядные иллюстрации SQL JOINS и рассмотрим несколько примеров.
SQL INNER JOIN (простое соединение)
Скорее всего, вы уже писали SQL запрос, который использует SQL INNER JOIN. Это наиболее распространенный тип соединения SQL. INNER JOIN возвращает все строки из нескольких таблиц, где выполняется условие соединения.
Синтаксис
Синтаксис INNER JOIN в SQL:
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Рисунок.
На этом рисунке SQL INNER JOIN возвращает затененную область:
SQL INNER JOIN будет возвращать записи, где пересекаются table1 и table2.
Пример
Давайте рассмотрим пример использования INNER JOIN в запросе.
В этом примере у нас есть таблица customer и следующими данными:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
И таблица orders со следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Выполним следующий SQL оператор:
|
1 2 3 4 5 6 7 |
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 записи. Вот результаты, которые вы должны получить:
| customer_id | order_id | order_date |
|---|---|---|
| 4000 | 4 | 2019/06/20 |
| 5000 | 2 | 2019/06/18 |
| 7000 | 1 | 2019/06/18 |
| 8000 | 3 | 2019/06/19 |
В этом примере будут возвращены все строки из таблиц 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:
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, что и в предыдущем примере:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
И таблицу orders со следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите следующий SQL оператор:
|
1 2 3 4 5 6 7 |
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 записей. Вот результаты, которые вы получите:
| customer_id | order_id | order_date |
|---|---|---|
| 4000 | 4 | 2019/06/20 |
| 5000 | 2 | 2019/06/18 |
| 6000 | NULL | NULL |
| 7000 | 1 | 2019/06/18 |
| 8000 | 3 | 2019/06/19 |
| 9000 | NULL | NULL |
Этот пример 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:
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.
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
И таблицу orders со следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите следующий SQL оператор:
|
1 2 3 4 5 6 7 |
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 записей. Вот результаты, которые вы должны получить:
| customer_id | order_id | order_date |
|---|---|---|
| NULL | 5 | 2019/07/01 |
| 4000 | 4 | 2019/06/20 |
| 5000 | 2 | 2019/06/18 |
| 7000 | 1 | 2019/06/18 |
| 8000 | 3 | 2019/06/19 |
Этот пример 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:
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, что и в предыдущем примере:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
И таблицу orders со следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите следующий 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 записей. Вот результаты, которые вы получите:
| customer_id | order_id | order_date |
|---|---|---|
| NULL | 5 | 2019/07/01 |
| 4000 | 4 | 2019/06/20 |
| 5000 | 2 | 2019/06/18 |
| 6000 | NULL | NULL |
| 7000 | 1 | 2019/06/18 |
| 8000 | 3 | 2019/06/19 |
| 9000 | NULL | NULL |
Это пример 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.