В этом учебном пособии вы узнаете, как использовать SQLite JOINS (inner и outer) с синтаксисом и примерами.
Описание
SQLite JOINS используются для извлечения данных из нескольких таблиц. SQLite JOIN выполняется всякий раз, когда две или более таблицы объединяются в операторе SQL.
Существуют различные типы SQLite JOIN:
- INNER JOIN (или иногда называется простым соединением)
- LEFT OUTER JOIN (или иногда называется LEFT JOIN)
- CROSS JOIN
Подсказка. В SQLite не поддерживаются RIGHT OUTER JOIN и FULL OUTER JOIN.
Итак, давайте обсудим синтаксис SQLite JOIN, рассмотрим наглядные иллюстрации SQLite JOINS и рассмотрим некоторые примеры.
INNER JOIN (Простое соединение)
SQLite INNER JOIN это самый распространенный тип объединения. SQLite INNER JOINS возвращают все строки из нескольких таблиц, где выполняется условие соединения.
Синтаксис
Синтаксис INNER JOIN в SQLite:
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Рисунок
На этом рисунке SQLite INNER JOIN возвращает затененную область:
SQLite INNER JOIN будет возвращать записи, где пересекаются table1 и table2.
Пример
Вот пример SQLite INNER JOIN:
|
1 2 3 4 5 6 |
SELECT employees.employee_id, employees.last_name, positions.title FROM employees INNER JOIN positions ON employees.position_id = positions.position_id; |
В этом SQLite примере INNER JOIN будут возвращены все строки из таблиц employees и positions, где в таблицах employees и positions есть совпадающее значение position_id.
Давайте посмотрим на некоторые данные, чтобы понянть, как работает INNER JOINS:
У нас есть таблица с именем employee и четырьмя полями (employee_id, last_name, first_name и position_id). Она содержит следующие данные:
| employee_id | last_name | first_name | position_id |
|---|---|---|---|
| 10000 | Petrov | Alex | 1 |
| 10001 | Ivanov | Max | 2 |
| 10002 | Sidorov | Piter | 3 |
| 10003 | Nikonov | Simon |
У нас есть еще одна таблица с названием position с двумя полями (position_id и title). Она содержит следующие данные:
| position_id | title |
|---|---|
| 1 | Manager |
| 2 | Project Planner |
| 3 | Programmer |
| 4 | Data Analyst |
Если мы запустим SQLite оператор SELECT (который содержит INNER JOIN) ниже:
|
1 2 3 4 5 6 |
SELECT employees.employee_id, employees.last_name, positions.title FROM employees INNER JOIN positions ON employees.position_id = positions.position_id; |
Наш результирующий набор будет выглядеть так:
| employee_id | last_name | title |
|---|---|---|
| 10000 | Petrov | Manager |
| 10001 | Ivanov | Project Planner |
| 10002 | Sidorov | Programmer |
Строка для employee_id 10003 из таблицы employees была бы опущена, так как соответствующий position_id не существует в таблице positions. Строка для position_id, равная 4, из таблицы positions будет опущена, поскольку этот positions не существует в таблице employees.
Старый синтаксис
В заключение следует отметить, что приведенный выше пример SQLite INNER JOIN можно переписать с использованием более старого неявного синтаксиса следующим образом (но мы все же рекомендуем использовать синтаксис ключевого слова INNER JOIN):
|
1 2 3 4 5 |
SELECT employees.employee_id, employees.last_name, positions.title FROM employees, positions WHERE employees.position_id = positions.position_id; |
LEFT OUTER JOIN
Другой тип объединения SQLite называется LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие объединения).
Синтаксис
Синтаксис SQLite LEFT OUTER JOIN:
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
Рисунок
На этом рисунке SQLite LEFT OUTER JOIN возвращает затененную область:
SQLite LEFT OUTER JOIN возвращает все записи из table1 и только те записи из table2, которые пересекаются с table1.
Пример
Вот пример SQLite LEFT OUTER JOIN:
|
1 2 3 4 5 6 |
SELECT employees.employee_id, employees.last_name, positions.title FROM employees LEFT OUTER JOIN positions ON employees.position_id = positions.position_id; |
В этом примере LEFT OUTER JOIN будут возвращены все строки из таблицы employees и только те строки из таблицы positions, где объединенные поля равны.
Если значение position_id в таблице employees не существует в таблице positions, все поля в таблице positions будут отображаться как
Давайте посмотрим на некоторые данные, чтобы понять, как работает LEFT OUTER JOINS:
У нас есть таблица с employee и четырьмя полями (employee_id, last_name, first_name и position_id). Она содержит следующие данные:
| employee_id | last_name | first_name | position_id |
|---|---|---|---|
| 10000 | Petrov | Alex | 1 |
| 10001 | Ivanov | Max | 2 |
| 10002 | Sidorov | Piter | 3 |
| 10003 | Nikonov | Simon |
У нас есть вторая таблица с position и двумя полями (position_id и title). Она содержит следующие данные:
| position_id | title |
|---|---|
| 1 | Manager |
| 2 | Project Planner |
| 3 | Programmer |
| 4 | Data Analyst |
Если мы запустим оператор SELECT (который содержит LEFT OUTER JOIN) ниже:
|
1 2 3 4 5 6 |
SELECT employees.employee_id, employees.last_name, positions.title FROM employees LEFT OUTER JOIN positions ON employees.position_id = positions.position_id; |
Наш результирующий набор будет выглядеть так:
| employee_id | last_name | title |
|---|---|---|
| 10000 | Petrov | Manager |
| 10001 | Ivanov | Project Planner |
| 10002 | Sidorov | Programmer |
| 10003 | Nikonov |
Строка для employee_id = 10003 будет включена, поскольку был использован LEFT OUTER JOIN. Однако вы заметите, что поле заголовка для этой записи содержит значение
CROSS JOIN
Другой тип SQLite объединения называется CROSS JOIN. Этот тип объединения возвращает комбинированный результирующий набор с каждой строкой из первой таблицы, сопоставленной с каждой строкой из второй таблицы. Это также называется декартовым произведением.
Синтаксис
Синтаксис для SQLite CROSS JOIN:
FROM table1
CROSS JOIN table2;
Подсказка. В отличие от соединения INNER или OUTER, CROSS JOIN не имеет условий для объединения двух таблиц.
Рисунок
На этом рисунке SQLite CROSS JOIN возвращает каждую строку из table1, соответствующую каждой строке из table2.
Пример
Вот пример SQLite CROSS JOIN:
|
1 2 3 |
SELECT * FROM positions CROSS JOIN departments; |
Этот пример CROSS JOIN вернул бы все строки из таблицы departments, соответствующие всем строкам из таблицы positions.
Давайте рассмотрим некоторые данные, чтобы объяснить, как работает CROSS JOINS:
У нас есть таблица positions содержащая следующие данные:
| position_id | title |
|---|---|
| 1 | Manager |
| 2 | Project Planner |
| 3 | Programmer |
| 4 | Data Analyst |
У нас есть вторая таблица под названием departments со следующими данными:
| department_id | department_name |
|---|---|
| 30 | HR |
| 999 | Sales |
Если мы запустим запрос SELECT (который содержит CROSS JOIN) ниже:
|
1 2 3 |
SELECT * FROM positions CROSS JOIN departments; |
Наш результирующий набор будет выглядеть так:
| position_id | title | department_id | department_name |
|---|---|---|---|
| 1 | Manager | 30 | HR |
| 1 | Manager | 999 | Sales |
| 2 | Project Manager | 30 | HR |
| 2 | Project Manager | 999 | Sales |
| 3 | Programmer | 30 | HR |
| 3 | Programmer | 999 | Sales |
| 4 | Data Analyst | 30 | HR |
| 4 | Data Analyst | 999 | Sales |
Поскольку таблица positions имеет 4 строки, а departments - 2 строки, перекрестное соединение вернет 8 строк (потому что 4x2 = 8). Каждая строка таблицы positions сопоставляется с каждой строкой таблицы departments.