Podzapytania, JOIN-y i widoki w SQL

✅ Sprawdź się z wykładu 6

Cel ostatniego wykładu

Wracamy do projektu centrum szkoleniowego „Dąb”. Umiemy już zaprojektować relacje, połączyć tabele i zmierzyć zapytanie. Teraz odpowiemy na pytanie: jak użyć wyniku jednego zapytania do zbudowania następnego?

Po wykładzie potrafisz dobrać IN, EXISTS, podzapytanie skalarne lub JOIN, przewidzieć liczbę wierszy wyniku i zapisać raport jako widok.

Proponowany przebieg 2 × 45 minut: przypomnienie i dane (10 min), animacja oraz IN/JOIN/EXISTS (25 min), agregacje i NULL (15 min), widoki (25 min), plany i zadania podsumowujące (15 min). Przerwę robimy między blokami.

1. Jedna baza do całego pokazu

Pobierz kompletny zestaw SQL. Przykłady dotyczą PostgreSQL 16. W środowisku kursu otwórz bazę prba7_pokaz w pgAdmin i folder materials/lectures/wyklad7. Po aktualizacji repozytorium wykonaj docker compose up -d w katalogu docker. Usługa przygotowująca tworzy nową bazę, zachowując wcześniejsze pokazy.

Jeśli tworzysz pustą bazę samodzielnie, wykonaj 00_setup.sql jeden raz. W gotowym środowisku zacznij od 01_steps.sql. Włącz Auto commit. Tabele są w osobnym schemacie prba7_demo; nie zmieniamy danych wykładu 6. To mały wycinek modelu „Dąb”, bez terminów i sal, bo badamy teraz logikę zapytań.

Relacja	Znaczenie i dane
`participants`	P1 Anna, P2 Bartek, P3 Celina, P4 Dawid
`courses`	SQL — Podstawy SQL; PY — Python
`editions`	E1: SQL, limit 2; E2: SQL, limit 3; E3: PY, limit 5; E4: PY, limit 2
`enrollments`	Zapis uczestnika na edycję; klucz `(participant_id, edition_id)`

Uczestnik	Edycja	Status
P1 Anna	E1	confirmed
P1 Anna	E2	confirmed
P2 Bartek	E1	confirmed
P3 Celina	E2	cancelled
P3 Celina	E3	confirmed

Dawid nie ma żadnego zapisu. E4 nie ma żadnego uczestnika. Te przypadki są celowe: sprawdzają, czy zapytanie poprawnie obsługuje brak dopasowania.

2. Film: od podzapytania do wyniku i JOIN-a

Film ma napisy, nie wymaga dźwięku. Można zatrzymywać go na każdej tabeli. Najpierw wybieramy edycje SQL: E1, E2. Następnie zachowujemy potwierdzone zapisy z tych edycji: P1/E1, P1/E2, P2/E1. Na końcu wybieramy uczestników: Anna, Bartek. Druga część pokazuje złączenia i dwa wiersze Anny.

Kolejność rozumowania a plan wykonania

„Najpierw podzapytanie, potem zapytanie zewnętrzne” jest tutaj modelem logicznym. SQL opisuje wynik. Optymalizator może zmienić sposób obliczania, np. użyć semizłączenia, a tabeli pośredniej wcale nie materializować. To, co silnik rzeczywiście zrobił, sprawdzamy przez EXPLAIN (ANALYZE, BUFFERS).

3. Podzapytanie wielowierszowe: IN

Pytanie: kto ma potwierdzony zapis na dowolną edycję SQL?

IN odpowiada na pytanie o przynależność: czy identyfikator występuje w wyniku podzapytania? W tym przykładzie prawa strona ma jedną kolumnę, ale może mieć wiele wierszy. Uruchom kolejno trzy polecenia z 01_steps.sql.

