:~$ newsletter --subscribe
cat ~/artykuly/bazy-danych/podzapytania-podzapytania-niepowiazane-w-sql-cz-i.md

Podzapytania w SQL: niepowiązane, powiązane, CTE i operatory EXISTS, ANY, ALL

Język SQL pozwala na zagnieżdżanie zapytań do bazy danych czyli na umieszczanie jednego zapytania w drugim itd. Jest to możliwe dzięki temu, że serwery bazodanowe odczytują dane najpierw z wewnętrznych zapytań. A później stopniowo „przesuwają się” do tych zewnętrznych. Dzięki temu pobranych wcześniej danych możemy użyć w innych zapytaniach.

Podzapytania niepowiązane

Ważne tutaj jest, żeby przy konstruowaniu kwerend zastanowić się co tak naprawdę nasze podzapytanie zwróci. Czy będzie to jedna wartość, czy może jakiś zbiór albo tabela? Jest to o tyle kluczowe, że na przykład kiedy dostaniemy zbiór danych to nie możemy tego porównać do jednej wartości. W takim wypadku należy zastosować na przykład operator IN.

W tym artykule omówię podzapytania niepowiązane. Charakteryzują się one tym, że zwracają tylko jedną wartość oraz wykonywane są raz. W przeciwieństwie do podzapytań powiązanych (zostaną one omówione w II części tego wpisu) gdzie zapytanie wewnętrzne wykonywane jest dla każdego wiersza zapytania zewnętrznego z osobna.

Należy również zwrócić uwagę, że podzapytania zawsze umieszczane są w nawiasach okrągłych: ( ).

Do ćwiczeń będziemy wykorzystywać bazę danych zbudowaną z dwóch tabel:

Tabela: pracownicy

PracownicyID (♠)Miasto (♣)ImieNazwiskoData_urodzeniaStanowiskoData_zatrudnienia
11JanKowalski1984-12-23Programista1999-12-09
21AdamAdamiak1991-08-08Menadżer2011-01-31
33JakubWawrzyniak1990-03-16Programista2010-01-04
42MichałJakubiak1981-05-02Projektant1998-09-22

Gdzie kluczem (♠) jest kolumna PracownicyID.

Tabela: miastoZamieszkania

MiastoZamieszkaniaID (♣)Miasto
1Kraków
2Warszawa
3Wrocław
4Londyn

Gdzie kluczem (♣) jest kolumna MiastoZamieszkaniaID.

Obie tabele zostały połączone za pomocą relacji PracownicyID -> MiastoZamieszkaniaID (♠ -> ♣). Oznacza to, że do każdego pracownika zostało przypisane jego miasto zamieszkania.

Ćwiczenia

Pobierzmy za pomocą podzapytań informacje o pracownikach, którzy mieszkają w Warszawie:

sql
SELECT imie, nazwisko FROM pracownicy WHERE miasto =
(SELECT miastoZamieszkaniaID FROM miastoZamieszkania WHERE miasto = 'Warszawa');

Wynik powyższego zapytania:

ImieNazwisko
MichałJakubiak

Jak to działa?

Na samym początku zostało uruchomione nasze podzapytanie:

sql
SELECT miastoZamieszkaniaID FROM miastoZamieszkania WHERE miasto = 'Warszawa'

Pobraliśmy tutaj miastoZamieszkaniaID dla „Warszawy” czyli po prostu „2”. Następnie zostało uruchomione zewnętrzne zapytanie dla pobranej wcześniej wartości. Czyli wyglądało to tak:

sql
SELECT imie, nazwisko FROM pracownicy WHERE miasto = (2);

Przejdźmy teraz do kolejnego ćwiczenia.

Wcześniej pobraliśmy informacje o pracownikach mieszkających w Warszawie za pomocą podzapytań. Wyświetliliśmy wtedy imię i nazwisko ale co zrobić, jeżeli będziemy potrzebowali jeszcze nazwę miasta?

Oczywiście aby rozwiązać ten problem również można wykorzystać podzapytania:

sql
SELECT imie, nazwisko,
(SELECT miasto FROM miastoZamieszkania WHERE miasto = 'Warszawa') AS Miasto
FROM pracownicy WHERE miasto =
(SELECT miastoZamieszkaniaID FROM miastoZamieszkania WHERE miasto = 'Warszawa');

Rezultat:

ImieNazwiskoMiasto
MichałJakubiakWarszawa

Jak widać na powyższym przykładzie podzapytania mogą być również wywoływane w klauzuli SELECT.

Podzapytania mogą być częścią wyrażeń arytmetycznych:

