Podzapytania SQL: jedna wartość, IN, EXISTS i tabela w FROM

Podzapytanie przydaje się wtedy, gdy odpowiedź na jedno pytanie staje się wejściem do następnego. Najpierw potrzebuję średniej ceny, a potem listy produktów droższych od tej średniej. Albo najpierw ustalam, którzy klienci mają zamówienia, a dopiero później wybieram ich dane. W SQL mogę zapisać takie zależności w jednej instrukcji.

Ja zaczynam od kształtu potrzebnego wyniku: jedna wartość, lista wartości, tabela czy odpowiedź na pytanie o istnienie. To pomaga wybrać właściwą konstrukcję i przewidzieć błędy. Pokażę różnice na danych syntetycznych w PostgreSQL 18. Wszystkie kwoty są umownymi jednostkami, a małe wyniki da się wyprowadzić bez komputera.

Czym jest podzapytanie

Podzapytanie to SELECT użyty wewnątrz innego zapytania. Może dostarczać wartość do porównania, zbiór do sprawdzenia przynależności albo źródło w FROM. Nawiasy wskazują jego granice, ale samo zagnieżdżenie nie określa jeszcze, jakiego wyniku oczekuje otoczenie.

Właśnie dlatego pytam najpierw o kontrakt. Zapis price > (...) oczekuje pojedynczej wartości. Zapis customer_id IN (...) porównuje identyfikator ze zbiorem. FROM potrzebuje wyniku tabelarycznego. EXISTS sprawdza obecność wiersza. Te konstrukcje mają różne reguły dla pustych wyników i NULL.

Nie uczę reguły „baza zawsze wykonuje najpierw wnętrze”. Tak można czasem objaśnić zależność logiczną, ale nie wolno z niej wyprowadzać fizycznego planu. PostgreSQL może przekształcić zapytanie albo zakończyć sprawdzanie wcześniej. Szczególnie przy EXISTS dokumentacja ostrzega przed zakładaniem pełnego wykonania podzapytania.

Dane do pierwszego przykładu

Źródło products ma cztery produkty. Ceny trzech są znane, przy czwartym zapisano NULL. W tym przykładzie NULL oznacza brak informacji o cenie.

Poniższy fragment buduje dane i wybiera produkty droższe od średniej. WITH nadaje nazwę źródłu z VALUES. Nazwa obowiązuje w obrębie tej jednej instrukcji; w kolejnych przykładach zachowaj odpowiednie WITH i podmień końcowe zapytanie.

WITH products(product_id, product_name, price) AS (
    VALUES
        (1, 'Notebook', 10),
        (2, 'Mug', 20),
        (3, 'Lamp', 30),
        (4, 'Poster', NULL)
)
SELECT product_id, product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY product_id;

Dokumentacja VALUES opisuje podawanie małej tabeli bez zapisywania jej na dysku. Takie źródło pozwala prześledzić przykład od początku do końca. Nie zakładam dodatkowych produktów, ukrytych filtrów ani cen spoza pokazanej listy.

Podzapytanie skalarne: jedna wartość

Średnia z dziesięciu, dwudziestu i trzydziestu wynosi dwadzieścia. AVG pomija NULL, więc plakat nie zwiększa mianownika. Warunek przepuszcza tylko lampę z ceną trzydzieści. Kubek jest równy średniej, a operator wymaga wartości ściśle większej od średniej. Reguły AVG opisuje dokumentacja agregacji.

Podzapytanie użyte jako wartość musi zwracać jedną kolumnę i najwyżej jeden wiersz. Jeden wiersz dostarcza jego wartość. Brak wierszy daje NULL. Więcej niż jeden wiersz oznacza błąd, nawet gdy wszystkie zwrócone wartości wyglądają tak samo. Takie zachowanie określa dokumentacja podzapytań skalarnych.

W naszym przykładzie AVG bez GROUP BY zwraca jeden wiersz agregatu. Gdy nie ma znanych cen, wartością będzie NULL. Porównanie ceny z NULL nie daje prawdy, więc WHERE niczego nie przepuści. Wynik pusty oznacza tu brak produktów spełniających warunek, a nie dowód, że średnia wynosi zero.

Dlaczego zwykły SELECT może dać błąd

Wyobraź sobie porównanie price > (SELECT price FROM products). W źródle są cztery wiersze, więc podzapytanie nie spełnia kontraktu pojedynczej wartości. Baza nie wybierze samodzielnie jednej ceny. Potrzebujesz zdecydować, czy chodzi o średnią, maksimum, minimum czy konkretny produkt.

Nie naprawiam tego przypadkowym LIMIT 1. Taki zapis ogranicza liczbę wierszy, ale bez reguły wyboru nie określa właściwej wartości. Nawet ORDER BY musi odpowiadać pytaniu. Najwyższa cena i cena ostatnio dodanego produktu to dwie różne rzeczy.