-- Step 1: SQL editions -> E1, E2.
SELECT edition_id FROM prba7_demo.editions WHERE course_id = 'SQL' ORDER BY edition_id;
-- Step 2: matching confirmed enrollments -> P1/E1, P1/E2, P2/E1.
SELECT participant_id, edition_id FROM prba7_demo.enrollments
WHERE status = 'confirmed' AND edition_id IN (
    SELECT edition_id FROM prba7_demo.editions WHERE course_id = 'SQL'
)
ORDER BY participant_id, edition_id;
-- Step 3: participant membership -> Anna, Bartek (one row per participant).
SELECT p.participant_id, p.name FROM prba7_demo.participants AS p
WHERE p.participant_id IN (
    SELECT z.participant_id FROM prba7_demo.enrollments AS z
    WHERE z.status = 'confirmed' AND z.edition_id IN (
        SELECT e.edition_id FROM prba7_demo.editions AS e WHERE e.course_id = 'SQL'
    )
)
ORDER BY p.participant_id;

Wynik wewnętrzny może zawierać P1 dwukrotnie. Nie powoduje to powielenia wiersza Anny w zapytaniu zewnętrznym: każdy wiersz participants jest albo zachowany, albo odrzucony. ORDER BY ustala kolejność wyświetlania; bez niego baza nie gwarantuje uporządkowania wyniku.

4. Ten sam problem przez JOIN — uwaga na krotność

Przypomnienie: JOIN ... ON tworzy wiersze z pasujących kombinacji rekordów. Klucz obcy wyjaśnia, po czym łączymy tabele, ale nie sprawia, że uczestnik pojawia się w wyniku tylko raz.

-- A JOIN returns matching combinations: Anna appears twice.
SELECT p.participant_id, p.name, e.edition_id
FROM prba7_demo.participants AS p
JOIN prba7_demo.enrollments AS z ON z.participant_id = p.participant_id
JOIN prba7_demo.editions AS e ON e.edition_id = z.edition_id
WHERE z.status = 'confirmed' AND e.course_id = 'SQL'
ORDER BY p.participant_id, e.edition_id;
-- Match the participant list returned by IN. Deduplicate by identity, not name alone.
SELECT DISTINCT p.participant_id, p.name
FROM prba7_demo.participants AS p
JOIN prba7_demo.enrollments AS z ON z.participant_id = p.participant_id
JOIN prba7_demo.editions AS e ON e.edition_id = z.edition_id
WHERE z.status = 'confirmed' AND e.course_id = 'SQL'
ORDER BY p.participant_id;

Pierwszy wynik ma 3 wiersze, a drugi 2:

participant_id	name	edition_id w pierwszym wyniku
P1	Anna	E1
P1	Anna	E2
P2	Bartek	E1

Jeżeli pytamy o zapisy, trzy wiersze są prawidłowe. Jeżeli pytamy o uczestników, potrzebujemy DISTINCT na identyfikatorze i nazwie albo konstrukcji wyrażającej istnienie. Samo DISTINCT name połączyłoby także różne osoby o tym samym imieniu. Nie usuwaj duplikatów bez ustalenia, co reprezentuje jeden wiersz raportu.

INNER JOIN i LEFT JOIN

INNER JOIN zachowuje wyłącznie dopasowania. LEFT JOIN zachowuje także wiersze lewej tabeli bez dopasowania; w kolumnach prawej pojawia się NULL. Dlatego do zestawienia wszystkich edycji, także pustej E4, użyjemy LEFT JOIN w sekcji o agregacji. Warunek statusu umieścimy w ON.

5. EXISTS i NOT EXISTS: czy istnieje dopasowanie?

Podzapytanie skorelowane odwołuje się do bieżącego wiersza zewnętrznego, tutaj przez z.participant_id = p.participant_id. EXISTS bada, czy istnieje co najmniej jeden pasujący wiersz; wartość 1 na liście SELECT nie jest liczbą zapisów. Nie potrzebujemy pobierać wszystkich danych znalezionych zapisów.

-- EXISTS tests membership without multiplying outer rows.
SELECT p.participant_id, p.name FROM prba7_demo.participants AS p
WHERE EXISTS (
    SELECT 1 FROM prba7_demo.enrollments AS z
    JOIN prba7_demo.editions AS e ON e.edition_id = z.edition_id
    WHERE z.participant_id = p.participant_id
      AND z.status = 'confirmed' AND e.course_id = 'SQL'
)
ORDER BY p.participant_id;
-- No confirmed enrollment -> Dawid (P4).
SELECT p.participant_id, p.name FROM prba7_demo.participants AS p
WHERE NOT EXISTS (
    SELECT 1 FROM prba7_demo.enrollments AS z
    WHERE z.participant_id = p.participant_id AND z.status = 'confirmed'
)
ORDER BY p.participant_id;

