SQL JOIN: виды соединений, синтаксис и примеры

SQL JOIN соединяет строки двух таблиц в одну строку результата по условию, обычно по равенству ключей: ON orders.customer_id = customers.id. Вид JOIN решает, что делать со строками, которым пары не нашлось: INNER JOIN их отбрасывает, LEFT, RIGHT и FULL JOIN оставляют и заполняют недостающие столбцы значением NULL, а CROSS JOIN строит все возможные комбинации строк без всякого условия.

Запросы выполнены в SQLite 3.50 из Python 3.14 одним файлом. RIGHT и FULL JOIN в SQLite работают с версии 3.39.0, проверить свою можно командой python -c "import sqlite3; print(sqlite3.sqlite_version)".

Что такое JOIN в SQL и зачем он нужен

Столбец orders.customer_id хранит id клиента из таблицы customers: информацию о клиенте и его заказах хранят в разных таблицах базы данных, иначе имя и город клиента пришлось бы повторять в каждом заказе (подробнее об этом в статье про нормализацию баз данных). Такую связь держит внешний ключ. Чтобы получить в одном SQL-запросе имя клиента и сумму заказа, таблицы соединяют оператором JOIN.

Внешний ключ для JOIN не обязателен: условие соединения может сравнивать любые столбцы. В примерах ниже это пара «первичный ключ и внешний ключ».

Сохраните раннер как run.py, блоки SQL из статьи подряд в файл join.sql и запустите python run.py join.sql. Блок с пометкой -- общий вид в первой строке в файл не копируйте. Раннер печатает шапку и строки каждого SELECT, NULL пишет словом; нужен Python 3.12 или новее (параметр autocommit). Значения в запросах статьи записаны прямо в SQL; в приложении данные из ввода пользователя передают параметрами, а не склеивают в строку запроса, иначе получится SQL-инъекция.

import sqlite3
import sys

print("SQLite", sqlite3.sqlite_version)
conn = sqlite3.connect(":memory:", autocommit=True)
buf = ""
for line in open(sys.argv[1], encoding="utf-8"):
    buf += line
    if sqlite3.complete_statement(buf):
        try:
            cur = conn.execute(buf)
            if cur.description:  # SELECT: шапка и строки
                print("|".join(d[0] for d in cur.description))
                for row in cur:
                    print("|".join("NULL" if v is None else str(v) for v in row))
                print()
        except sqlite3.Error as e:
            print(f"{type(e).__name__}: {e}\n")
        buf = ""

Учебная база данных магазина состоит из трёх таблиц: четыре клиента, четыре товара, пять заказов. Глеб ничего не заказывал, заказ 104 оформлен без регистрации и клиента не имеет, наушники никто не купил.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  invited_by INTEGER REFERENCES customers (id)
);
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  price INTEGER NOT NULL
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers (id),
  product_id INTEGER NOT NULL REFERENCES products (id),
  amount INTEGER NOT NULL
);
INSERT INTO customers VALUES
  (1, 'Анна', 'Москва', NULL),
  (2, 'Борис', 'Казань', NULL),
  (3, 'Вера', 'Москва', 1),
  (4, 'Глеб', 'Пермь', 3);
INSERT INTO products VALUES
  (1, 'Клавиатура', 1500),
  (2, 'Мышь', 700),
  (3, 'Монитор', 12000),
  (4, 'Наушники', 3000);
INSERT INTO orders VALUES
  (101, 1, 1, 1500),
  (102, 1, 1, 1500),
  (103, 2, 2, 700),
  (104, NULL, 3, 12000),
  (105, 3, 2, 700);

Синтаксис JOIN

-- общий вид, не для запуска
SELECT столбцы
FROM левая_таблица AS л
[INNER | LEFT | RIGHT | FULL] JOIN правая_таблица AS п
  ON условие_соединения;

Таблица, которая стоит в FROM, считается левой, та, что после JOIN, правой. Тип соединения указывают словом перед JOIN, условие пишут после ON. Слова INNER и OUTER можно не писать: просто JOIN означает INNER JOIN, а LEFT JOIN то же, что LEFT OUTER JOIN.

Чтобы выбрать нужный тип, решите, какие строки без пары должны остаться в результате.

