Menu

Złączenia w PostgreSQL: INNER, LEFT, FULL, CROSS i LATERAL

Złączenie łączy wiersze z dwóch tabel według warunku. INNER JOIN zostawia dopasowania, LEFT JOIN każdy wiersz pierwszej tabeli, FULL JOIN obie strony, CROSS JOIN łączy wszystko ze wszystkim, a LATERAL uruchamia podzapytanie dla każdego wiersza.

Na tej stronie są działające edytory: edytuj, uruchamiaj i od razu zobacz wynik.

Złączenie buduje wiersze z dwóch tabel dopasowanych według warunku: SELECT ... FROM a JOIN b ON a.id = b.a_id. JOIN (INNER JOIN) zostawia tylko pasujące wiersze, LEFT JOIN każdy wiersz pierwszej tabeli, a FULL JOIN każdy wiersz obu tabel.

Wracają trzy wiersze. Grace nie ma zamówień, a zamówienie 13 należy do klienta, który nie istnieje, więc oba zostają pominięte. Złączenia poniżej używają tych samych dwóch tabel.

Rodzaje złączeń

ZłączenieZwracane wiersze
INNER JOINPary spełniające warunek
LEFT JOINKażdy lewy wiersz; prawe kolumny NULL, gdy nic nie pasuje
RIGHT JOINKażdy prawy wiersz; lustrzane odbicie LEFT JOIN
FULL JOINKażdy wiersz obu stron, a luki wypełnia NULL
CROSS JOINKażdy lewy wiersz w parze z każdym prawym

LEFT JOIN: zachowanie wierszy bez dopasowania

Grace pojawia się z 0 zamówień. Licz o.id, a nie *: count(*) policzyłoby wiersz Grace pełen NULL jako 1.

  • Wiersze bez dopasowania (anti-join): dodaj WHERE o.id IS NULL, żeby zostawić tylko klientów bez zamówień. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) robi to samo i jasno wyraża zamiar.
  • ON vs WHERE: warunek na prawej tabeli należy do ON. LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 20 gubi Grace, bo jej suma NULL nie przechodzi przez WHERE. Złączenie zmienia się wtedy w wewnętrzne. Napisz ON o.customer_id = c.id AND o.total > 20, żeby zachować każdego klienta.

FULL JOIN: wiersze brakujące po którejkolwiek stronie

FULL JOIN znajduje niedopasowania w obu kierunkach, co pomaga przy porównywaniu dwóch list:

W wyniku jest Grace bez zamówienia i zamówienie 13 bez klienta. Klucz obcy na orders.customer_id nie dopuściłby do drugiego wiersza.

CROSS JOIN: każda kombinacja

CROSS JOIN nie ma warunku. Łączy każdy wiersz jednej strony z każdym wierszem drugiej, więc 3 rozmiary i 2 kolory dają 6 wierszy:

Obie listy to tutaj listy VALUES nazwane jak tabele. Z prawdziwymi tabelami zapis to FROM sizes CROSS JOIN colors, a FROM sizes, colors robi to samo. Złączenie, którego warunek ON jest zawsze prawdziwy, mnoży wiersze tak samo, co często tłumaczy, dlaczego zapytanie nagle zwraca o wiele za dużo.

LATERAL: podzapytanie dla każdego wiersza

Podzapytanie w FROM zwykle nie widzi innych tabel z tego samego FROM. Z LATERAL widzi je i jest obliczane raz dla każdego wiersza tabeli przed nim. To rozwiązuje problem „top N w grupie”:

Ada dostaje dwa największe zamówienia, Linus swoje jedno, a Grace wiersz pełen NULL dzięki LEFT JOIN ... ON true. CROSS JOIN LATERAL pominąłby klientów bez zamówień. Z indeksem na orders (customer_id, total) każde podzapytanie czyta tylko kilka wpisów indeksu. Dla jednego wiersza na grupę często krótsze jest DISTINCT ON.

Najczęściej zadawane pytania

Czy JOIN to w PostgreSQL to samo co INNER JOIN?

Tak. Samo JOIN oznacza INNER JOIN, a LEFT JOIN oznacza LEFT OUTER JOIN; słowa INNER i OUTER są opcjonalne.

Dlaczego mój LEFT JOIN zwraca więcej wierszy niż lewa tabela?

Lewy wiersz pojawia się raz dla każdego pasującego prawego wiersza. Klient z trzema zamówieniami pojawi się trzy razy. Agreguj przez GROUP BY albo złącz z podzapytaniem, które ma już jeden wiersz na klucz.

Czym różni się ON od USING?

ON przyjmuje dowolny warunek. USING (id) to skrót dla równych kolumn o tej samej nazwie w obu tabelach, a wynik ma jedną kolumnę id zamiast dwóch.

Czy kolejność złączeń wpływa na wydajność w PostgreSQL?

Zwykle nie. Planer zmienia kolejność złączeń wewnętrznych, żeby znaleźć tani plan, gdy zapytanie ma do join_collapse_limit (domyślnie 8) tabel. Złączenia zewnętrzne można przestawiać tylko w niektórych przypadkach, bo ich przesunięcie może zmienić wynik.

Ilustracja języków programowania w Coddy

Ucz się programowania z Coddy

Zacznij