Podzapytania, EXISTS i pułapka NOT IN

Zapytanie jako część innego zapytania

Podzapytanie pozwala użyć wyniku jednego SELECT w drugim. Może dostarczyć pojedynczą wartość, listę wartości albo odpowiedzieć, czy istnieje pasujący rekord. Wybór operatora musi pasować do kształtu wyniku.

SELECT nazwa, cena FROM produkty
WHERE cena > (SELECT AVG(cena) FROM produkty)
ORDER BY cena DESC, id;

Wewnętrzny SELECT oblicza średnią 251.25, a zewnętrzny wybiera produkty droższe od niej. Po resecie wynikiem jest Monitor 800. Nie trzeba wpisywać średniej ręcznie; zapytanie uwzględni zmianę danych.

IN z wynikiem SELECT

SELECT id, imie FROM klienci
WHERE id IN (SELECT klient_id FROM zamowienia WHERE status = 'nowe')
ORDER BY id;

Lista identyfikatorów to 2 i 3, więc dostajesz Jana i O’Neila. Gdy wewnętrzny wynik ma powtórzenia, IN nadal sprawdza przynależność; nie powiela klienta. Nie zastępuj IN znakiem =, jeśli podzapytanie może zwrócić kilka wierszy.

Podzapytanie skorelowane i EXISTS

SELECT k.id, k.imie FROM klienci k
WHERE EXISTS (
  SELECT 1 FROM zamowienia z
  WHERE z.klient_id = k.id
)
ORDER BY k.id;

Warunek wewnętrzny odwołuje się do k.id z zapytania zewnętrznego. Dla każdego klienta pytamy o jego zamówienie. Wynik: Ala, Jan, O’Neil. SELECT 1 jest konwencją: EXISTS sprawdza obecność wiersza, nie jego wartość. W zwykłym podzapytaniu EXISTS także wiersz zawierający NULL oznacza „istnieje”.

Logicznie można wyobrażać sobie sprawdzenie dla kolejnych klientów, ale rzeczywisty serwer może zoptymalizować je do innego planu. Nie zakładaj, że każde podzapytanie będzie wolniejsze od JOIN. JOIN zwraca dopasowane pary; EXISTS odpowiada tylko na pytanie o istnienie i nie powiela klienta przez liczbę jego zamówień.

NOT EXISTS i pułapka NOT IN

SELECT k.id, k.imie FROM klienci k
WHERE NOT EXISTS (
  SELECT 1 FROM zamowienia z WHERE z.klient_id = k.id
)
ORDER BY k.id;

Otrzymasz Ewę i Adama. Jeśli brak powiązania jest celem, ten zapis jest czytelny i nie zależy od wystąpienia NULL w obcej kolumnie w taki sposób jak NOT IN.

SELECT id, imie FROM klienci WHERE id NOT IN (1, NULL);

To zapytanie nie zwraca naszych klientów. Dla id 1 negowane dopasowanie daje FALSE; dla pozostałych nie można ustalić, czy różnią się od nieznanej wartości, więc wynik jest UNKNOWN. WHERE zachowuje tylko TRUE. Gdy używasz NOT IN z podzapytaniem, sprawdź zarówno NULL wewnątrz niego, jak i NULL po lewej stronie porównania.

W naszej tabeli zamowienia klient_id ma NOT NULL, więc id NOT IN (SELECT klient_id FROM zamowienia) działa zgodnie z zamiarem. Po zmianie schematu lub użyciu innej kolumny takie założenie może przestać być prawdziwe. Filtrowanie WHERE klient_id IS NOT NULL wewnątrz podzapytania usuwa ten konkretny problem, lecz nadal trzeba określić oczekiwane zachowanie wartości zewnętrznej.

Ten sam problem w różnych bazach

Pokazane SELECT z AVG, IN, EXISTS i NOT EXISTS używają podstawowej składni wspólnej dla MySQL/MariaDB, SQL Server, Oracle 19c i Access. Aliasy tabel bez AS pasują także do Oracle. Access obsługuje podzapytania w warunkach, ale nie każdy serwerowy zapis z tabelą pochodną w FROM jest przenośny; do bardziej złożonych etapów użyj zapisanych kwerend.

Ważny wyjątek edytora: SQLite przy wielowierszowym podzapytaniu skalarnym może wykorzystać pierwszy wiersz. MySQL, SQL Server i Oracle zwykle zgłoszą błąd „więcej niż jeden wiersz”. Tryb porównawczy edytora nie dowodzi spełnienia ograniczenia jednego wiersza. W tej lekcji podzapytania skalarne używają agregacji bez GROUP BY, więc świadomie zapewniamy pojedynczą wartość. Nie wykorzystuj przypadkowego wyboru SQLite jako poprawnego rozwiązania serwerowego.

Podzapytanie czy HAVING?

HAVING filtruje grupy utworzone w bieżącym SELECT. Podzapytanie pozwala użyć osobnego zestawienia jako progu lub listy. Nie zapisuj MAX(SUM(…)) w jednej płaskiej liście SELECT, aby szukać największej sumy grup: najpierw utwórz zestawienie grup, a potem podsumuj je w osobnym poziomie lub zapisanej kwerendzie. To pomaga kontrolować, co oznacza każdy wiersz.

Ćwiczenia

1. Pokaż wszystkie produkty o minimalnej cenie, zachowując ewentualne remisy.
2. Pokaż klientów mających przynajmniej jedno opłacone zamówienie.
3. Pokaż produkty, które nie występują w żadnej pozycji zamówienia.

SELECT id, nazwa, cena FROM produkty
WHERE cena = (SELECT MIN(cena) FROM produkty) ORDER BY id;
SELECT k.imie FROM klienci k WHERE EXISTS (
 SELECT 1 FROM zamowienia z
 WHERE z.klient_id = k.id AND z.status = 'oplacone'
) ORDER BY k.id;
SELECT pr.id, pr.nazwa FROM produkty pr WHERE NOT EXISTS (
 SELECT 1 FROM pozycje p WHERE p.produkt_id = pr.id
) ORDER BY pr.id;

Źródła: MySQL — podzapytania, SQL Server EXISTS, IN i NULL, Access — podzapytania, SQLite — semantyka podzapytań. INF.03.4: zapytania i podzapytania. Dalej: UNION i widoki.