Odpowiedź na niektóre pytania wymaga dwóch kroków. „Które zamówienia są powyżej średniej?” wymaga najpierw znalezienia średniej, a następnie porównania z nią każdego zamówienia. Podzapytania SQL pozwalają zapisać oba kroki jako jedną instrukcję.

W tym samouczku omówiono każdy typ przy użyciu dwóch małych tabel. Uruchomiliśmy te zapytania w SQLite 3.50 i pokazane wyniki pochodzą z tego uruchomienia. Standardowa składnia podzapytań jest szeroko obsługiwana, a my wskazujemy miejsca, w których bazy danych się różnią.

Co to jest podzapytanie?

Podzapytanie, zwane także zapytaniem wewnętrznym lub zagnieżdżonym, to instrukcja SELECT umieszczona wewnątrz innej instrukcji, w nawiasach okrągłych. Koncepcyjnie zapytanie wewnętrzne generuje wynik, który następnie wykorzystuje zapytanie zewnętrzne.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Przykładowe tabele

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Pamiętaj, że Dev nie ma żadnych rozkazów. Ten szczegół ma znaczenie w przypadku kilku przykładów poniżej.

Rodzaje podzapytań w skrócie

Podzapytania skalarne

Podzapytanie skalarne zwraca dokładnie jedną wartość, więc można go używać wszędzie tam, gdzie pojedyncza wartość ma sens. Oto wcześniejsze zapytanie wraz z jego wynikiem. Średnia wszystkich zamówień wynosi 980:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

Nie można było napisać WHERE total > AVG(total), ponieważ agregaty nie są dozwolone bezpośrednio w WHERE. Podzapytanie rozwiązuje ten problem, obliczając wartość osobno.

Co się stanie, jeśli podzapytanie „skalarne” zwróci kilka wierszy? Większość baz danych, w tym PostgreSQL, MySQL i SQL Server, zgłasza błąd. SQLite nie. W naszym teście po cichu użył tylko pierwszego wiersza, co mogło ukryć prawdziwy błąd, więc nie polegaj na tym zachowaniu.

W i NIE W

Gdy wewnętrzne zapytanie zwróci listę, użyj IN. Znajduje to klientów z co najmniej jednym zamówieniem powyżej 1000:

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN robi odwrotnie i znajduje klientów, którzy nigdy nie zamawiali:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

To działa tutaj, ale NOT IN ma niebezpieczny przypadek Edge, który omówimy poniżej.

Podzapytania w OD

Podzapytanie w FROM działa jak tabela tymczasowa, często nazywana tabelą pochodną. Jest to przydatne, gdy trzeba dokonać dwukrotnej agregacji, jak w przypadku tego zapytania dotyczącego średnich wydatków na klienta w każdym mieście:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

Zapytanie wewnętrzne sumuje każdego klienta, a zapytanie zewnętrzne uśrednia te sumy. Nadawanie tabeli pochodnej aliasu jest dobrym zwyczajem, ponieważ wymagają tego niektóre bazy danych. Jeśli GROUP BY jest dla Ciebie nowy, zobacz GROUP SQL BY i HAVING.

Podzapytania w SELECT

Podzapytanie skalarne może również dodać kolumnę obliczeniową. Ten liczy zamówienia na klienta:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev pojawia się z zerem. Sprzężenie wewnętrzne spowodowałoby całkowite porzucenie Deva, ponieważ Dev nie ma pasujących wierszy. Ten sam wynik można uzyskać za pomocą LEFT JOIN i GROUP BY, co wyjaśniamy w Wyjaśnienie złączy SQL.

Skorelowane podzapytania

Poprzedni przykład jest również skorelowany, ponieważ zapytanie wewnętrzne odwołuje się do c.id z zapytania zewnętrznego. Nieskorelowane podzapytanie może działać samodzielnie. Skorelowany nie może, ponieważ zależy od wartości z bieżącego zewnętrznego wiersza.

