Kurs języka HTML i CSS

Poradnik webmastera

  • Zwiększ rozmiar czcionki
  • Domyślny  rozmiar czcionki
  • Zmniejsz rozmiar czcionki

Podzapytania

Email Drukuj PDF

W tym odcinku poznasz odpowiednik zmiennych i funkcji znanych z proceduralnych języków programowania — podzapytania. Dowiesz się, w jaki sposób MySQL wykonuje podzapytania niepowiązane i powiązane, oraz nauczysz się korzystać z podzapytań przy wybieraniu danych.

Wprowadzenie

Podzapytania lub zapytania zagnieżdżone to instrukcje SELECT umieszczone wewnątrz innych instrukcji SELECT. Podzapytanie może być użyte w dowolnej klauzuli — w klauzuli FROM będziemy je wykorzystywać jako źródła danych, w klauzuli SELECT — jako odpowiedniki zmiennych lub funkcji, w klauzuli WHERE — do wybierania danych itd.

Stosując podzapytania, możemy osiągnąć efekt podobny do tego, jaki osiągamy przez wprowadzenie zmiennych w proceduralnych językach programowania. Zamiast wykonania instrukcji i przechowania jej w zmiennej, wykonywane jest drugie wyrażenie wykorzystujące poprzedni wynik, co daje efekt zagnieżdżania pierwszej instrukcji w drugiej.

Podobnie jak wywołania funkcji w innych funkcjach, zapytania możemy zagnieżdżać w innych zapytaniach. Podzapytanie musi zostać zapisane w nawiasie.

Serwer baz danych wykonuje podzapytania, zaczynając od najbardziej wewnętrznej instrukcji SELECT, po to aby wynik tej instrukcji wykorzystać do wykonania zapytań zewnętrznych.

W zależności od typu zwracanych przez wewnętrzne zapytania wartości, podzapytania dzieli się na:

  1. podzapytania zwracające pojedynczą wartość skalarną, np. nazwisko sprzedawcy, który sprzedał najwięcej towarów;

  2. podzapytania zwracające listę wartości, np. identyfikatory sprzedanych w danym miesiącu towarów;

  3. podzapytania zwracające dane tabelaryczne, np. dane sprzedawców uzupełnione o liczbę i wartość zrealizowanych przez nich zamówień.

Niezależnie od typu zwracanych wartości, podzapytania mogą być powiązane lub niepowiązane:

  1. W podzapytaniach niepowiązanych wewnętrzne zapytanie jest wykonywane tylko raz, a więc zwraca jeden wynik.

  2. W podzapytaniach powiązanych wewnętrzne zapytanie jest wykonywane dla każdego wiersza zwróconego przez zewnętrzne zapytanie, a więc zwraca tyle wyników, ile wierszy liczy wynik zewnętrznego zapytania.

Podzapytania niepowiązane

Podzapytania niepowiązane wykonywane są następująco:

  1. Wykonana zostaje wewnętrzna instrukcja SELECT.
  2. Jej wyniki są przekazywane do zapytania zewnętrznego.
  3. Otrzymane dane pozwalają wykonać zapytanie zewnętrzne.

Podzapytania jako źródła danych

Wynik podzapytania może być zbiorem danych źródłowych dla innego zapytania. W takim przypadku podzapytanie znajduje się w klauzuli FROM zapytania nadrzędnego. Pozwala to:

  1. uprościć zapytanie i poprawić jego czytelność — ponieważ zapytanie wewnętrzne wykonywane jest jako pierwsze, zdefiniowane w nim aliasy kolumn mogą być używane w każdej klauzuli zapytania zewnętrznego;

  2. dynamicznie filtrować wiersze i wyliczać dane bazowe dla zapytań zewnętrznych (listing 7.1).

Listing 7.1. Podzapytanie jako źródło danych, czyli w roli dynamicznego widoku

SELECT *

FROM (

SELECT title, fname, lname

FROM customer) AS t

WHERE title ='Mr';

+-------+---------+---------+

| title | fname   | lname   |

