Виды JOIN: INNER, LEFT, RIGHT, FULL, CROSS

INNER, LEFT, RIGHT, FULL, CROSS — какие бывают соединения и чем отличаются.

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

Наши данные

Будем соединять клиентов и заказы. Заметьте: у Веры нет заказов, а заказ с customer_id = 4 — «сирота» (такого клиента нет). Эти крайние случаи и показывают разницу между типами JOIN.

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

INNER JOIN оставляет только те строки, для которых нашлась пара в обеих таблицах. Вера (нет заказов) и заказ-сирота не попадут в результат.

CREATE TABLE customers (
    id   INTEGER PRIMARY KEY,
    name TEXT
);
INSERT INTO customers (id, name) VALUES
    (1, 'Аня'),
    (2, 'Борис'),
    (3, 'Вера');   -- у Веры заказов нет

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount      INTEGER
);
INSERT INTO orders (customer_id, amount) VALUES
    (1, 500),
    (1, 300),
    (2, 900),
    (4, 100);  -- заказ «сироты»: customer_id=4, такого клиента нет

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

Вывод:

Аня|300
Аня|500
Борис|900

Только Аня и Борис — те, у кого есть и клиент, и заказ. Веры нет (нет заказов), сироты-заказа нет (нет клиента).

LEFT JOIN — все из левой таблицы

LEFT JOIN берёт все строки левой таблицы (customers), а из правой подставляет совпадения. Если пары нет — на месте правых колонок будет NULL. Так Вера попадёт в результат с NULL вместо суммы.

CREATE TABLE customers (
    id   INTEGER PRIMARY KEY,
    name TEXT
);
INSERT INTO customers (id, name) VALUES
    (1, 'Аня'),
    (2, 'Борис'),
    (3, 'Вера');   -- у Веры заказов нет

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount      INTEGER
);
INSERT INTO orders (customer_id, amount) VALUES
    (1, 500),
    (1, 300),
    (2, 900),
    (4, 100);  -- заказ «сироты»: customer_id=4, такого клиента нет

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

Вывод:

Аня|300
Аня|500
Борис|900
Вера|

Вера осталась в выборке, но amount у неё пуст (NULL). Это типичный способ найти «клиентов без заказов».

RIGHT и FULL JOIN

RIGHT JOIN — зеркало LEFT: все строки правой таблицы. FULL JOIN (полное внешнее) — все строки обеих таблиц, с NULL там, где пары нет. Современный SQLite их поддерживает:

CREATE TABLE customers (
    id   INTEGER PRIMARY KEY,
    name TEXT
);
INSERT INTO customers (id, name) VALUES
    (1, 'Аня'),
    (2, 'Борис'),
    (3, 'Вера');   -- у Веры заказов нет

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount      INTEGER
);
INSERT INTO orders (customer_id, amount) VALUES
    (1, 500),
    (1, 300),
    (2, 900),
    (4, 100);  -- заказ «сироты»: customer_id=4, такого клиента нет

SELECT c.name, o.amount, o.customer_id
FROM customers c
FULL JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.amount;

Вывод:

|100|4
Аня|300|1
Аня|500|1
Борис|900|2
Вера||

В результате есть и Вера (клиент без заказа, amount = NULL), и заказ-сирота (name = NULL, но customer_id = 4). FULL JOIN не теряет никого с обеих сторон.

CROSS JOIN — декартово произведение

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

CREATE TABLE sizes (s TEXT);
INSERT INTO sizes (s) VALUES ('S'), ('M'), ('L');

CREATE TABLE colors (c TEXT);
INSERT INTO colors (c) VALUES ('красный'), ('синий');

SELECT s, c
FROM sizes
CROSS JOIN colors
ORDER BY s, c;

Вывод:

L|красный
L|синий
M|красный
M|синий
S|красный
S|синий

6 строк = 3 размера × 2 цвета. Осторожно с большими таблицами: CROSS JOIN растёт как произведение размеров.

Памятка по типам

Итог

• INNER теряет строки без пары; LEFT/RIGHT/FULL их сохраняют, подставляя NULL.
• LEFT JOIN + проверка на NULL — классический приём «найти строки без связанных».
• CROSS JOIN даёт все комбинации; следите за размером результата.