Если столбец с одинаковым названием есть в двух таблицах, перед ним нужен префикс: имя таблицы (customers.id) или её псевдоним (c.id). Псевдонимы c, o, p короче имён, а для таблиц обязательны только тогда, когда таблицу соединяют саму с собой. В нашей схеме id есть и у клиентов, и у заказов, поэтому запрос без префикса упадёт:

SELECT id, name FROM customers c JOIN orders o ON o.customer_id = c.id;

OperationalError: ambiguous column name: id

Чтобы запрос заработал, укажите c.id или o.id, смотря что нужно.

INNER JOIN: только совпадения

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

SELECT c.name, o.id AS order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;

name|order_id
Анна|101
Анна|102
Борис|103
Вера|105

Анна из таблицы customers встречается дважды, по разу на каждый заказ. Строка левой таблицы повторяется столько раз, сколько пар нашлось в правой. Глеба нет, потому что заказов у него нет. Нет и заказа 104: в его customer_id записан NULL, а сравнение через = с NULL не даёт «истину».

LEFT JOIN: все строки левой таблицы

LEFT JOIN сначала делает то же, что INNER JOIN, а потом добавляет строки левой таблицы, которым пары не нашлось. Столбцы правой таблицы у них заполнены NULL. LEFT JOIN нужен, если хотите увидеть «всех клиентов и их заказы, если есть», например в отчёте, в котором должны быть даже клиенты без покупок.

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

name|order_id
Анна|101
Анна|102
Борис|103
Вера|105
Глеб|NULL

Как найти строки без пары

Клиентов без единого заказа ищут с помощью LEFT JOIN и проверки IS NULL по столбцу правой таблицы, который в настоящих строках NULL не бывает. Первичный ключ o.id подходит.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

name
Глеб

Проверять нужно именно IS NULL: o.id = NULL даёт NULL и не пропустит ни одной строки.

RIGHT JOIN: все строки правой таблицы

RIGHT JOIN зеркален LEFT JOIN: сохраняет все строки правой таблицы. Здесь это таблица orders, то есть все заказы, включая гостевой.

SELECT c.name, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;

name|order_id
Анна|101
Анна|102
Борис|103
NULL|104
Вера|105

Те же строки даёт запрос, где таблицы поменяли местами: FROM orders o LEFT JOIN customers c ON .... Советуем написать его именно так: запрос начинается с главной таблицы, и все внешние соединения в нём одного вида, LEFT.

FULL OUTER JOIN: все строки обеих таблиц

FULL JOIN объединяет оба поведения: в результате есть все строки обеих таблиц, то есть все клиенты и все заказы, а где пары нет, стоит значение NULL. Такой запрос полезен при сверке двух таблиц: в одном наборе строк видно и клиентов, которые ничего не купили, и гостевые заказы.

SELECT c.name, o.id AS order_id
FROM customers c
FULL JOIN orders o ON o.customer_id = c.id
ORDER BY o.id NULLS LAST;

name|order_id
Анна|101
Анна|102
Борис|103
NULL|104
Вера|105
Глеб|NULL

NULLS LAST ставит строку Глеба с пустым order_id вниз. Без него SQLite при сортировке по возрастанию выводит NULL первыми, а PostgreSQL последними.

Как заменить FULL JOIN в MySQL

В MySQL полного внешнего соединения нет. Его собирают из двух запросов: LEFT JOIN даёт всех клиентов, RIGHT JOIN с условием c.id IS NULL добавляет только заказы без клиента.

-- замена FULL JOIN для MySQL
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION ALL
SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL;

name|amount
Анна|1500
Анна|1500
Борис|700
Вера|700
Глеб|NULL
NULL|12000

В SQLite этот запрос дал те же шесть строк, что FULL JOIN выше, только вместо номера заказа сумма. Ошибкой будет склеить LEFT и RIGHT JOIN через UNION без ALL и без фильтра. UNION убирает одинаковые строки, а два заказа Анны по 1500 в такой выборке одинаковы:

SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.amount FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;

name|amount
NULL|12000
Анна|1500
Борис|700
Вера|700
Глеб|NULL

Пять строк вместо шести: у Анны остался один заказ на 1500 из двух.

CROSS JOIN: все возможные комбинации

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