+-------+---------+---------+

| Mr    | Andrew  | Stones  |

| Mr    | Adrian  | Matthew |

| Mr    | Simon   | Cozens  |

| Mr    | Neil    | Matthew |

| Mr    | Richard | Stones  |

| Mr    | Mike    | Howard  |

| Mr    | Dave    | Jones   |

| Mr    | Richard | Neill   |

| Mr    | Bill    | Neill   |

| Mr    | David   | Hudson  |

+-------+---------+---------+

MySQL, wykonując tę instrukcję, najpierw wykonał wewnętrzne zapytanie (listing 7.2) i nazwał wynik >t, a następnie odczytał z tymczasowej tabeli >t te wiersze, dla których spełniony był warunek title ='Mr'.

Tabela pochodna to nic innego jak wynik zapytania wewnętrznego zdefiniowanego w klauzuli FROM zewnętrznego zapytania. Tabele pochodne są dostępne tylko w ramach tych podzapytań, w których były zdefiniowane, i po zakończeniu ich wykonywania są automatycznie usuwane.

Listing 7.2. Charakterystyczne dla zapytań niepowiązanych jest to, że zapytanie wewnętrzne można wykonać jako osobną instrukcję

SELECT title, fname, lname

FROM customer;

+-------+-----------+---------+

| title | fname     | lname   |

+-------+-----------+---------+

| Miss  | Jenny     | Stones  |

| Mr    | Andrew    | Stones  |

| Miss  | Alex      | Matthew |

| Mr    | Adrian    | Matthew |

| Mr    | Simon     | Cozens  |

| Mr    | Neil      | Matthew |

| Mr    | Richard   | Stones  |

| Mrs   | Anna      | Stones  |

| Mrs   | Christine | Hickman |

| Mr    | Mike      | Howard  |

| Mr    | Dave      | Jones   |

| Mr    | Richard   | Neill   |

| Mrs   | Laura     | Hendy   |

| Mr    | Bill      | Neill   |

| Mr    | David     | Hudson  |

|       |           | Wolski  |

+-------+-----------+---------+

Podzapytania jako zmienne

W proceduralnych i obiektowych językach programowania zmienne są pojemnikami o określonych nazwach, w których można przechowywać wartości pewnych typów. Nazwę i typ zmiennej najczęściej określa się podczas jej deklarowania, a wartość początkowa nadawana jest zmiennej podczas jej inicjowania.

Dwie najważniejsze wykonywane na zmiennych operacje to:

  1. przypisanie wartości, pozwalające zmieniać wartość zmiennej w trakcie działania programu;

  2. odczytanie wartości, czyli posłużenie się nazwą zmiennej w celu pobrania jej aktualnej wartości.

Podzapytania zwracające pojedynczą wartość są odpowiednikami zmiennych typów prostych, a podzapytania zwracające listę wartości — zmiennych tabelarycznych. Najwyraźniej widać to podobieństwo na przykładzie podzapytań niepowiązanych. Wynik podzapytania może zostać potraktowany jak zmienna z ustawioną w wyniku wykonania podzapytania wartością. Również w tym przypadku wewnętrzne zapytanie-wyrażenie jest wykonywane tylko raz podczas wykonywania całej instrukcji SELECT (listing 7.3).

Listing 7.3. Podzapytanie jako wyrażenie. W tym przypadku podzapytanie posłużyło nam do wyliczenia średniej ceny towarów

SELECT description, sell_price,

(SELECT AVG(sell_price)

FROM item)

FROM item;

+-----------------+------------+------------------------------------+

| description     | sell_price | (SELECT AVG(sell_price) FROM item) |

+-----------------+------------+------------------------------------+

| Wood Puzzle     | 21.95      | 10.435455                          |

| Rubik Cube      | 11.49      | 10.435455                          |

| Linux CD        | 2.49       | 10.435455                          |

| Tissues         | 3.99       | 10.435455                          |

| Picture Frame   | 9.95       | 10.435455                          |