Spowoduje to wykrycie zamówień powyżej średniej tego samego klienta:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Oto jak to przeczytać. Dla zamówienia 101 wewnętrzne zapytanie uśrednia zamówienia klienta 1 (1200 i 800), co daje 1000. Ponieważ 1200 jest większe, wiersz zostaje zachowany. Dla rzędu 102 obowiązuje ta sama średnia, ale 800 nie jest większa, więc zostaje odrzucona. Pojedyncze zamówienie Klienta 2 jest równe jego własnej średniej, więc ono również zostaje odrzucone.

„Raz na wiersz” opisuje logikę, a optymalizatory zapytań często przepisują je na coś znacznie bardziej wydajnego. Mimo to warto zbadać wolno skorelowane podzapytanie. Indeks w wewnętrznej kolumnie, tutaj orders.customer_id, może mieć duże znaczenie. Zobacz Wyjaśnienie indeksów SQL.

ISTNIEJE I NIE ISTNIEJE

EXISTS zadaje pytanie tak lub nie: czy zapytanie wewnętrzne zwraca co najmniej jeden wiersz? Może zatrzymać się na pierwszym meczu, a to, co w środku wybierzesz, nie ma znaczenia, dlatego ludzie często piszą SELECT 1.

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

Pułapka NOT IN NULL

Jest to jeden z najczęstszych błędów podzapytań. Stworzyliśmy tabelę z jednym prawdziwym identyfikatorem i jednym NULL, a następnie uruchomiliśmy dla niej oba formularze:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Dlaczego pierwszy nic nie zwraca? id NOT IN (1, NULL) oznacza id <> 1 AND id <> NULL. Każde porównanie z NULL jest raczej nieznane niż prawdziwe, więc cały warunek nigdy nie może być prawdziwy. Nie pojawia się żaden komunikat o błędzie ani ostrzeżenie, jest po prostu pusty wynik, który wygląda wiarygodnie.

Podzapytanie vs JOIN vs CTE

Wiele zapytań można zapisać na więcej niż jeden sposób. Wcześniej znaleźliśmy klientów z dużym zamówieniem za pomocą IN. Oto ten sam wynik z połączeniem:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Zwróć uwagę na DISTINCT. Sprzężenie może generować duplikaty, jeśli klient ma kilka dopasowań, podczas gdy IN i EXISTS nie.

Wydajność różni się w zależności od bazy danych i danych, a nowoczesne optymalizatory często tworzą podobne plany. Więc najpierw wybierz najczystszą formę, a następnie sprawdź EXPLAIN, jeśli okaże się, że jest powolny.

Typowe błędy

• Użyj = z wynikiem wielowierszowym. Użyj IN lub upewnij się, że wewnętrzne zapytanie zwróci jeden wiersz.
• Użycie NOT IN w kolumnie dopuszczającej wartość null. Możesz po cichu utracić każdy wiersz.
• Używanie niejasnych aliasów. W skorelowanych zapytaniach kwalifikuj każdą kolumnę, w przeciwnym razie może ona zostać powiązana z niewłaściwą tabelą.
• Głębokie zagnieżdżanie. Trzy poziomy są trudne do odczytania, więc przekonwertuj je na CTE.
• Dodanie ORDER BY do środka. Zwykle nie ma to wpływu na wynik zewnętrzny i niektóre bazy danych je odrzucają.

Podsumowanie

Podzapytania są naturalnym sposobem wyrażania pytań dwuetapowych. Zacznij od podzapytań skalarnych i IN, a następnie przećwicz podzapytania skorelowane, rozumując po jednym zewnętrznym wierszu na raz. I pamiętaj o NOT EXISTS, ilekroć możliwe są wartości NULL. Aby poćwiczyć, napisz jedno pytanie na trzy sposoby, z podzapytaniem, złączeniem i CTE, a następnie porównaj oba wyniki i czytelność.

Najczęstsze pytania

Ucz się dalej