Gdy potrzebujesz rekordu wybranego według kolejności, podaj stabilne kryterium i rozstrzygnięcie remisów. Jeżeli dwie aktualizacje mają tę samą datę, sam znacznik czasu może nie wystarczyć. Dobór dodatkowego klucza jest częścią definicji wyniku. Dokumentacja LIMIT wyjaśnia ryzyko nieokreślonego podzbioru.

Drugi zbiór: klienci i zamówienia

Do dalszych przykładów użyję osobnego zbioru. customers zawiera Adę, Bena i Celinę, o identyfikatorach odpowiednio jeden, dwa i trzy. orders ma cztery zamówienia. Ostatnie ma nieznanego klienta; celowo zostawiam ten brak, żeby pokazać pułapkę NOT IN.

Oto źródła dla kolejnych zapytań. Końcowy SELECT tylko pokazuje zamówienia; zastępuj go przykładami z dalszych sekcji.

WITH customers(customer_id, customer_name) AS (
    VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Celina')
), orders(order_id, customer_id, amount) AS (
    VALUES
        (101, 1, 100),
        (102, 1, 50),
        (103, 2, 80),
        (104, NULL, 20)
)
SELECT order_id, customer_id, amount
FROM orders
ORDER BY order_id;

Każdy klient występuje raz. Każde zamówienie też występuje raz, ale klient może mieć wiele zamówień. Ta asymetria będzie istotna, gdy porównamy podzapytanie z połączeniem. Nie utożsamiam liczby zamówień z liczbą osób, które je złożyły.

IN: czy identyfikator należy do zbioru

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders)
ORDER BY customer_id;

Wynik zawiera Adę i Bena. Identyfikator Ady pojawia się w zamówieniach dwa razy, ale IN nie powiela z tego powodu jej wiersza. Sprawdza przynależność. W tym przykładzie lewa strona jest pojedynczym identyfikatorem, więc podzapytanie musi zwrócić jedną kolumnę.

NULL w zbiorze nie przeszkadza potwierdzić znanego dopasowania Ady i Bena. Dla Celiny nie ma równego identyfikatora, ale jest brak. Wynik warunku jest więc nieznany, a WHERE go odrzuca. Dokumentacja IN opisuje te reguły.

Nie dodaję DISTINCT do podzapytania wyłącznie z obawy przed powieleniem klientów. IN nie tworzy par dla każdego wystąpienia. DISTINCT może być przydatny w innych zadaniach, ale tutaj nie zmieni odpowiedzi na pytanie o znaną przynależność.

NOT IN i NULL: dlaczego znika Celina

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders)
ORDER BY customer_id;

To zapytanie nie zwróci żadnego klienta. Ada i Ben mają znane dopasowania, więc NOT IN daje dla nich fałsz. Dla Celiny wynik jest nieznany z powodu NULL w podzapytaniu. WHERE przepuszcza wyłącznie prawdę. Celina znika mimo braku zamówienia z jej identyfikatorem.

Możesz odfiltrować brakujące klucze wewnątrz podzapytania przez WHERE customer_id IS NOT NULL. Dla naszej niepustej listy identyfikatorów klientów zostanie wtedy Celina. Nadal musisz jednak rozumieć reguły dla NULL po zewnętrznej stronie. Nie przedstawiam NOT IN jako uniwersalnej konstrukcji odpornej na braki.

Dział NOT IN w dokumentacji opisuje tę pułapkę wprost. Przy pytaniu o brak powiązania zwykle wolę NOT EXISTS z jawnym warunkiem korelacji. Łatwiej przeczytać, czego konkretnie szukam.

EXISTS i NOT EXISTS: istnienie dopasowania

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

Wynikiem jest Celina. Dla każdego klienta pytam, czy istnieje zamówienie z równym identyfikatorem. NULL w zamówieniu 104 nie pasuje do żadnego z trzech znanych identyfikatorów. Nie blokuje znalezienia Celiny tak, jak zrobił to NOT IN.

EXISTS bez NOT dałby Adę i Bena. Liczy się obecność wiersza, a nie wartość jedynki w SELECT. W tych prostych przypadkach nawet SELECT NULL nadal oznaczałby istnienie, jeśli warunek znalazł wiersz. Jedynka jest czytelną konwencją, nie licznikiem dopasowań.

Takie podzapytanie jest skorelowane, bo odwołuje się do c.customer_id z zewnętrznego poziomu. To poprawne i udokumentowane odwołanie. Nie obowiązuje ogólny zakaz korzystania z kolumn zapytania zewnętrznego. Trzeba natomiast pilnować zakresu nazw i rodzaju konstrukcji, w której podzapytanie występuje.

Skorelowany licznik w SELECT