| Fan Small       | 15.75      | 10.435455                          |

| Fan Large       | 19.95      | 10.435455                          |

| Toothbrush      | 1.45       | 10.435455                          |

| Roman Coin      | 2.45       | 10.435455                          |

| Carrier Bag     | 0.00       | 10.435455                          |

| Speakers        | 25.32      | 10.435455                          |

| SQL Server 2005 | NULL       | 10.435455                          |

+-----------------+------------+------------------------------------+

Skoro wartością wyrażenia (SELECT AVG(sell_price) FROM item) jest po prostu średnia cena produktów, nic nie stoi na przeszkodzie, żeby użyć tego wyrażenia w operacjach matematycznych (listing 7.4).

Listing 7.4. Zapytanie zwracające różnice pomiędzy ceną danego produktu a średnią ceną wszystkich towarów

SELECT description, sell_price, sell_price –

(SELECT AVG(sell_price)

FROM item)

FROM item;

+-----------------+------------+------------------------------------------------+

| description     | sell_price | sell_price - (SELECT AVG(sell_price)

FROM item) |

+-----------------+------------+------------------------------------------------+

| Wood Puzzle     | 21.95      | 11.514545|

| Rubik Cube      | 11.49      | 1.054545 |

| Linux CD        | 2.49       | -7.945455|

| Tissues         | 3.99       | -6.445455|

| Picture Frame   | 9.95       | -0.485455|

| Fan Small       | 15.75      | 5.314545 |

| Fan Large       | 19.95      | 9.514545 |

| Toothbrush      | 1.45       | -8.985455|

| Roman Coin      | 2.45       | -7.985455|

| Carrier Bag     | 0.00       | -10.435455|

| Speakers        | 25.32      | 14.884545|

| SQL Server 2005 | NULL       | NULL|

+-----------------+------------+------------------------------------------------+

Kolejny przykład pokazuje sposób użycia podzapytania niepowiązanego w klauzuli WHERE (listing 7.5).

Listing 7.5. Zapytanie zwracające nazwę najdroższego towaru. Ten sam wynik można uzyskać za pomocą klauzuli HAVING

SELECT description, sell_price

FROM item

WHERE sell_price = (SELECT MAX(sell_price)

FROM item);

+-------------+------------+

| description | sell_price |

+-------------+------------+

| Speakers    | 25.32      |

+-------------+------------+

Raz jeszcze przeanalizujmy wykonanie instrukcji przez MySQL:

  1. Najpierw zostaje wykonana wewnętrzna instrukcja SELECT max(sell_price) FROM item.
  2. W jej wyniku otrzymujemy najwyższą cenę sprzedaży (25,32). Następnie wykonywana jest instrukcja SELECT description, sell_price FROM item WHERE sell_price=25,32;, po której zrealizowaniu otrzymamy nazwę najdroższego towaru.

Podzapytania powiązane

Wykonanie poprzednio opisanych podzapytań sprowadza się do wykonania wewnętrznej instrukcji SELECT i zwrócenia obliczonego wyniku do zapytania zewnętrznego.

Podzapytania drugiego typu, podzapytania powiązane, wykonywane są według innego schematu. W tym wypadku podzapytanie wykonywane jest dla każdego wiersza wyniku zapytania zewnętrznego i może być z nim porównywane. Podzapytanie powiązane jest przykładem dynamicznego złączenia wyniku zapytania z każdym kolejnym wierszem wyniku zapytania zewnętrznego.

Podzapytanie powiązane jako przykład dynamicznego złączenia można łatwo rozpoznać po tym, że kolumna (kolumny) wyniku podzapytania jest porównywana z kolumną (kolumnami) wyniku zapytania zewnętrznego. Niezależne wykonanie wewnętrznego zapytania jest w tym przypadku niemożliwe.

Podzapytania jako złączenia

Z opisu wykonywania przez serwer baz danych podzapytań powiązanych wynika, że mogą być one używane jako funkcjonalne odpowiedniki złączenia tabel. Takie zastosowanie podzapytania pokazuje przykład z listingu 7.6.

