Użycie podzapytań w SELECT

Chciałbym jeszcze raz wrócić do tematu podzapytań w SELECT. Szczególnie z naciskiem na fakt, że zapytanie wewnętrzne może odnosić się do danych z zapytania zewnętrznego. Niby wszystko proste, ale nie do końca. Zanurzmy się jeszcze raz w ten temat...

Podzapytania w SELECT pozwalają dodawać dodatkowe kolumny z wyliczanymi wartościami lub danymi zależnymi od innych rekordów albo tabel. Na przykład możesz wyświetlić listę studentów z ich średnią oceną, liczbą kursów, na które są zapisani, albo aktualną maksymalną oceną w grupie. To przydatne, gdy chcesz analizować dane "w locie", tworząc podsumowujące kolumny bez wcześniejszego przetwarzania danych.

Podstawy podzapytań w SELECT

Zanim przejdziemy do przykładów, rozłóżmy na czynniki ogólną składnię. Podzapytania w SELECT wyglądają tak:

SELECT column1,
       column2,
       (SELECT agregacja_lub_warunek FROM inna_tabela WHERE warunek) AS nowa_nazwa_kolumny
FROM glowna_tabela;

Zwróć uwagę, że podzapytanie zwraca jedną wartość, która pojawia się w wyniku jako nowa kolumna. Przy tym warunek może odnosić się do kolumn glowna_tabela.

Przykład 1: Dodanie średniej oceny studenta

Zacznijmy od prostego i przydatnego zapytania: mamy tabelę students i tabelę grades, w której trzymamy oceny studentów.

Tabela students:

Tabela grades:

Teraz chcemy dostać listę studentów z ich imionami i średnią oceną. Do tego użyjemy podzapytania w SELECT:

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade) 
     FROM grades g 
     WHERE g.student_id = s.id) AS average_grade
FROM students s;

Wynik:

Tutaj podzapytanie (SELECT AVG(g.grade) FROM grades g WHERE g.student_id = s.id) liczy średnią ocenę dla każdego studenta. Zwraca jedną wartość dla każdego wiersza z tabeli students i to jest wygodne, gdy nie chcesz robić JOIN albo przygotowywać widoków.

Przykład 2: Liczenie liczby kursów dla każdego studenta

Teraz dodajmy dane o studentach: ile kursów uczęszczają. Do tego mamy dodatkowe tabele:

Tabela enrollments:

Wyświetlamy listę studentów z liczbą kursów, na które są zapisani:

SELECT
    s.id,
    s.name,
    (SELECT COUNT(*)
     FROM enrollments e
     WHERE e.student_id = s.id) AS course_count -- odniesienie do tabeli students z zapytania zewnętrznego
FROM students s;

Wynik:

Podzapytanie (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id) liczy liczbę rekordów w tabeli enrollments dla każdego studenta.

Agregacja danych w podzapytaniach

Często podzapytania w SELECT używane są do liczenia danych agregowanych. Takie funkcje jak AVG, SUM, COUNT, MAX, MIN pozwalają obrabiać dane bezpośrednio w innych zapytaniach.

Przykład 3: Suma ocen studenta

Dodajmy teraz sumę ocen dla każdego studenta. Do tego użyjemy podzapytania, które liczy sumę wszystkich ocen z tabeli grades:

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Wynik:

To podzapytanie (SELECT SUM(g.grade) FROM grades g WHERE g.student_id = s.id) sumuje oceny każdego studenta. Jeśli student nie ma ocen, wynik będzie NULL, bo SUM zwraca NULL, gdy nie ma wartości.

Ograniczenia i wskazówki

1. Wydajność. Podzapytania w SELECT są wykonywane osobno dla każdego wiersza głównej tabeli. To może powodować spore opóźnienia na dużych zbiorach danych. Jeśli możesz, zamień je na JOIN albo użyj wcześniej przygotowanych danych agregowanych. Na przykład:

SELECT
    s.id,
    s.name,
    g.total_grade
FROM students s
LEFT JOIN (
    SELECT student_id, SUM(grade) AS total_grade
    FROM grades
    GROUP BY student_id
) g ON s.id = g.student_id;

To podejście z JOIN jest bardziej optymalne, bo grupowanie i liczenie odbywa się tylko raz.

2. Problemy z NULL.

Jeśli w podzapytaniu nie ma danych, wynik będzie NULL. To może być zaskakujące. Przykład:

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Jeśli dla studenta nie ma rekordów w grades, wynik total_grade będzie NULL. Żeby zamienić NULL na 0, użyj funkcji COALESCE:

SELECT
    s.id,
    s.name,
    COALESCE((SELECT SUM(g.grade)
              FROM grades g
              WHERE g.student_id = s.id), 0) AS total_grade
FROM students s;

Tak, tutaj jako pierwszy parametr funkcji COALESCE przekazujemy

(
    SELECT SUM(g.grade)
    FROM grades g
    WHERE g.student_id = s.id
)

Optymalizacja podzapytań w SELECT

Żeby uniknąć zbędnych obliczeń i poprawić wydajność:

1. Używaj indeksów na kolumnach, które biorą udział w podzapytaniach. Na przykład indeksowanie student_id w tabeli grades przyspieszy filtrowanie.
2. Zamieniaj podzapytania na wcześniej przygotowane dane agregowane przez JOIN, jeśli się da.
3. Ogranicz ilość danych przetwarzanych przez podzapytania, stosując filtrowanie (WHERE).

Końcowy przykład: łączenie podzapytań

Zbierzmy całą naszą wiedzę i stwórzmy zapytanie, które wyświetli imię studenta, średnią ocenę, liczbę kursów i sumę ocen:

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS average_grade,
    (SELECT COUNT(*) 
     FROM enrollments e 
     WHERE e.student_id = s.id) AS course_count,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

To zapytanie zwraca pełny profil studenta, zbudowany dzięki mocy podzapytań. Widzimy średnią i sumę ocen oraz liczbę kursów, na które zapisany jest każdy student. Taka konstrukcja to świetny sposób na szybkie uzyskanie danych agregowanych bez tworzenia osobnych VIEW czy łączenia przez JOIN.