SELECT
    c.customer_id,
    c.customer_name,
    (
        SELECT COUNT(*)
        FROM orders AS o
        WHERE o.customer_id = c.customer_id
    ) AS order_count
FROM customers AS c
ORDER BY c.customer_id;

Ada otrzyma dwa, Ben jeden, Celina zero. COUNT bez GROUP BY daje pojedynczy wiersz także dla pustego zbioru dopasowań. To ważna różnica względem zwykłego SELECT zwracającego zero wierszy, którego skalarna wartość byłaby NULL.

Porównaj ten wariant z COUNT po LEFT JOIN. Tutaj COUNT(*) pracuje wewnątrz źródła zamówień przefiltrowanego do klienta. Nie liczy pustego wiersza dostarczonego przez złączenie zewnętrzne. Ten sam zapis funkcji może więc wymagać innej interpretacji zależnie od tego, jakie wiersze trafiają na wejście.

Czytam aliasy bardzo uważnie. Warunek o.customer_id = c.customer_id łączy dwa poziomy. Zapis z obiema kolumnami wskazującymi wyłącznie na orders odpowiadałby na inne pytanie. Jawne prefiksy chronią przed przypadkową utratą korelacji podczas kopiowania fragmentu.

Podzapytanie w FROM buduje kolejny poziom

Chcę znaleźć klientów z co najmniej dwoma zamówieniami i pokazać ich sumę. Najpierw buduję podsumowanie po kliencie, a potem filtruję jego wynik.