CREATE TABLE sizes (size TEXT);
CREATE TABLE colors (color TEXT);
INSERT INTO sizes VALUES ('S'), ('M'), ('L');
INSERT INTO colors VALUES ('белый'), ('чёрный');
SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;

size|color
S|белый
S|чёрный
M|белый
M|чёрный
L|белый
L|чёрный

Три размера на два цвета дают шесть строк. Без ORDER BY порядок строк СУБД не обещает, в другой базе он может быть иным.

Тот же эффект бывает случайным. Если забыть ON, SQLite и MySQL выполнят обычный JOIN как CROSS JOIN:

SELECT COUNT(*) AS rows_count FROM customers JOIN orders;

rows_count
20

Четыре клиента на пять заказов, двадцать строк без единой ошибки. PostgreSQL такой запрос не примет: у JOIN без CROSS там обязательно условие.

SELF JOIN: таблица соединяется сама с собой

Отдельного слова SELF JOIN в SQL нет: это обычный JOIN, где одна таблица стоит дважды под разными псевдонимами. Без псевдонимов СУБД не поймёт, о какой копии речь. В таблице customers столбец invited_by хранит id клиента, который пригласил этого.

SELECT c.name, i.name AS invited_by
FROM customers c
LEFT JOIN customers i ON i.id = c.invited_by
ORDER BY c.id;

name|invited_by
Анна|NULL
Борис|NULL
Вера|Анна
Глеб|Вера

LEFT JOIN здесь нужен, чтобы не потерять Анну и Бориса: их никто не приглашал. Так же устроены запросы «сотрудник и его начальник» и «категория и родительская категория».

JOIN нескольких таблиц

JOIN можно ставить подряд несколько раз (SQLite допускает до 64 таблиц в одном соединении). Без скобок соединения выполняются слева направо: сначала таблица заказов с таблицей клиентов, затем результат с товарами.

SELECT o.id AS order_id, c.name, p.title, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id
ORDER BY o.id;

order_id|name|title|amount
101|Анна|Клавиатура|1500
102|Анна|Клавиатура|1500
103|Борис|Мышь|700
105|Вера|Мышь|700

Заказ 104 с монитором пропал: первый INNER JOIN не нашёл ему клиента. Если нужны все заказы, первое соединение делают LEFT JOIN customers. На столбцы, по которым соединяют большие таблицы, обычно ставят индексы: первичный ключ проиндексирован сам, а внешний ключ SQLite и PostgreSQL сами не индексируют.

Запятую и JOIN в одном FROM лучше не смешивать: в MySQL запятая по приоритету слабее JOIN, а в SQLite все соединения равны и выполняются слева направо. В MySQL запрос FROM t1, t2 JOIN t3 ON t1.i1 = t3.i3 завершится ошибкой: JOIN соединяет сначала t2 и t3, и условие ON не видит столбцов таблицы t1.

Условие в ON или в WHERE

Для INNER JOIN разницы нет. Для LEFT JOIN она большая: условие в ON проверяется до добавления строк с NULL, условие в WHERE после. Нужны все клиенты и их заказы дороже 1000.

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 1000
ORDER BY c.id;

name|order_id|amount
Анна|101|1500
Анна|102|1500
Борис|NULL|NULL
Вера|NULL|NULL
Глеб|NULL|NULL

Теперь то же условие в WHERE:

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 1000
ORDER BY c.id;

name|order_id|amount
Анна|101|1500
Анна|102|1500

У строк с NULL условие o.amount > 1000 не выполняется, и WHERE их выбрасывает. Если все строки левой таблицы нужны в результате, фильтр по правой таблице при LEFT JOIN пишите в ON.

Почему после JOIN строк стало больше

Круги Венна, которыми часто объясняют JOIN, рисуют пересечение множеств и не показывают главного: JOIN размножает строки. Каждая строка левой таблицы повторяется по числу пар справа, и агрегатные функции считают повторы.

Первая ловушка: COUNT(*) после LEFT JOIN считает и строку с NULL.

SELECT c.name, COUNT(*) AS wrong, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

name|wrong|orders
Анна|2|2
Борис|1|1
Вера|1|1
Глеб|1|0