Pierwszy wynik: P1 Anna, P2 Bartek. Drugi: P4 Dawid. Celina nie pojawia się w drugim wyniku, ponieważ ma potwierdzony zapis Python. Gdybyśmy chcieli osoby bez potwierdzonego SQL, należałoby dołączyć edycje i dodać course_id = 'SQL' wewnątrz NOT EXISTS.

Skorelowane podzapytanie opisujemy logicznie dla każdego wiersza zewnętrznego. Nie wynika z tego, że silnik zawsze uruchamia je od początku dla każdego wiersza. Sposób wykonania zależy od planu, indeksów, statystyk i rozmiaru danych.

6. Podzapytanie skalarne: jedna wartość

Pytanie: które edycje mają limit większy od średniego limitu wszystkich edycji? Średnia (2 + 3 + 5 + 2) / 4 = 3. Odpowiedź: E3, limit 5.

-- The aggregate returns one row: average limit = 3. E3 is above average.
SELECT edition_id, seat_limit FROM prba7_demo.editions
WHERE seat_limit > (SELECT AVG(seat_limit) FROM prba7_demo.editions)
ORDER BY edition_id;
-- A nonaggregate scalar subquery returning zero rows has the value NULL.
SELECT (SELECT edition_id FROM prba7_demo.editions WHERE course_id = 'MISSING') AS no_match;
-- Correlated aggregate: P1=2, P2=1, P3=1, P4=0.
SELECT p.participant_id, p.name, (
    SELECT COUNT(*) FROM prba7_demo.enrollments AS z
    WHERE z.participant_id = p.participant_id AND z.status = 'confirmed'
) AS confirmed_count
FROM prba7_demo.participants AS p ORDER BY p.participant_id;

Podzapytanie skalarne musi zwrócić jedną kolumnę i najwyżej jeden wiersz. Zero wierszy daje NULL; więcej niż jeden wiersz powoduje błąd PostgreSQL. Agregacja AVG bez GROUP BY zwraca jeden wiersz, także dla pustego wejścia (wtedy AVG jest NULL). Analogiczny COUNT(*) zwraca wtedy 0.

W pliku 09_expected_errors.sql pierwsze polecenie próbuje potraktować dwie edycje SQL jako jedną wartość — to celowy błąd kardynalności. Nie naprawiamy go przypadkowym LIMIT 1: najpierw ustalamy, czy pytanie dotyczy jednej edycji, czy całego zbioru edycji.

7. Podzapytanie w FROM: wynik jako relacja

Pytanie: które edycje mają co najmniej dwóch potwierdzonych uczestników? Najpierw grupujemy zapisy, a potem filtrujemy wynik agregacji.

-- Aggregate first, filter the resulting relation: E1=2, E2=1, E3=1; result E1.
SELECT q.edition_id, q.confirmed_count FROM (
    SELECT edition_id, COUNT(*) AS confirmed_count
    FROM prba7_demo.enrollments WHERE status = 'confirmed' GROUP BY edition_id
) AS q
WHERE q.confirmed_count >= 2 ORDER BY q.edition_id;
-- The same logical operation using HAVING.
SELECT edition_id, COUNT(*) AS confirmed_count
FROM prba7_demo.enrollments WHERE status = 'confirmed'
GROUP BY edition_id HAVING COUNT(*) >= 2 ORDER BY edition_id;
-- LEFT JOIN preserves E4. COUNT(z.participant_id) ignores the null-extended row.
SELECT e.edition_id, COUNT(z.participant_id) AS confirmed_count
FROM prba7_demo.editions AS e
LEFT JOIN prba7_demo.enrollments AS z
    ON z.edition_id = e.edition_id AND z.status = 'confirmed'
GROUP BY e.edition_id ORDER BY e.edition_id;

Pierwsze dwa zapytania zwracają E1, 2. Alias q pozwala odwołać się do kolumn tabeli pochodnej. Nie jest to trwała tabela utworzona w bazie.