SELECT s.customer_id, s.order_count, s.total_amount
FROM (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS s
WHERE s.order_count >= 2
ORDER BY s.customer_id;

Wynik ma jeden wiersz: klient jeden, dwa zamówienia i kwota 150. Podzapytanie tworzy też grupę klienta dwa oraz grupę z NULL dla zamówienia 104, ale ich liczniki wynoszą jeden. Filtr zewnętrzny ich nie przepuszcza.

Alias s nadaje nazwę wyprowadzonej tabeli. Kolumny order_count i total_amount są dostępne na zewnętrznym poziomie. Nie można natomiast używać tam dowolnych kolumn ukrytych wewnątrz, których podzapytanie nie zwróciło. Taką postać źródła opisuje dokumentacja podzapytań w FROM.

To samo kryterium liczności można wyrazić przez HAVING na poziomie grupowania. Podzapytanie wybieram tu po to, żeby pokazać osobno dwa etapy i dostępność aliasów. Więcej poziomów zagnieżdżenia nie oznacza automatycznie lepszego zapytania.

Średnia zamówienia wymaga właściwego poziomu danych

Stary zapis z podzapytaniem może wyglądać wiarygodnie, a mimo to liczyć inną miarę niż oczekiwana. Jeśli zamówienie ma wiele pozycji, najpierw trzeba otrzymać jeden wiersz na zamówienie. Dopiero potem średnia kwoty zamówienia opisuje przeciętny zakup. Średnia cen pozycji odpowiada na inne pytanie.

W naszym małym źródle każdy wiersz jest już zamówieniem. Mogę więc obliczyć średnią kwotę osobno dla znanych klientów. Poniższy fragment wyklucza zamówienie z nieznanym klientem, żeby zakres raportu był jawny.

SELECT customer_id, AVG(amount) AS average_order_amount
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY customer_id;

Ada ma kwoty sto i pięćdziesiąt, więc jej średnia wynosi siedemdziesiąt pięć. Ben ma jedno zamówienie o wartości osiemdziesiąt, więc jego średnia wynosi osiemdziesiąt. Celina nie pojawi się w tym podsumowaniu, ponieważ nie ma zamówień. Jeśli ma zostać, trzeba dołączyć podsumowanie do listy klientów.

Gdybyś następnie policzył średnią z tych dwóch średnich, każdy klient dostałby taką samą wagę. Przy liczeniu średniej wszystkich zamówień Ada wnosi dwa zdarzenia, a Ben jedno. Obie miary mogą mieć sens, ale wymagają różnych nazw. Dodatkowy poziom podzapytania nie rozstrzyga, którą z nich chcesz uzyskać.

Dlatego przy każdym etapie zapisuję znaczenie wiersza. Wewnątrz może to być pozycja, potem zamówienie, następnie klient. Jeśli pomylę te poziomy, uzyskam poprawną liczbę dla źle nazwanego wskaźnika. Mały przykład z różną liczbą zamówień na klienta szybko ujawnia tę różnicę.

Pusty wynik i agregat to dwa różne przypadki

Porównaj dwa skalarne podzapytania dla Celiny. Zwykły SELECT kwoty z warunkiem klienta trzy nie znajduje żadnego wiersza. Jako wartość skalarną daje NULL. SELECT COUNT(*) z tym samym filtrem nadal daje jeden wiersz agregatu, zawierający zero.

Jeżeli do COUNT dołożysz GROUP BY po identyfikatorze klienta, dla klienta bez zamówień nie powstanie grupa. Zniknie więc także wiersz z zerem. To nie sprzeczność w funkcji COUNT, lecz zmiana sposobu budowania wyniku. Przy korelacji warto sprawdzić, czy grupowanie rzeczywiście jest potrzebne.

Podobnie MAX lub AVG bez grupowania mogą dać jeden wiersz z NULL. Nie oznacza to tego samego co pusty wynik EXISTS: EXISTS sprawdza wiersze, więc pojedynczy wiersz agregatu wystarczy do prawdy. W warunku istnienia zwykle pytam bezpośrednio o przefiltrowane rekordy, zamiast umieszczać tam agregację bez świadomego powodu.

Tę różnicę łatwo przeoczyć podczas upraszczania zapytania. Jeśli przepisujesz fragment z licznika na EXISTS, sprawdź, czy wnętrze nadal zwraca wiersze tylko przy rzeczywistym dopasowaniu. Zachowanie dla klienta bez zamówień jest tu szczególnie dobrym punktem kontrolnym.

Zakres nazw i LATERAL

Odwołanie do zewnętrznej kolumny w EXISTS lub skalarnym SELECT jest zwykłą korelacją. Inna sytuacja występuje, gdy podzapytanie w FROM chce korzystać z wcześniejszego źródła tego samego FROM. W PostgreSQL do takich odwołań służy LATERAL. Dokumentacja LATERAL rozróżnia te przypadki.

Na początek wystarczy nie mieszać obu reguł. Nie dopisuj LATERAL do każdego podzapytania i nie zakładaj, że każda nazwa jest widoczna wszędzie. Sprawdź, który poziom wprowadza alias, jakie kolumny zwraca i gdzie chcesz ich użyć.

Dobre nazwy pomagają także przy dłuższym zapytaniu. Alias s wystarcza przy krótkim przykładzie, ale w większym raporcie nazwa customer_totals szybciej wyjaśni rolę źródła. Skracanie kodu nie powinno zmuszać następnej osoby do odgadywania znaczenia każdego fragmentu.

Podzapytanie, JOIN czy CTE

Gdy potrzebujesz kolumn z każdego dopasowania, INNER JOIN jest naturalnym wyborem. Gdy pytasz tylko o istnienie, EXISTS opisuje to bez tworzenia wszystkich par. Gdy potrzebujesz jednego podsumowania przed dołączeniem kolejnego zbioru, przyda się tabela wyprowadzona albo CTE.

CTE zapisane przez WITH pozwala nazwać etap wcześniej i odwołać się do niego w dalszej części instrukcji. W PostgreSQL samo użycie WITH nie jest uniwersalną obietnicą materializacji ani przyspieszenia. Szczegóły zależą od konstrukcji, liczby odwołań i opcji opisanych w dokumentacji WITH.

Nie wybieram między tymi zapisami na podstawie mitu, że JOIN zawsze wygrywa z podzapytaniem. Najpierw porównuję znaczenie wyników, zwłaszcza duplikaty i NULL. Dopiero później można oceniać plan przez EXPLAIN. Różny wynik nie jest uczciwym porównaniem wydajności.

Kolejność wyniku należy do zewnętrznego zapytania

Sortowanie wewnątrz podzapytania nie jest ogólną obietnicą kolejności końcowej tabeli. Jeśli odbiorca ma widzieć klientów według identyfikatora, wpisz ORDER BY na końcowym poziomie. W pokazanych przykładach robię to jawnie. Łatwiej wtedy porównać przewidywany wynik z prezentacją, a zmiana planu nie staje się przypadkiem zmianą umowy z odbiorcą.

Ćwiczenie: trzy pytania do tego samego źródła

Najpierw wypisz klientów z przynajmniej jednym zamówieniem. Oczekiwane osoby to Ada i Ben. Następnie wypisz klientów bez zamówień. Oczekiwanym wynikiem jest Celina, także gdy w źródle pozostaje zamówienie z nieznanym klientem. Na końcu dodaj licznik zamówień do wszystkich trzech klientów.

Przed zapisaniem każdego wariantu nazwij potrzebny kształt wyniku: warunek istnienia, brak istnienia lub pojedynczy licznik na klienta. Potem uzasadnij, dlaczego dwa zamówienia Ady nie powinny tworzyć dwóch wierszy listy klientów. Jeśli wybierasz JOIN, wyjaśnij dodatkowo, jak kontrolujesz tę liczność.

Sprawdź też w myślach puste źródło zamówień. EXISTS nikogo nie przepuści, NOT EXISTS przepuści wszystkich trzech klientów, a skorelowany COUNT da każdemu zero. Te przypadki pomagają zrozumieć konstrukcję bez zapamiętywania gotowych formułek. Dalej polecam LEFT JOIN i logiczną kolejność SQL.