Listing 7.6. Zapytanie zwracające dane o towarach, których mamy mało w magazynie. Warunek sprawdzany jest przez podzapytanie

SELECT * FROM item

WHERE item_id IN (SELECT item_id FROM stock WHERE quantity <3);

+---------+-------------+------------+------------+

| item_id | description | cost_price | sell_price |

+---------+-------------+------------+------------+

| 2       | Rubik Cube  | 7.45       | 11.49      |

| 10      | Carrier Bag | 0.01       | 0.00       |

+---------+-------------+------------+------------+

W tym przypadku podzapytanie zostało użyte do wybrania identyfikatorów tych produktów, których w magazynie jest mniej niż trzy, a zapytanie zewnętrzne odczytało dane o produktach o znalezionych identyfikatorach. Równoważne zapytanie wykorzystujące złączenie pokazuje listing 7.7.

Listing 7.7. Złączenie zwracające te same wyniki

SELECT * FROM item

JOIN stock USING (item_id)

WHERE quantity <3;

+---------+-------------+------------+------------+----------+

| item_id | description | cost_price | sell_price | quantity |

+---------+-------------+------------+------------+----------+

| 2       | Rubik Cube  | 7.45       | 11.49      | 2        |

| 10      | Carrier Bag | 0.01       | 0.00       | 1        |

+---------+-------------+------------+------------+----------+

Podsumujmy: podzapytania powiązane wykonywane są podobnie jak złączenia — w obu przypadkach serwer bazodanowy porównuje każdy wiersz jednej tabeli z każdym wierszem drugiej tabeli. Jednak do porównania używane są inne operatory, przede wszystkim w podzapytaniach można zastosować operator IN.

Obie techniki mają też unikatowe zalety:

  1. W podzapytaniach można używać funkcji grupujących i porównywać ich wyniki z wartościami pojedynczych wierszy.

  2. Wyniki złączeń mogą zawierać dane ze wszystkich połączonych tabel.

Podzapytania z operatorem EXISTS

Za pomocą operatorów EXISTS i NOT EXISTS możemy ograniczyć wynik zapytania zewnętrznego do wierszy odpowiednio zwróconych (lub nie) przez podzapytanie powiązane. Operator EXIST zwraca wartość logiczną 1 (prawda), jeżeli określony wiersz istnieje w wyniku podzapytania; operator NOT EXISTS — jeżeli wynik podzapytania nie zawiera określonego wiersza.

Podzapytania z operatorem EXISTS lub NOT EXISTS nie zwracają żadnych danych, a jedynie wartości 1 lub 0. Operator EXISTS jest jednym z dwóch używanych w języku SQL operatorów, które zwracają prawdę lub fałsz, nigdy wartość nieznaną (drugim jest IS NULL, służący do sprawdzania, czy dana wartość jest nieznana; jeżeli tak, zwraca prawdę, w przeciwnym razie zwraca fałsz). Wynika to ze sposobu jego działania — wewnętrzne zapytanie może albo zwrócić przynajmniej jedną wartość (wtedy EXIST będzie prawdziwy), albo nie zwrócić żadnych danych (wtedy EXISTS będzie fałszywy).

Pokazane na listingu 7.8 podzapytanie powiązane wyświetla wszystkie informacje o klientach mających to samo imię i nazwisko, ale różne ID, a więc dane, które pomyłkowo mogły zostać kilkakrotnie wprowadzone do bazy.

Listing 7.8. Przykład zastosowania podzapytań do wyszukiwania duplikatów danych

SELECT customer_id, fname, lname FROM customer AS T1

WHERE EXISTS (SELECT customer_id

FROM customer AS T2

WHERE T1.fname=T2.fname AND T1.customer_id<>T2.customer_id);

+-------------+---------+--------+

| customer_id | fname   | lname  |

+-------------+---------+--------+

| 7           | Richard | Stones |

| 12          | Richard | Neill  |

+-------------+---------+--------+