Trzecie zapytanie zwraca E1: 2, E2: 1, E3: 1, E4: 0. COUNT(z.participant_id) nie liczy NULL z niedopasowanego wiersza. COUNT(*) policzyłby go i błędnie dał E4 wartość 1. Przeniesienie z.status = 'confirmed' do WHERE usunęłoby E4: porównanie NULL = 'confirmed' nie jest prawdziwe.

WHERE, GROUP BY, HAVING

Logicznie WHERE filtruje wiersze wejściowe, GROUP BY tworzy grupy, a HAVING filtruje grupy. Podzapytanie w FROM pomaga rozdzielić te etapy, gdy wynik agregacji ma być wejściem do kolejnej operacji. Dla prostego warunku na COUNT zapis z HAVING jest krótszy.

8. Pułapka NOT IN i NULL

W SQL warunek może mieć wartość TRUE, FALSE lub nieznaną (NULL). WHERE zachowuje tylko wiersze, dla których warunek jest TRUE.

-- UNKNOWN, not TRUE: WHERE would discard this result.
SELECT 'P4' NOT IN ('P1', NULL) AS not_in_with_null;
-- A nullable imported list is deliberately separate from the constrained course tables.
WITH imported_ids(participant_id) AS (VALUES ('P1'::text), (NULL::text))
SELECT p.participant_id FROM prba7_demo.participants AS p
WHERE p.participant_id NOT IN (SELECT participant_id FROM imported_ids)
ORDER BY p.participant_id;
-- NOT EXISTS ignores the unmatched NULL: P2, P3, P4.
WITH imported_ids(participant_id) AS (VALUES ('P1'::text), (NULL::text))
SELECT p.participant_id FROM prba7_demo.participants AS p
WHERE NOT EXISTS (
    SELECT 1 FROM imported_ids AS i WHERE i.participant_id = p.participant_id
)
ORDER BY p.participant_id;

Lista ('P1', NULL) nie pozwala stwierdzić, że P4 na pewno do niej nie należy: nie wiemy, czym jest brakująca wartość. Dlatego NOT IN daje tu NULL, a drugi przykład nie zwraca żadnego wiersza. NOT EXISTS z jawnym warunkiem równości zwraca P2, P3, P4. W naszych tabelach klucze obce są NOT NULL, ale importy i inne źródła danych nie muszą mieć tej gwarancji.

ANY i ALL — rozszerzenie

x = ANY (podzapytanie) odpowiada porównaniu z dowolną wartością (dla jednej kolumny jest odpowiednikiem IN). x > ALL (podzapytanie) wymaga, by x było większe od każdej zwróconej wartości. Dla pustego podzapytania ANY daje FALSE, a ALL daje TRUE. Obecność NULL może prowadzić do wyniku nieznanego, jeżeli pozostałe porównania nie rozstrzygają całości. Nie traktuj ALL jako zamiennika porównania z MAX bez sprawdzenia pustego zbioru i brakujących wartości.

9. Widok: nazwany raport

Zamiast wklejać agregację do każdego raportu, zapiszmy ją jako VIEW. Zwykły widok przechowuje definicję zapytania, a nie osobną kopię wyników. W pliku 07_views.sql wykonaj tworzenie widoków jeden raz.

CREATE VIEW prba7_demo.edition_summary AS
SELECT e.edition_id, e.course_id, e.seat_limit,
       COUNT(z.participant_id) AS confirmed_count,
       e.seat_limit - COUNT(z.participant_id) AS free_places
FROM prba7_demo.editions AS e
LEFT JOIN prba7_demo.enrollments AS z
    ON z.edition_id = e.edition_id AND z.status = 'confirmed'
GROUP BY e.edition_id, e.course_id, e.seat_limit;
SELECT * FROM prba7_demo.edition_summary ORDER BY edition_id;
SELECT edition_id, free_places FROM prba7_demo.edition_summary
WHERE free_places > 0 ORDER BY edition_id;
-- A regular view reflects source changes. ROLLBACK restores the dataset.
BEGIN;
UPDATE prba7_demo.enrollments SET status = 'cancelled'
WHERE participant_id = 'P2' AND edition_id = 'E1';
SELECT * FROM prba7_demo.edition_summary WHERE edition_id = 'E1';
ROLLBACK;
SELECT * FROM prba7_demo.edition_summary WHERE edition_id = 'E1';
CREATE VIEW prba7_demo.small_editions AS
SELECT edition_id, course_id, seat_limit FROM prba7_demo.editions
WHERE seat_limit <= 3 WITH CHECK OPTION;
-- Successful update through an automatically updatable view; then restore it.
BEGIN;
UPDATE prba7_demo.small_editions SET seat_limit = 3 WHERE edition_id = 'E1';
SELECT * FROM prba7_demo.small_editions ORDER BY edition_id;
ROLLBACK;