У Глеба нет заказов, а COUNT(*) показывает один. COUNT(o.id) пропускает NULL и даёт верный ноль.

Вторая ловушка: две дочерние таблицы в одном запросе. Добавим таблицу отзывов reviews.

CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  stars INTEGER NOT NULL
);
INSERT INTO reviews VALUES (1, 1, 5), (2, 1, 4), (3, 2, 5);
SELECT c.name, SUM(o.amount) AS total, COUNT(r.id) AS reviews
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN reviews r ON r.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

name|total|reviews
Анна|6000|4
Борис|700|1

У Анны два заказа по 1500 и два отзыва, а запрос насчитал 6000 и четыре отзыва: каждый заказ соединился с каждым отзывом, 2 × 2 = 4 строки. Лечится агрегацией данных до соединения: сначала свернуть каждую дочернюю таблицу до одной строки на клиента, потом соединять.

SELECT c.name,
       COALESCE(o.total, 0) AS total,
       COALESCE(r.reviews, 0) AS reviews
FROM customers c
LEFT JOIN (SELECT customer_id, SUM(amount) AS total
           FROM orders GROUP BY customer_id) o ON o.customer_id = c.id
LEFT JOIN (SELECT customer_id, COUNT(*) AS reviews
           FROM reviews GROUP BY customer_id) r ON r.customer_id = c.id
ORDER BY c.id;

name|total|reviews
Анна|3000|2
Борис|700|1
Вера|700|0
Глеб|0|0

Здесь заодно все клиенты на месте: подзапросы присоединены через LEFT JOIN, а COALESCE заменяет NULL нулём у тех, у кого нет заказов или отзывов.

Чем отличаются JOIN в PostgreSQL, MySQL и SQLite

INNER, LEFT, RIGHT и CROSS JOIN есть во всех трёх СУБД. FULL JOIN в MySQL нет совсем, а JOIN без условия и CROSS JOIN с ON каждая база данных понимает по-своему, и на этом запрос может сломаться при переносе. Таблица для PostgreSQL 18, MySQL 9.7 и SQLite 3.39+.

NATURAL JOIN соединяет по всем столбцам с одинаковыми названиями. Мы бы его не использовали: добавите в обе таблицы столбец updated_at, и запрос молча начнёт сравнивать ещё и его.

Частые вопросы

Чем INNER JOIN отличается от LEFT JOIN?

INNER JOIN оставляет только строки, у которых нашлась пара. LEFT JOIN оставляет ещё и строки левой таблицы без пары, с NULL в столбцах правой. В нашем примере INNER JOIN вернул четыре строки без Глеба, LEFT JOIN пять, с Глебом.

JOIN и INNER JOIN это одно и то же?

Да. Слово INNER необязательно, просто JOIN выполняет внутреннее соединение во всех трёх СУБД из таблицы выше.

Чем JOIN отличается от UNION?

JOIN добавляет столбцы: строка результата собирается из данных двух таблиц. UNION добавляет строки: результаты двух SELECT с одинаковым числом столбцов ставятся друг под другом. В MySQL FULL JOIN нет, поэтому его собирают из двух JOIN через UNION ALL.

Чем ON отличается от USING?

USING (столбец) годится, когда столбец связи в обеих таблицах называется одинаково, и оставляет его в результате один раз:

SELECT * FROM orders JOIN reviews USING (customer_id) WHERE customer_id = 2;

id|customer_id|product_id|amount|id|stars
103|2|2|700|3|5

Столбец customer_id в выводе один, а id два: по нему таблицы не соединялись, и каждая принесла свой. ON пишут во всех остальных случаях: имена разные (o.customer_id = c.id) или условие сложнее равенства.

Можно ли соединять таблицы по нескольким столбцам?

Да: ON a.x = b.x AND a.y = b.y или USING (x, y), если столбцы называются одинаково. Так соединяют таблицы с составным ключом, например остатки по паре «склад и товар».

Почему не соединяются строки, где ключ равен NULL?

NULL = NULL возвращает NULL, и условие соединения не выполняется:

SELECT NULL = NULL AS eq, NULL IS NULL AS is_null;

eq|is_null
NULL|1

Строку с NULL в ключе вернут только внешние соединения, как вернули заказ 104 RIGHT и FULL JOIN.