Wykonując tę instrukcję, MySQL odczytuje jeden wiersz z tabeli customer i sprawdza, czy w całej tabeli nie ma zapisanych danych o kliencie, który miałby to samo imię, ale inny identyfikator. Jeżeli osoba o tym samym imieniu, ale różnym identyfikatorze zostanie znaleziona, operator EXISTS zwraca prawdę i dane klienta dołączane są do wyniku. Następnie odczytywany jest drugi wiersz z tabeli >customer i cały proces powtarza się aż do sprawdzenia wszystkich wierszy.

W tym przypadku nie wystarczyło pogrupować dane według nazwisk i wyeliminować z wyniku grupy liczące dokładnie jeden wiersz (a więc te, w których nazwisko było unikatowe) — w ten sposób nie jesteśmy w stanie sprawdzić, czy osoby o tym samym nazwisku mają różne identyfikatory (listing 7.9).

Listing 7.9. Proste wyszukanie powtórzonych wartości we wskazanej kolumnie

SELECT COUNT(customer_id), lname

FROM customer

GROUP BY lname

HAVING COUNT(customer_id)>1;

+--------------------+---------+

| COUNT(customer_id) | lname   |

+--------------------+---------+

| 3                  | Matthew |

| 2                  | Neill   |

| 4                  | Stones  |

+--------------------+---------+

Podzapytania zwracające jeden wiersz

Podzapytania tego typu można traktować jak zwykłe wyrażenia. W szczególności podzapytania tego typu mogą być używane ze standardowymi operatorami porównań. Dlatego, jeśli chcemy znaleźć, powiedzmy, te towary, które zostały kupione za cenę niższą od średniej ceny zakupu towarów, należy wykonać polecenie (listing 7.10):

Listing 7.10. Kolejny przykład zastosowania podzapytania jako wyrażenia. MySQL najpierw obliczy średnią cenę zakupu towarów, a potem wykorzysta ją do sprawdzenia warunku zewnętrznego zapytania

SELECT description

FROM item

WHERE cost_price < (SELECT AVG(cost_price)

FROM item);

+-------------+

| description |

+-------------+

| Linux CD    |

| Tissues     |

| Toothbrush  |

| Roman Coin  |

| Carrier Bag |

+-------------+

Podzapytania zwracające wiele wierszy

Przyjrzyjmy się poniższemu podzapytaniu (listing 7.11).

Listing 7.11. Podzapytanie zwracające dane o zamówieniach złożonych przez klienta o podanym nazwisku

SELECT *

FROM orderinfo

WHERE customer_id = (SELECT customer_id

FROM customer

WHERE lname = 'Hudson');

+--------------+-------------+-------------+--------------+----------+

| orderinfo_id | customer_id | date_placed | date_shipped | shipping |

+--------------+-------------+-------------+--------------+----------+

| 3            | 15          | 2000-09-02  | 2000-09-12   | 3.99     |

+--------------+-------------+-------------+--------------+----------+

Co by było, gdyby zostało ono użyte do odczytania zamówień złożonych przez klienta o nazwisku Stones? Ponieważ wewnętrzne zapytanie zwróciłoby listę wartości (mamy kilku klientów o takim nazwisku), a nie pojedynczy wiersz, MySQL nie mógłby poprawnie obliczyć wyniku zapytania zewnętrznego (niemożliwe jest sprawdzenie, czy identyfikator klienta jest równy zbiorowi wartości) i zamiast listy zamówień otrzymalibyśmy komunikat o błędzie. Przekształceniem tej instrukcji tak, aby podzapytanie mogło zwrócić dowolną liczbę wartości (włączając w to 0), a przy tym działało zgodnie z naszymi oczekiwaniami, zajmiemy się w kolejnym punkcie.

Podzapytania zwracające listę wartości mogą być wykorzystane w wyrażeniach typu IN, NOT IN, EXISTS oraz łącznie ze słowami kluczowymi ANY lub ALL.

Podzapytania z wyrażeniem IN