sql
SELECT DATEDIFF(YEAR, (SELECT Data_urodzenia FROM pracownicy WHERE PracownicyID = 2), GETDATE());

Można je również dowolnie zagnieżdżać.

Podzapytania powiązane

W wcześniejszej części tego wpisu, omówiłem podzapytania niepowiązane. Można było tam zaobserwować pewną zależność, a mianowicie podzapytanie było w pełni niezależne od danych zwracanych przez zapytanie zewnętrzne.

Podzapytania powiązane wyróżniają się tym, że zapytanie wewnętrzne wykonywane jest dla każdego wiersza z osobna wygenerowanego przez zapytanie zewnętrzne, co uniemożliwia wykonanie takiego podzapytania „osobno”.

Do ćwiczeń będziemy wykorzystywać bazę danych zbudowaną z dwóch tabel:

Tabela: pracownicy

PracownicyID (♠)Miasto (♣)ImieNazwiskoData_urodzeniaStanowiskoData_zatrudnienia
11JanKowalski1984-12-23Programista1999-12-09
21AdamAdamiak1991-08-08Menadżer2011-01-31
33JakubWawrzyniak1990-03-16Programista2010-01-04
42MichałJakubiak1981-05-02Projektant1998-09-22

Gdzie kluczem (♠) jest kolumna PracownicyID.

Tabela: miastoZamieszkania

MiastoZamieszkaniaID (♣)Miasto
1Kraków
2Warszawa
3Wrocław
4Londyn

Gdzie kluczem (♣) jest kolumna MiastoZamieszkaniaID.

Obie tabele zostały połączone za pomocą relacji PracownicyID -> MiastoZamieszkaniaID (♠ -> ♣). Oznacza to, że do każdego pracownika zostało przypisane jego miasto zamieszkania.

Ćwiczenia

Pobierzmy informację o pracownikach mieszkających poza „Warszawą”:

sql
SELECT Imie, Nazwisko FROM pracownicy AS P
WHERE Miasto IN (SELECT miastoZamieszkaniaID FROM miastoZamieszkania WHERE P.miasto <> 'Warszawa');

Rezultat:

ImieNazwisko
JanKowalski
AdamAdamiak
JakubWawrzyniak

Nasze podzapytanie:

sql
SELECT miastoZamieszkaniaID FROM miastoZamieszkania WHERE P.miasto <> 'Warszawa'

wykorzystuje dane zwrócone przez zapytanie zewnętrzne (co prawda w tym przykładzie trochę na siłę) co z definicji powoduje, że jest podzapytaniem powiązanym. Dodatkowo w tym ćwiczeniu będzie ono uruchamiane dla każdego wiersza z tabeli pracownicy (czyli w przeciwieństwie do podzapytań niepowiązanych będzie uruchamiane wiele razy). Drugą rzeczą, na którą należy zwrócić uwagę, jest to, że powyższa kwerenda zwraca tabelę (zbiór danych) więc aby poprawnie wszystko zadziałało musieliśmy zastosować operator IN.

Wykorzystajmy kwerendę z poprzedniego ćwiczenia ale z małą modyfikacją:

sql
SELECT Imie, Nazwisko FROM pracownicy AS P
WHERE Miasto IN
(SELECT miastoZamieszkaniaID FROM miastoZamieszkania AS P WHERE P.imie <> '3');

Jak widać, zastosowaliśmy ten sam alias w zapytaniu zewnętrznym oraz wewnętrznym. Po uruchomieniu tego przykładu powinniśmy otrzymać błąd podobny do tego:

sql
Msg 207, Level 16, State 1, Line 3
Invalid column name 'imie'.

Czyli serwer zasygnalizował nam próbę podania niepoprawnej nazwy kolumny, gdyż w tabeli miastoZamieszkania nie ma kolumny o nazwie imie.

Warto jeszcze na koniec wspomnieć, że zarówno zapytanie zewnętrzne jak i wewnętrzne może odwoływać się do tej samej tabeli.

Podzapytanie jako źródło danych

Do tej pory we wcześniejszych częściach tego artykułu omawiane były podzapytania, które były wstawiane np. w klauzuli SELECT albo WHERE. Oczywiście to nie wyczerpuje tego tematu, bowiem nasze podzapytanie można również umieścić w klauzuli FROM. Jest nawet do tego stworzone specjalne wyrażenie tabelaryczne – CTE (ang. Common Table Expressions).

Tabela klienci, na której uruchomimy przykładowe zapytanie:

