Dokumentacja SQLite z przykładami: Podzapytania

Q: Czym jest podzapytanie w SQLite?

Podzapytanie to instrukcja SELECT zagnieżdżona w innej instrukcji i ujęta w nawiasy. SQLite wykonuje zapytanie wewnętrzne i przekazuje jego wynik do zewnętrznego. Podzapytania mogą pojawiać się w WHERE, FROM, SELECT i kilku innych klauzulach.

Q: Czym różni się IN od EXISTS w SQLite?

IN (SELECT ...) sprawdza, czy wartość pasuje do któregokolwiek wiersza zwróconego przez podzapytanie. EXISTS (SELECT ...) sprawdza tylko, czy podzapytanie zwraca jakikolwiek wiersz, a wartości go nie interesują. EXISTS to zwykle lepszy wybór, gdy zapytanie wewnętrzne odwołuje się do wiersza zewnętrznego (podzapytanie skorelowane).

Q: Użyć podzapytania czy JOIN w SQLite?

Użyj JOIN, gdy w wyniku potrzebujesz kolumn z obu tabel. Użyj podzapytania, gdy chcesz tylko filtrować albo obliczyć jedną wartość. Optymalizator SQLite i tak często przepisuje jedną formę na drugą, więc wybierz tę, która czyta się czytelniej.

Podzapytanie to SELECT wewnątrz SELECT

Podzapytanie jest dokładnie tym, na co wygląda: instrukcją SELECT schowaną w innej instrukcji i ujętą w nawiasy. SQLite wykonuje zapytanie wewnętrzne, bierze jego wynik i przekazuje go do zewnętrznego.

Przygotujmy mały przykład, z którego będziemy korzystać dalej:

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders;

Pięć zamówień, czterech klientów, z których dwóch nic nie zamówiło. Będziemy używać tych danych przez całą stronę.

Podzapytanie w WHERE: filtrowanie według listy

Najczęstszy schemat: pobierz listę identyfikatorów w zapytaniu wewnętrznym, a potem przefiltruj według niej zapytanie zewnętrzne.

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Zapytanie wewnętrzne zwraca każdy customer_id, który występuje w orders. Zapytanie zewnętrzne zostawia tylko klientów, których id jest na tej liście. Pojawiają się Cleo, Boris i Ada; Dmitri (bez zamówień) nie.

IN (SELECT ...) to podstawowy wzorzec dla pytania „wiersze z A, które mają dopasowanie w B”. Czytaj to w myślach jako „gdzie wartość tej kolumny jest jedną z wartości zwróconych przez zapytanie wewnętrzne”.

NOT IN: uważaj na NULL

Odwrotne pytanie, „którzy klienci nic nie zamówili?”, jest o jedną linijkę dalej:

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Tutaj to działa. Ale NOT IN ma ostrą krawędź: jeśli podzapytanie kiedykolwiek zwróci NULL, całe NOT IN staje się NULL (a to nie jest TRUE) i dostajesz zero wierszy. Zaskakujące i ciche.

Bezpieczny nawyk przy użyciu NOT IN z kolumną, która może zawierać NULL:

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Albo użyj NOT EXISTS, które w ogóle nie ma tego problemu. Zaraz do tego dojdziemy.

Podzapytania skalarne: jeden wiersz, jedna kolumna

Podzapytanie skalarne zwraca jedną wartość (jeden wiersz, jedną kolumnę) i możesz go użyć wszędzie tam, gdzie oczekiwana jest wartość.

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name, total
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE total = (SELECT MAX(total) FROM orders);

Wewnętrzne SELECT MAX(total) FROM orders zwraca 200. Zapytanie zewnętrzne filtruje potem zamówienia o tej wartości. Przydaje się zawsze, gdy musisz porównać coś z agregatem.

Podzapytania skalarnego możesz też użyć na liście SELECT, żeby dołączyć obliczoną wartość do każdego wiersza:

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Dla każdego wiersza customers zapytanie wewnętrzne wykonuje się raz, z podstawionym customers.id. To podzapytanie skorelowane, więcej o nim niżej. W przypadkach typu „jedna liczba na wiersz” LEFT JOIN z GROUP BY zwykle działa szybciej, ale forma skalarna czyta się świetnie.

EXISTS: sprawdź tylko, czy cokolwiek pasuje

EXISTS to spokojniejszy kuzyn IN. Nie interesują go wartości, sprawdza tylko, czy podzapytanie zwraca jakikolwiek wiersz. Zwykle wpisuje się w środku SELECT 1, bo kolumna nie ma znaczenia.

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

To zapytanie znajduje klientów, którzy złożyli co najmniej jedno zamówienie powyżej 100. Zapytanie wewnętrzne odwołuje się do c.id z zapytania zewnętrznego i właśnie to czyni je skorelowanym. SQLite przestaje skanować tabelę wewnętrzną, gdy tylko znajdzie dopasowanie, dlatego EXISTS często wypada lepiej niż IN przy pytaniach „czy ten wiersz ma powiązany wiersz?”.

Negacja, NOT EXISTS, to bezpieczny względem NULL sposób, żeby zapytać o „brak powiązanego wiersza”:

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Podzapytanie w FROM: tabela pochodna

Podzapytanie może stać wszędzie tam, gdzie tabela, także w klauzuli FROM. Zapytanie wewnętrzne staje się tymczasową, nazwaną „tabelą pochodną”, którą możesz łączyć, filtrować albo agregować.

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT country, AVG(customer_total) AS avg_per_customer
FROM (
    SELECT c.country, c.id, SUM(o.total) AS customer_total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
) AS per_customer
GROUP BY country;