Początkowo edition_summary daje:

edition_id	course_id	seat_limit	confirmed_count	free_places
E1	SQL	2	2	0
E2	SQL	3	1	2
E3	PY	5	1	4
E4	PY	2	0	2

Po anulowaniu zapisu Bartka E1 ma 1 potwierdzony zapis i 1 wolne miejsce. Po ROLLBACK znowu ma 2 zapisy i 0 wolnych miejsc. Widok nie wymaga odświeżania; odczyt podlega widoczności danych bieżącej transakcji. Widok zmaterializowany jest inną konstrukcją: przechowuje wynik i wymaga odświeżania, gdy chcemy uaktualnić ten wynik.

Czy można zmieniać dane przez widok?

Prosty widok small_editions jest automatycznie aktualizowalny w PostgreSQL. Zmiana trafia do tabeli editions. WITH CHECK OPTION wymaga, aby nowy lub zmieniony wiersz nadal spełniał filtr widoku. Ustawienie E1 limitu 10 przez ten widok zostanie odrzucone — drugie polecenie pliku 09_expected_errors.sql.

Raport edition_summary, zawierający agregację i złączenie, nie jest automatycznie aktualizowalny. PostgreSQL pozwala programować obsługę zmian bardziej złożonych widoków, np. wyzwalaczem INSTEAD OF; to osobny temat.

Utrzymanie definicji i uprawnienia

CREATE OR REPLACE VIEW pozwala zmienić zapytanie, ale istniejące kolumny muszą zachować nazwy, kolejność i typy; można dodać nowe kolumny na końcu. To ograniczenie chroni zapytania zależne od dotychczasowego interfejsu widoku.

Widok może udostępniać wybrane kolumny użytkownikowi mającemu odpowiednie uprawnienia. Samo utworzenie widoku nie odbiera dostępu do tabel źródłowych. Bezpieczeństwo zależy także od GRANT/REVOKE, właściciela widoku i sposobu sprawdzania uprawnień. Widok raportowy nie jest automatycznie szybszy: optymalizator nadal planuje zapytanie do danych źródłowych.

10. Pierwszy program w bazie — PL/pgSQL

Dotychczas polecenia SQL opisywały, jakie dane chcemy otrzymać. Język proceduralny pozwala zapisać także zmienne, obliczenia i wybór kolejnej instrukcji. W PostgreSQL używamy PL/pgSQL; podobny język Oracle nazywa się PL/SQL. Poniższy program wykonujemy w naszym dotychczasowym PostgreSQL.

Uruchomienie w pgAdmin

Otwórz 10_program.sql z paczki wykładu w Query Tool bazy prba7_pokaz. Najpierw musi być wykonany 07_views.sql, który tworzy widok prba7_demo.edition_summary. Zaznacz cały blok od DO do końcowego $$ LANGUAGE plpgsql; i kliknij Execute / F5. Wynik komunikatu zobaczysz w zakładce Messages, a nie w tabeli Data Output.

-- Run after 07_views.sql. Execute the entire DO block in pgAdmin Query Tool.
-- This program reads the report and prints a message; it does not reserve a place.
DO $$
DECLARE
    selected_edition text := 'E2';
    available_places bigint;
BEGIN
    SELECT free_places INTO STRICT available_places
    FROM prba7_demo.edition_summary
    WHERE edition_id = selected_edition;

    IF available_places > 0 THEN
        RAISE NOTICE 'Edycja %: wolne miejsca = %', selected_edition, available_places;
    ELSE
        RAISE NOTICE 'Edycja %: brak wolnych miejsc', selected_edition;
    END IF;
END;
$$ LANGUAGE plpgsql;