Wyrażenie IN jest wykorzystywane do sprawdzenia, czy wartość należy do pewnego zbioru. Podzapytanie może być zastosowane do wybrania tego zbioru wartości. Przekształćmy naszą instrukcję tak, aby w podzapytaniu użyć operatora IN (listing 7.12).

Listing 7.12. Lepszy, bo bardziej uniwersalny sposób sformułowania podzapytania

SELECT *

FROM orderinfo

WHERE customer_id IN (SELECT customer_id

FROM customer

WHERE lname = 'Stones');

+--------------+-------------+-------------+--------------+----------+

| orderinfo_id | customer_id | date_placed | date_shipped | shipping |

+--------------+-------------+-------------+--------------+----------+

| 2            | 8           | 2000-06-23  | 2000-06-23   | 0.00     |

| 5            | 8           | 2000-07-21  | 2000-07-24   | 0.00     |

+--------------+-------------+-------------+--------------+----------+

Podzapytania z wyrażeniem NOT IN

Wyniki podzapytań mogą być również porównywane za pomocą operatora NOT IN. Wartość logiczna takiego wyrażenia jest prawdziwa, gdy wartość testowana nie należy do listy wartości zwróconych przez podzapytanie (listing 7.13).

Nie zaleca się stosować wyrażeń NOT IN w podzapytaniach z powodu ich niskiej wydajności. W większości wypadków można zamiast tego użyć zapytania wykorzystującego złączenie zewnętrzne.

Listing 7.13. Podzapytanie zwracające dane klientów, którzy nie złożyli żadnego zamówienia

SELECT fname, lname

FROM customer

WHERE customer_id NOT IN (SELECT customer_id FROM orderinfo);

+-----------+---------+

| fname     | lname   |

+-----------+---------+

| Jenny     | Stones  |

| Andrew    | Stones  |

| Adrian    | Matthew |

| Simon     | Cozens  |

| Neil      | Matthew |

| Richard   | Stones  |

| Christine | Hickman |

| Mike      | Howard  |

| Dave      | Jones   |

| Richard   | Neill   |

| Bill      | Neill   |

|           | Wolski  |

+-----------+---------+

Podzapytania z wyrażeniem EXIST

W przypadku podzapytań czasami chcemy jedynie sprawdzić, czy wiersz spełniający podane warunki istnieje w bazie danych. Prostą metodą sprawdzenia, czy dany wiersz występuje w podanej tabeli, jest użycie znanego nam już wyrażenia EXIST (listing 7.14).

Ponieważ operator EXIST zwraca wartość logiczną True, gdy wewnętrzne podzapytanie zwróci jakikolwiek wynik, a False, gdy zwróci pusty zbiór, nie ma znaczenia, co znajdzie się w klauzuli SELECT wewnętrznego podzapytania.

Listing 7.14. Zapytania zwracają informacje o towarach, które mają przypisane kody

SELECT *

FROM item

WHERE EXISTS (SELECT *

FROM barcode

WHERE item.item_id = barcode.item_id);

+---------+---------------+------------+------------+

| item_id | description   | cost_price | sell_price |

+---------+---------------+------------+------------+

| 1       | Wood Puzzle   | 15.23      | 21.95      |

| 2       | Rubik Cube    | 7.45       | 11.49      |

| 3       | Linux CD      | 1.99       | 2.49       |

| 4       | Tissues       | 2.11       | 3.99       |

| 5       | Picture Frame | 7.54       | 9.95       |

| 6       | Fan Small     | 9.23       | 15.75      |

| 7       | Fan Large     | 13.36      | 19.95      |

| 8       | Toothbrush    | 0.75       | 1.45       |

| 9       | Roman Coin    | 2.34       | 2.45       |

| 11      | Speakers      | 19.73      | 25.32      |

+---------+---------------+------------+------------+

Podzapytania z wyrażeniem NOT EXIST

Jeżeli interesuje nas brak jakichś danych, a nie ich istnienie, możemy sprawdzić to za pomocą wyrażenia NOT EXIST. Aby za pomocą tego operatora znaleźć towary, które nie mają przypisanego kodu, napiszemy (listing 7.15):