Zapytanie wewnętrzne liczy sumę dla każdego klienta. Zapytanie zewnętrzne uśrednia te sumy dla każdego kraju. Dwuetapowe agregacje tego typu to dokładnie to, do czego służą tabele pochodne: gdy nie da się zrobić wszystkiego jednym GROUP BY.

Alias AS per_customer jest wymagany: każda tabela pochodna musi mieć nazwę.

Podzapytania skorelowane: wykonanie dla każdego wiersza zewnętrznego

Podzapytanie jest skorelowane, gdy odwołuje się do kolumny z zapytania zewnętrznego. SQLite musi obliczać zapytanie wewnętrzne od nowa dla każdego wiersza zewnętrznego, co daje elastyczność, ale może sporo kosztować.

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name,
       (SELECT MAX(total)
        FROM orders
        WHERE customer_id = customers.id) AS biggest_order
FROM customers;

Dla każdego klienta znajdź jego największe zamówienie. Zapytanie wewnętrzne zależy od customers.id, więc wykonuje się raz na klienta. Klienci bez zamówień dostają NULL, i o to właśnie chodzi.

Podzapytania skorelowane naturalnie pasują do zadań typu „dla każdego wiersza z A oblicz coś z B”. Jeśli tabela jest mała albo wyszukiwanie korzysta z indeksu, wszystko jest w porządku. Na dużych tabelach bez odpowiednich indeksów zmierz wydajność przed wdrożeniem: JOIN z GROUP BY jest często szybszy.

Podzapytanie czy JOIN: co wybrać?

Te dwa zapytania odpowiadają na to samo pytanie:

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Podzapytanie
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- JOIN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

Oba zwracają te same wiersze. Optymalizator SQLite często wewnętrznie przepisuje jedną formę na drugą. Wybieraj na podstawie czytelności:

Użyj podzapytania, gdy chcesz tylko filtrować i nie chcesz, żeby kolumny z tabeli wewnętrznej zaśmiecały wynik.
Użyj JOIN, gdy wynik potrzebuje kolumn z obu tabel.
Użyj EXISTS, gdy pytasz „czy istnieje co najmniej jeden powiązany wiersz?”: to czytelniejsze i omija pułapki NULL w IN/NOT IN.

W razie wątpliwości napisz wersję, która tłumaczy się sama, gdy przeczytasz ją na głos.

Częsta pułapka: podzapytania zwracające wiele wierszy

Podzapytanie użyte z = musi zwracać najwyżej jeden wiersz. Jeśli zwróci więcej, SQLite wybierze jeden (w praktyce przypadkowy) i dostaniesz po cichu błędne wyniki, bez żadnego błędu.

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Ryzykowne: co, jeśli więcej niż jeden klient jest z UK?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');

Użyj IN, gdy zapytanie wewnętrzne może zwrócić wiele wierszy:

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');

Jeśli oczekujesz dokładnie jednego wiersza i chcesz to wymusić, dodaj LIMIT 1 i ORDER BY, żeby wybór był przynajmniej deterministyczny. Lepiej jednak napisać zapytanie tak, żeby pojedynczy wiersz gwarantowały same dane (filtruj po kolumnie unikalnej).

Dalej: Common Table Expressions

Podzapytania w FROM szybko stają się nieporęczne, zwłaszcza gdy potrzebujesz tej samej tabeli pochodnej dwa razy albo zagnieżdżenie sięga trzech poziomów. Common Table Expressions (WITH ... AS (...)) pozwalają nazwać podzapytanie na początku i odwoływać się do niego po nazwie w reszcie instrukcji. O tym jest następna strona.

Najczęściej zadawane pytania

Czym jest podzapytanie w SQLite?

Podzapytanie to instrukcja SELECT zagnieżdżona w innej instrukcji i ujęta w nawiasy. SQLite wykonuje zapytanie wewnętrzne i przekazuje jego wynik do zewnętrznego. Podzapytania mogą pojawiać się w WHERE, FROM, SELECT i kilku innych klauzulach.

Czym różni się IN od EXISTS w SQLite?

IN (SELECT ...) sprawdza, czy wartość pasuje do któregokolwiek wiersza zwróconego przez podzapytanie. EXISTS (SELECT ...) sprawdza tylko, czy podzapytanie zwraca jakikolwiek wiersz, a wartości go nie interesują. EXISTS to zwykle lepszy wybór, gdy zapytanie wewnętrzne odwołuje się do wiersza zewnętrznego (podzapytanie skorelowane).

Użyć podzapytania czy JOIN w SQLite?

Użyj JOIN, gdy w wyniku potrzebujesz kolumn z obu tabel. Użyj podzapytania, gdy chcesz tylko filtrować albo obliczyć jedną wartość. Optymalizator SQLite i tak często przepisuje jedną formę na drugą, więc wybierz tę, która czyta się czytelniej.

Czym jest podzapytanie skorelowane w SQLite?

Podzapytanie skorelowane odwołuje się do kolumny z zapytania zewnętrznego, więc musi być obliczane od nowa dla każdego wiersza zewnętrznego. Jest elastyczne, ale na dużych tabelach bywa wolne. Jeśli takie podzapytanie staje się wąskim gardłem, często pomaga przepisanie go na JOIN albo CTE.

Powiązane zagadnienia