Dla początkowych danych program wypisze:

NOTICE: Edycja E2: wolne miejsca = 2

DECLARE deklaruje zmienne, a := nadaje wartość początkową. SELECT ... INTO STRICT zapisuje wynik do zmiennej i wymaga dokładnie jednego wiersza. IF ... THEN ... ELSE ... END IF wybiera jedną z dwóch instrukcji, a RAISE NOTICE wypisuje komunikat. BEGIN ... END wyznacza tutaj blok programu — nie jest poleceniem rozpoczęcia osobnej transakcji.

Zmień tylko 'E2' na 'E1' i uruchom cały blok ponownie. E1 nie ma wolnych miejsc, więc zobaczysz drugą gałąź:

NOTICE: Edycja E1: brak wolnych miejsc

Blok DO wykonuje program jednorazowo, bez zapisywania w bazie nazwanej funkcji. Nie zmienia danych ani nie rezerwuje miejsca. Jeśli podasz nieistniejący identyfikator edycji, INTO STRICT zgłosi błąd braku wiersza, zamiast po cichu przekazać NULL do warunku. Ten krótki przykład pokazuje przejście od wyniku SQL do prostej decyzji w programie.

11. IN, EXISTS czy JOIN — jak wybrać?

Potrzeba	Konstrukcja	Na co uważać
Jedna obliczona wartość	Podzapytanie skalarne	Najwyżej jeden wiersz; zero daje `NULL`
Przynależność do zbioru	`IN`	Liczba kolumn i logika `NULL`
Istnienie powiązania	`EXISTS`	Poprawna korelacja z wierszem zewnętrznym
Brak powiązania	`NOT EXISTS`	Jakie dokładnie powiązania wykluczamy?
Kolumny z obu tabel	`JOIN`	Jeden wiersz na każdą pasującą kombinację
Wszystkie lewe wiersze	`LEFT JOIN`	Filtry prawej tabeli i liczenie `NULL`
Dalsza praca na agregacie	Podzapytanie w `FROM`	Znaczenie jednego wiersza wyniku
Wielokrotnie używany raport	`VIEW`	Uprawnienia, stabilny interfejs, plan zapytania

Wykonaj 08_plans.sql. Sprawdź, czy plan zawiera złączenie, agregację lub semizłączenie. Nie zakładaj konkretnego węzła: plan może się zmienić. Na czterech uczestnikach różnica czasu nie jest wiarygodnym pomiarem wydajności. Najpierw dowodzimy równoważności wyników i krotności, dopiero potem porównujemy plany na reprezentatywnej ilości danych.

12. Zakończenie kursu — zadania do wspólnego omówienia

Zmień warunek przykładu na Python. Kto zostanie wybrany przez IN?
Znajdź uczestników bez potwierdzonego zapisu SQL. Czy Celina należy do wyniku?
Dlaczego DISTINCT participant_id, name, edition_id nadal zwróci Annę dwa razy?
Zwróć edycje bez potwierdzonych zapisów przez NOT EXISTS, a potem LEFT JOIN.
Dlaczego filtr widoku nie zastępuje CHECK (seat_limit > 0) w tabeli?
Czy odczyt free_places > 0 i późniejszy INSERT gwarantują brak nadmiarowych zapisów przy dwóch użytkownikach? Nie: to wymaga kontroli transakcyjnej; sam widok raportowy nie rezerwuje miejsca.

Odpowiedzi do samodzielnej kontroli

Celina (P3), mimo że Python ma dwie edycje — E4 jest pusta.
Celina i Dawid (P3, P4); anulowany SQL Celiny nie jest potwierdzonym zapisem.
E1 i E2 różnią się, więc całe wiersze są różne.
E4. W wersji z LEFT JOIN status filtrujemy w ON, a w WHERE sprawdzamy z.participant_id IS NULL.
CHECK OPTION dotyczy zmian przez dany widok, a ograniczenie tabeli chroni także inne drogi zapisu.

Przeszliśmy od wymagań i modelu encji, przez klucze i normalizację, do SQL, indeksów, podzapytań i widoków. W projekcie każdą decyzję uzasadnij pytaniem: jaką regułę danych lub potrzebę użytkownika realizuje ten element?