IDImieNazwiskoMiasto
1JanKowalskiWarszawa
2MichałŁagodaKraków
3MarcinKulczykKraków
4RadosławSikorskiKraków
5MonikaKopaczGdańsk
6PiotrGrabiecToruń
7EwaPogodnaBydgoszcz
8SebastianSzybkiWarszawa

Ćwiczenie

Przyjrzyjmy się teraz przykładowemu podzapytaniu jako źródło danych:

sql
SELECT * FROM (SELECT Imie, Nazwisko FROM klienci) AS k;

Rezultat:

ImieNazwisko
JanKowalski
MichałŁagoda
MarcinKulczyk
RadosławSikorski
MonikaKopacz
PiotrGrabiec
EwaPogodna
SebastianSzybki

Nasza kwerenda pobrała wszystkie kolumny (*) z utworzonej za pomocą podzapytania z klauzuli FROM tabeli.

Tego typu techniki przydatne są między innymi, kiedy musimy wykonać jakąś operację na danych z kilku tabel i na dodatek informacje pobrane z każdej tabeli muszą również zostać wcześniej jakoś zmodyfikowane (np. musi zostać obliczona średnia, suma itp.).

Przypuśćmy na przykład, że musimy obliczyć średnią cenę komputerów, drukarek oraz monitorów sprzedawanych w sklepie komputerowym. Dane te są zapisane w trzech różnych tabelach. Zastosujemy więc tutaj podzapytanie które obliczy średnią cenę z tabeli komputery, średnią cenę z tabeli drukarki oraz średnią cenę z tabeli monitory, a następnie z tych trzech danych wywołamy zewnętrzne podzapytanie i obliczymy właściwą średnią.

Ten przykład został omówiony w dalszej części tego artykułu.

CTE (Common Table Expressions)

W wcześniejszej części tego artykułu omawiałem podzapytania jako źródło danych. Teraz będziemy kontynuować tę tematykę z tym, że zastosujemy w naszych kwerendach specjalne wyrażenie tabelaryczne CTE – Common Table Expressions specjalnie stworzone do tego typu konstrukcji.

Ogólna składnia CTE:

sql
;WITH nazwaCTE AS
(podzapytanie wewnętrzne)
SELECT * FROM nazwaCTE; --podzapytanie główne

Przykład

sql
;WITH nazwaCTE AS
(SELECT kol1, kol2 FROM tabela) -- podzapytanie (wewnętrzne)
SELECT * FROM nazwaCTE; -- zapytanie główne (zewnętrzne)

Ta sama kwerenda bez CTE:

sql
SELECT * FROM (SELECT kol1, kol2 FROM tabela) AS t;

Przy korzystaniu z CTE należy zwrócić uwagę na kilka rzeczy:

  1. Nazwę CTE podaje się po słowie kluczowym: ;WITH (pamiętać o „;„),
  2. Do utworzonego CTE można się odwoływać dowolną ilość razy za pośrednictwem jego nazwy,
  3. Do CTE można się odwołać po jego zdefiniowaniu, jeżeli mamy jakieś rozbudowane zapytanie np. z kilkoma CTE to najpierw musimy je zdefiniować a potem się odwołać (nie można odwołać się do CTE, które zostało zdefiniowane później).

Ćwiczenie

Przejdźmy teraz do przykładu, który opisywałem już w wcześniejszej części tego wpisu. Mamy więc następujące tabele:

komputery:

NazwaCena
ASUS T200TA2000
MacBook PRO6800
Lenovo YOGA 22500
Dell XPS6000

drukarki:

NazwaCena
HP DESJKET 170
Canon PIXMA MX395220
EPSON XP-215330

monitory:

NazwaCena
Samsung SyncMaster TA23A3501000
EIZO FlexScan EV2730Q3400
EIZO LCD EV2436WFS3-BK1800

Chcemy obliczyć średnią cenę sprzętu komputerowego, czyli średnią cenę z komputerów, drukarek oraz monitorów. Do tego celu wykorzystamy podzapytania oraz CTE:

sql
;WITH cte AS
(
SELECT AVG(Cena) AS Cena FROM komputery
UNION ALL
SELECT AVG(Cena) AS Cena FROM drukarki
UNION ALL
SELECT AVG(Cena) AS Cena FROM monitory
)
SELECT AVG(Cena) FROM cte;

Rezultat:

AVG(Cena)
2210

Operatory EXISTS, ANY, SOME i ALL

