LEFT JOIN w SQLite: zachowaj każdy wiersz z lewej tabeli

LEFT JOIN zachowuje wszystko po lewej stronie

INNER JOIN zwraca tylko wiersze, w których obie strony są dopasowane. Często o to właśnie chodzi, ale nie zawsze. Czasem „brak dopasowania” jest dokładnie tą odpowiedzią, której szukasz: użytkownicy, którzy nie złożyli zamówienia, produkty, które nigdy się nie sprzedały, posty bez komentarzy. Do tego potrzebujesz LEFT JOIN.

LEFT JOIN zwraca każdy wiersz z lewej tabeli. Jeśli prawa tabela ma pasujący wiersz, dostajesz dopasowane kolumny. Jeśli nie, lewy wiersz i tak się pojawia, a kolumny z prawej strony wracają jako NULL.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

Cleo nie ma zamówień, ale i tak się pojawia, z NULL w kolumnie total. Zamień LEFT JOIN na INNER JOIN, a Cleo całkowicie zniknie.

Model myślowy

Czytaj zapytanie od góry do dołu i traktuj lewą tabelę jako kotwicę. Każdy wiersz z users pojawi się w wyniku bez względu na wszystko. LEFT JOIN pyta potem dla każdego użytkownika: „czy w orders jest pasujący wiersz?”

• Znaleziono dopasowanie → doklej dopasowane kolumny do wiersza użytkownika.
• Wiele dopasowań → utwórz jeden wiersz wyniku na każde dopasowanie (Ada ma dwa zamówienia, więc pojawia się dwa razy).
• Brak dopasowania → utwórz jeden wiersz z NULL w każdej kolumnie z prawej tabeli.

Ten ostatni przypadek to cały powód istnienia LEFT JOIN. NULL nie znaczy tu „nie wiemy”, tylko „po prawej stronie nie ma czego dokleić”.

LEFT OUTER JOIN to ta sama operacja. W SQLite słowo kluczowe OUTER jest opcjonalne i większość osób je pomija.

Szukanie wierszy bez dopasowania

Klasyczne zastosowanie LEFT JOIN: znaleźć wiersze z lewej tabeli, które nie mają dopasowania po prawej. Sztuczka polega na filtrowaniu po kolumnie z prawej tabeli, która w prawdziwych danych jest NOT NULL (zwykle po jej kluczu głównym), i sprawdzeniu po złączeniu, czy jest NULL:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

Wraca tylko Cleo. Złączenie dokleja dane zamówień tam, gdzie istnieją, a WHERE o.id IS NULL zostawia tylko wiersze, w których doklejenie się nie udało. Czasem nazywa się to „antyzłączeniem”.

ON a WHERE: subtelna pułapka

To najczęstszy błąd przy LEFT JOIN i warto się przy nim zatrzymać. Warunki trafiają albo do klauzuli ON, albo do klauzuli WHERE, ale przy złączeniach zewnętrznych zachowują się zupełnie inaczej.

• ON działa w trakcie złączenia. Warunki w nim decydują, które wiersze z prawej strony liczą się jako dopasowanie.
• WHERE działa po tym, jak złączenie wygenerowało wiersze. Filtruje połączony wynik.

Zobacz, co się dzieje, gdy warunek na prawej tabeli trafi do WHERE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- Źle: po cichu zamienia LEFT JOIN w INNER JOIN
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

Cleo nie ma zamówienia, więc w jej wierszu o.status to NULL, a NULL = 'shipped' nie jest prawdą: zostaje odfiltrowana. Status Borisa to 'pending', więc on też odpada. LEFT JOIN po cichu zachował się jak INNER JOIN.

Rozwiązanie: przenieś warunek do ON, żeby filtrował dopasowania, a nie wiersze wyniku:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

Teraz pojawia się każdy użytkownik. Ada dostaje swoje wysłane zamówienie, Boris dostaje NULL (jego oczekujące zamówienie nie zakwalifikowało się jako dopasowanie), a Cleo dostaje NULL (nie ma żadnych zamówień). To właściwa odpowiedź na pytanie „pokaż każdego użytkownika i jego wysłane zamówienia, jeśli jakieś są”.

Praktyczna zasada: warunki na lewej tabeli mogą trafić do WHERE. Warunki na prawej tabeli prawie zawsze należą do ON, chyba że celowo szukasz niedopasowanych wierszy przez IS NULL.

Liczenie z LEFT JOIN

Częste zadanie: policzyć powiązane wiersze dla każdego rodzica, łącznie z rodzicami, którzy mają ich zero. INNER JOIN odrzuciłby zera. LEFT JOIN z COUNT po kolumnie z prawej strony daje poprawną odpowiedź:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

Dwie rzeczy warte uwagi:

• COUNT(o.id) liczy niepuste wiersze z prawej strony. Cleo dostaje 0, a nie 1, bo COUNT ignoruje NULL. Gdyby napisać COUNT(*), Cleo dostałaby 1 (wiersz istnieje, tylko ma w sobie NULL). Prawie zawsze potrzebujesz COUNT(right.id).
• COALESCE(SUM(o.total), 0) zamienia sumę Cleo równą NULL na 0. Bez tego jej przychód wyświetlałby się jako NULL, co jest technicznie poprawne, ale brzydko wygląda.

Łączenie wielu tabel

LEFT JOIN można łączyć w łańcuch. Każde złączenie bierze bieżący wynik i dołącza do niego kolejną tabelę. Gdy już przez LEFT JOIN kolumna może być pusta, używaj dalej LEFT JOIN dla wszystkich tabel, które od niej zależą. W przeciwnym razie kolejny INNER JOIN po cichu odrzuci wiersze, które chcesz zachować.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

Wraca trzech użytkowników. Ada ma zamówienie i przesyłkę. Boris ma zamówienie, ale bez przesyłki (carrier to NULL). Cleo nie ma zamówienia, więc zarówno o.total, jak i s.carrier to NULL. Łańcuch LEFT JOIN zachowuje każdego użytkownika bez względu na to, na którym etapie łańcucha relacji kończą się dane.

Kiedy LEFT JOIN to właściwy wybór

Sięgaj po LEFT JOIN, gdy pytanie w gruncie rzeczy dotyczy lewej tabeli, a prawa tabela to informacje uzupełniające. Sformułowania w rodzaju „każdy użytkownik z zamówieniami, jeśli jakieś ma” albo „wszystkie produkty z ich najnowszą recenzją” przekładają się wprost na LEFT JOIN.

Sięgaj po INNER JOIN, gdy obie strony są równie potrzebne. „Zamówienia z danymi użytkownika” nie mają sensu dla zamówienia bez użytkownika, więc filtrowanie złączenia wewnętrznego jest tym, czego chcesz.

Jeśli piszesz LEFT JOIN ... WHERE right.col IS NOT NULL, tak naprawdę chcesz INNER JOIN. Jeśli piszesz LEFT JOIN ... WHERE right.col IS NULL, chcesz antyzłączenia i robisz to dobrze.

Dalej: self-join

Czasem tabela, z którą chcesz złączyć dane, jest tą samą tabelą, którą już odpytujesz: pracownicy i ich przełożeni, kategorie i ich kategorie nadrzędne, pary użytkowników z tego samego miasta. To self-join, czyli temat następnej strony.