Listing 7.15. Lista towarów bez kodów

SELECT *

FROM item

WHERE NOT EXISTS (SELECT *

FROM barcode

WHERE item.item_id = barcode.item_id);

+---------+-----------------+------------+------------+

| item_id | description     | cost_price | sell_price |

+---------+-----------------+------------+------------+

| 10      | Carrier Bag     | 0.01       | 0.00       |

| 12      | SQL Server 2005 | NULL       | NULL       |

+---------+-----------------+------------+------------+

Podzapytania z wyrażeniem ANY

W przypadku użycia operatora ANY wartość bieżącego wiersza zwróconego przez zapytanie zewnętrzne jest porównywana z każdą wartością zwróconą przez zapytanie wewnętrzne. Wystarczy, że jedno z tych porównań będzie prawdziwe (czyli wiersz z wyniku zapytania zewnętrznego przynajmniej raz spełni warunek logiczny), i całe porównanie będzie traktowane jako spełnione. Innymi słowy, wiersz zostanie wybrany, jeżeli wyrażenie jest zgodne z co najmniej jedną wartością zwróconą przez zapytanie wewnętrzne. Jeżeli jest to warunek równościowy, operator ANY z reguły jest zastępowany operatorem (listing 7.16).

Listing 7.16. Podzapytanie zwracające identyfikatory towarów, które zostały zamówione w ilości równej zapasowi tego towaru w magazynie

SELECT item_id

FROM stock

WHERE quantity IN

(SELECT quantity

FROM orderline);

+---------+

| item_id |

+---------+

|       2 |

|      10 |

+---------+

Ten sam wynik możemy uzyskać, sprawdzając, czy zapas któregoś towaru jest równy ilości sprzedanego towaru w ramach dowolnego zamówienia (listing 7.17).

Listing 7.17. Gdybyśmy skasowali operator ANY, to próba wykonania podzapytania skończyłaby się błędem spowodowanym tym, że wewnętrzne zapytanie zwraca więcej niż jeden wiersz, a my próbujemy porównać jego wynik za pomocą operatora =

SELECT item_id

FROM stock

WHERE quantity = ANY

(SELECT quantity

FROM orderline);

+---------+

| item_id |

+---------+

|       2 |

|      10 |

+---------+

Aby z pomocą operatora ANY wybrać towary, których cena zakupu jest większa niż cena zakupu jakiegokolwiek towaru, należy wykonać instrukcję z listingu 7.18.

Listing 7.18. Lista wszystkich towarów z wyjątkiem najtaniej kupionego. Należy zwrócić uwagę, że na liście nie znalazł się również towar o nieznanej cienie zakupu

SELECT *

FROM item

WHERE cost_price > ANY

(SELECT  cost_price

FROM item);

+---------+---------------+------------+------------+

| item_id | description   | cost_price | sell_price |

+---------+---------------+------------+------------+

| 1       | Wood Puzzle   | 15.23      | 21.95      |

| 2       | Rubik Cube    | 7.45       | 11.49      |

| 3       | Linux CD      | 1.99       | 2.49       |

| 4       | Tissues       | 2.11       | 3.99       |

| 5       | Picture Frame | 7.54       | 9.95       |

| 6       | Fan Small     | 9.23       | 15.75      |

| 7       | Fan Large     | 13.36      | 19.95      |

| 8       | Toothbrush    | 0.75       | 1.45       |

| 9       | Roman Coin    | 2.34       | 2.45       |

| 11      | Speakers      | 19.73      | 25.32      |

+---------+---------------+------------+------------+

Podzapytania z wyrażeniem ALL

Operator ALL jest prawdziwy, jeżeli wszystkie dane zwrócone przez wewnętrzne zapytanie spełniają poprzedzający go warunek logiczny. Pozwala on np. odczytać nazwy towarów droższych niż jakikolwiek towar, którego mamy w magazynie więcej niż 5 sztuk (listing 7.19).