Rezultat zwracany przez podzapytanie (zapytanie wewnętrzne) można porównywać za pomocą jednego ze specjalnych operatorów dostępnych w SQL: EXISTS, ANY, SOME lub ALL. Możemy za ich pomocą sprawdzić między innymi czy podzapytanie zwróciło jakieś dane lub wartość wiersza.

  • EXISTS – zwraca true jeśli podzapytanie zwróciło jakiekolwiek dane, jeśli nie zwraca false,
  • ANY – sprawdza wartość dowolnego wiersza zwróconego przez podzapytanie,
  • SOME – sprawdza wartość dowolnego wiersza zwróconego przez podzapytanie (ten operator działa tak samo jak ANY),
  • ALL – sprawdza wartości wszystkich wierszy zwróconych przez podzapytanie.

Do ćwiczeń będziemy wykorzystywać bazę danych zbudowaną z dwóch tabel:

Tabela: pracownicy

PracownicyID (♠)Miasto (♣)ImieNazwiskoData_urodzeniaStanowiskoData_zatrudnienia
11JanKowalski1984-12-23Programista1999-12-09
21AdamAdamiak1991-08-08Menadżer2011-01-31
33JakubWawrzyniak1990-03-16Programista2010-01-04
42MichałJakubiak1981-05-02Projektant1998-09-22

Gdzie kluczem (♠) jest kolumna PracownicyID.

Tabela: miastoZamieszkania

MiastoZamieszkaniaID (♣)Miasto
1Kraków
2Warszawa
3Wrocław
4Londyn

Gdzie kluczem (♣) jest kolumna MiastoZamieszkaniaID.

Obie tabele zostały połączone za pomocą relacji PracownicyID -> MiastoZamieszkaniaID (♠ -> ♣). Oznacza to, że do każdego pracownika zostało przypisane jego miasto zamieszkania.

Ćwiczenia

Exists

Pobierzmy z bazy danych informacje o miastach, w których nie mieszkają żadni pracownicy:

sql
SELECT Miasto FROM miastoZamieszkania AS mz WHERE
NOT EXISTS (SELECT * FROM pracownicy WHERE mz.MiastoZamieszkaniaID = miasto);

Rezultat:

Miasto
Londyn

Oczywiście w tym ćwiczeniu musieliśmy użyć przeczenia (NOT), NOT EXISTS jest prawdziwe (zwraca true) jeśli nie zostanie zwrócony przez podzapytanie żaden rekord, a właśnie o to nam chodziło w tej kwerendzie.

Informacje o pracownikach niemieszkających w Krakowie:

sql
SELECT imie, nazwisko FROM pracownicy WHERE
EXISTS (SELECT * FROM miastoZamieszkania
WHERE pracownicy.miasto = miastoZamieszkaniaID AND miasto <> 'Kraków')

Rezultat:

ImieNazwisko
JakubWawrzyniak
MichałJakubiak

ANY, SOME

UWAGA: Omawiany materiał działa na bazach danych firmy Microsoft.

Operator ANY/SOME jest prawdziwy (zwraca true) jeśli którekolwiek ze zwróconych przez podzapytanie danych spełni poprzedzający go warunek logiczny. Jak pisałem na wstępie, wystarczy że warunek logiczny spełni dowolny (którykolwiek) ze zwróconych rekordów.

sql
SELECT imie, nazwisko FROM pracownicy WHERE
'Madryt' <> ANY (SELECT miasto FROM miastoZamieszkania);

Rezultat:

ImięNazwisko
JanKowalski
AdamAdamiak
JakubWawrzyniak
MichałJakubiak

Oczywiście ANY możemy zastąpić operatorem SOME.

ALL

UWAGA: Omawiany materiał działa na bazach danych firmy Microsoft.

Operator ALL działa w zasadzie tak samo jak ANY/SOME z tym wyjątkiem, że sprawdza wszystkie dane. Wszystkie zwrócone przez podzapytanie rekordy muszą spełniać podany wcześniej warunek logiczny.

sql
SELECT imie, nazwisko FROM pracownicy WHERE
'Londyn' <> ALL (SELECT miasto FROM miastoZamieszkania);

Rezultat:

Nie otrzymamy żadnego wyniku. 0 rekordów.

W tabeli miastoZamieszkania znajduje się miasto „Londyn” dlatego warunek logiczny nie został spełniony.

udostępnij: ·
Łukasz Dudziński
Łukasz Dudziński · założyciel i autor
Pasjonat informatyki, a zwłaszcza programowania. Zwariowany maniak na tle nowych technologii oraz komputerów. W wolnych chwilach bloger... więcej-o-mnie →
ls ~/artykuly/powiazane --limit 3
komentarze --count 0
rss dla komentarzy →

// brak komentarzy — bądź pierwszy

dodaj-komentarz

Komentarze są moderowane. Podane dane przetwarzamy wyłącznie w celu publikacji komentarza (polityka prywatności).