Listing 7.19. Wewnętrzne zapytanie zwróciło ceny wszystkich towarów, których w magazynie jest więcej niż 5 sztuk; najdroższy z takich towarów kosztował 21,95, a więc operator ALL zwrócił dane tylko tych towarów, których cena przekracza 21,95

SELECT description, sell_price

FROM item

WHERE sell_price > ALL

(SELECT sell_price

FROM item

JOIN stock USING (item_id)

WHERE quantity >5 )

ORDER BY sell_price;

+----------------------+------------+

| description          | sell_price |

+----------------------+------------+

| Speakers             |      25.32 |

| SQL. Praktyczny kurs |      35.99 |

+----------------------+------------+

Operator ALL nie jest w praktyce używany do porównań równościowych. Warunek = ALL oznacza równy ze wszystkimi danymi zwróconymi przez wewnętrzne zapytanie, czyli w rzeczywistości identyczny z jego wynikiem. Wykorzystuje się go natomiast do wyszukiwania danych różnych od wszystkich danych zwróconych przez wewnętrzne zapytanie.

W przypadku użycia operatora ALL warunek musi być spełniony przez wszystkie wartości wybrane w podzapytaniu. Innymi słowy, jeśli wykonujemy porównanie oparte na równości, to wartość z lewej strony równania musi być równa każdej wartości wyniku podzapytania, żeby wynik całego porównania też był prawdziwy (listing 7.20).

Listing 7.20. Dane najdrożej kupionego towaru

SELECT *

FROM item

WHERE cost_price >= ALL

(SELECT  cost_price

FROM item);

+---------+-------------+------------+------------+

| item_id | description | cost_price | sell_price |

+---------+-------------+------------+------------+

| 11      | Speakers    | 19.73      | 25.32      |

+---------+-------------+------------+------------+

Zagnieżdżanie podzapytań

Możliwe jest wielokrotne zagnieżdżanie podzapytań — podzapytanie może być zagnieżdżone zarówno w standardowym zapytaniu, jak i w innym podzapytaniu. Jedyne ograniczenie liczby poziomów zagnieżdżeń wynika z wydajności zapytania. W praktyce zamiast zagnieżdżać podzapytania, znacznie lepiej jest rozbić je na osobne instrukcje — czas ich wykonania przez MySQL będzie wielokrotnie krótszy.

Przypuśćmy, że chcemy za pomocą podzapytania odczytać nazwiska i imiona osób, które kiedykolwiek kupiły dany produkt.

  1. Najpierw musimy odczytać identyfikator produktu o podanej nazwie.
  2. Następnie numery zamówień, w ramach których produkt o tym identyfikatorze został sprzedany.
  3. Na podstawie identyfikatorów zamówień odczytamy, łącząc odpowiednie tabele, imiona i nazwiska osób, które je złożyły (listing 7.21).

Listing 7.21. Zagnieżdżone zapytanie zwracające dane osób, które kiedykolwiek kupiły wybrany produkt

SELECT fname, lname

FROM customer

JOIN orderinfo USING (customer_id)

WHERE orderinfo_id IN

(SELECT orderinfo_id

FROM orderline

WHERE item_id IN

 (SELECT item_id

  FROM item

    WHERE description = 'Tissues'));

+-------+---------+

| fname | lname   |

+-------+---------+

| Alex  | Matthew |

| Anna  | Stones  |

+-------+---------+

 

PHP, MySQL i MVC. Tworzenie witryn WWW opartych na bazie danych

PHP, MySQL i MVC.
Tworzenie witryn WWW
opartych na bazie danych

PHP i MySQL. Tworzenie stron WWW. Vademecum profesjonalisty.

PHP i MySQL. Tworzenie stron WWW.
Vademecum profesjonalisty.

PHP i MySQL. Projekty do wykorzystania

PHP i MySQL.
Projekty do wykorzystania

Nowości Helionu

Statystyki

Użytkowników : 766
Artykułów : 513
Zakładki : 28
Odsłon : 15342695