Kurs języka HTML i CSS

Poradnik webmastera

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

Pobieranie danych z wielu tabel i łączenie wyników zapytań

Email Drukuj PDF

W tym odcinku nauczysz się odczytywać dane zapisane w różnych tabelach, poznasz różnice pomiędzy złączeniem wewnętrznym a złączeniami zewnętrznymi i dowiesz się, jak przeprowadzać na tabelach operacje na zbiorach, znane z lekcji matematyki.

Wprowadzenie

Cechą charakterystyczną relacyjnych baz danych jest przechowywanie informacji podzielonych między wiele tabel. W wielu przypadkach w trakcie wyszukiwania informacji w bazie danych okazuje się, że potrzebne dane przechowywane są w kilku tabelach. Aby sensownie połączyć w jednym zapytaniu dane z wielu tabel, wymagane jest ich złączenie (ang. Join). Złączenie tabel możemy traktować jak opisaną poniżej operację (w rzeczywistości serwery baz danych optymalizują łączenie tabel).

Pierwszym etapem jest obliczenie wyniku iloczynu kartezjańskiego łączonych tabel — kombinacji wszystkich wierszy z pierwszej tabeli z wszystkimi wierszami z drugiej tabeli. Jeśli każda tabela zawiera tylko jeden wiersz, to wynik iloczynu kartezjańskiego też będzie miał jeden wiersz. W przypadku tabel o 5 wierszach wynik iloczynu kartezjańskiego wynosi 25 wierszy. Iloczyn kartezjański trzech tabel o odpowiednio 30, 100 i 10 wierszach daje w wyniku tabelę z 30 000 wierszy. Ten ogromny zbiór stanowi podstawę dalszego wykonywania zapytania. Przede wszystkim usuwane są z niego wiersze niespełniające warunku złączenia. Dzięki temu pozbywamy się ogromnej liczby powtórzonych i bezsensownych kombinacji danych. Kolejny krok polega na wykonaniu ograniczeń wynikających z klauzul WHERE i HAVING. Wszystkie wiersze, które nie spełniają określonych w nich warunków, są odrzucane. Końcowym etapem jest wybranie z tabeli kolumn zawartych w klauzuli SELECT i wykonanie odpowiedniej projekcji.

Z reguły łączy się tabele na podstawie wartości wspólnego atrybutu, na przykład wartości pary klucz podstawowy-klucz obcy. W takim przypadku musimy użyć jednoznacznego identyfikatora obiektu (kolumny). Ponieważ nazwy kolumn zawierających klucz podstawowy i obcy najczęściej są takie same, musimy poprzedzać nazwy kolumn nazwami tabel. Możemy poprawić czytelność zapytania, stosując aliasy dla nazw tabel.

Tabele łączymy zgodnie z następującymi wskazówkami:

1.                   Staramy się łączyć tabele za pomocą kolumn przechowujących parę kluczy podstawowy-klucz obcy.

2.                   Do złączenia używamy całych kluczy podstawowych tabel. Jeżeli dla jakiejś tabeli zdefiniowano złożony (składający się z kilku atrybutów) klucz podstawowy, łącząc taką tabelę, odwołujemy się do całego klucza.

3.                   Łączymy obiekty za pomocą kolumn tego samego typu.

4.                   Poprzedzamy nazwy kolumn aliasem nazwy obiektu źródłowego, nawet jeżeli nazwy te są unikatowe — w ten sposób poprawimy czytelność zapytania.

5.                   Ograniczamy liczbę łączonych obiektów do niezbędnego minimum.

Złączenie naturalne

Wynikiem złączenia naturalnego jest zbiór wierszy łączonych tabel; dla tych wierszy wartości kolumn określonych jako warunek złączenia są takie same. Ponieważ w relacyjnych bazach danych informacje są podzielone pomiędzy tabele zawierające dane o obiektach jednego typu, złączenie naturalne jest najczęściej wykorzystywanym (i domyślnym) złączeniem obiektów.

W przykładowej bazie danych informacje o klientach, zamówieniach, towarach i stanach magazynowych przechowywane są w powiązanych ze sobą tabelach. Dlatego, żeby odczytać na przykład daty składania zamówień przez poszczególnych klientów, musimy odwołać się do dwóch tabel — nazwę klienta odczytamy z tabeli customer, a datę złożenia zamówienia — z tabeli orderinfo. Przy czym z reguły nie chodzi nam o uzyskanie poniższego wyniku (listing 4.1).

Listing 4.1. Odwołując się do wielu tabel, powinniśmy określić warunek złączenia, inaczej wynik będzie zawierał wszystkie kombinacje wierszy wymienionych tabel (iloczyn kartezjański). W tym przypadku tabela customer liczy 16, a tabela orderinfo 5 wierszy

SELECT lname, date_placed

FROM customer, orderinfo;

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

| lname   | date_placed |

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

| Stones  | 2000-03-13  |

| Stones  | 2000-06-23  |

| Stones  | 2000-09-02  |

| Stones  | 2000-09-03  |

| Stones  | 2000-07-21  |

| Stones  | 2000-03-13  |

| Stones  | 2000-06-23  |

| Stones  | 2000-09-02  |

| Stones  | 2000-09-03  |

| Stones  | 2000-07-21  |

| Matthew | 2000-03-13  |

| Wolski  | 2000-07-21  |

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

80 rows in set

Poprawnie napisana instrukcja powinna zwrócić nam tylko daty zamówień złożonych przez danego klienta. Żeby to osiągnąć, musimy określić warunek złączenia, czyli poinformować serwer baz danych, co łączy zapisane w obu tabelach dane. W tym przypadku jest to identyfikator klienta — zwróć uwagę, że kolumna customer_id występuje w obu tabelach, czyli na podstawie tego identyfikatora jesteśmy w stanie sensownie połączyć informacje o klientach z informacjami o zamówieniach (listing 4.2).

Listing 4.2. Poprawne zapytanie zwracające nazwiska klientów i daty złożenia przez nich zamówień

SELECT lname, date_placed

FROM customer INNER JOIN orderinfo

ON customer.customer_id = orderinfo.customer_id;

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

| lname   | date_placed |

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

| Matthew | 2000-03-13  |

| Stones  | 2000-06-23  |

| Hudson  | 2000-09-02  |

| Hendy   | 2000-09-03  |

| Stones  | 2000-07-21  |

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

Złączenie naturalne pozwoli nam również odczytać kody poszczególnych towarów. Jednak tym razem użyjemy nieco innej składni; zamiast dość „rozwlekłej” klauzuli ON wykorzystamy jej bardziej zwięzły odpowiednik — klauzulę USING (listing 4.3).

Listing 4.3. Złączenie naturalne za pomocą klauzuli USING

SELECT description, barcode_ean

FROM barcode INNER JOIN item

USING (item_id);

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

| description   | barcode_ean   |

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

| Wood Puzzle   | 6241527836173 |

| Rubik Cube    | 6241574635234 |

| Linux CD      | 6241527746363 |

| Linux CD      | 6264537836173 |

| Tissues       | 7465743843764 |

| Picture Frame | 3453458677628 |

| Fan Small     | 6434564564544 |

| Fan Large     | 8476736836876 |

| Toothbrush    | 6241234586487 |

| Toothbrush    | 9473625532534 |

| Toothbrush    | 9473627464543 |

| Roman Coin    | 4587263646878 |

| Speakers      | 2239872376872 |

| Speakers      | 9879879837489 |

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

Klauzule ON i USING są różnymi sposobami na podanie warunku złączenia, czyli wskazania wspólnych kolumn łączonych tabel.

Łączenie wielu tabel

Nie zawsze interesujące nas informacje znajdują się w bezpośrednio ze sobą połączonych tabelach — częściej będą one albo rozrzucone pomiędzy wiele tabel, albo zapisane w niezwiązanych ze sobą tabelach. W takich przypadkach, żeby uniknąć błędu z listingu 4.1, musimy prawidłowo złączyć wszystkie pośrednie tabele.

Język SQL umożliwia wybieranie danych z dowolnej liczby tabel. Serwer baz danych połączy dwie z wymienionych tabel, tak uzyskany zbiór pośredni połączy z trzecią tabelą, tak uzyskany zbiór pośredni połączy z czwartą tabelą itd.

Żeby na przykład odczytać nazwiska klientów (tabela customer) i nazwy kupionych przez nich towarów (tabela item), musimy:

  1. Złączyć tabele customer i orderinfo.
  2. Złączyć otrzymany zbiór pośredni z tabelą orderline.
  3. Złączyć otrzymany zbiór pośredni z tabelą item — tylko w ten sposób będziemy w stanie określić, który klient zamawiał dany towar (listing 4.4).

Listing 4.4. Złączenie naturalne czterech tabel

SELECT lname, description

FROM customer INNER JOIN orderinfo

USING (customer_id) INNER JOIN orderline

USING (orderinfo_id) INNER JOIN item

USING (item_id);

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

| lname   | description   |

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

| Matthew | Tissues       |

| Matthew | Fan Large     |

| Matthew | Roman Coin    |

| Stones  | Wood Puzzle   |

| Stones  | Tissues       |

| Stones  | Fan Large     |

| Stones  | Carrier Bag   |

| Stones  | Wood Puzzle   |

| Stones  | Linux CD      |

| Hendy   | Picture Frame |

| Hudson  | Wood Puzzle   |

| Hudson  | Rubik Cube    |

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

Liczbę warunków użytych do łączenia tabel można wyliczyć ze wzoru: minimalna liczba warunków = liczba tabel –1.

Aliasy

Jeżeli zapytanie odwołuje się tylko do jednej tabeli, poprzedzanie nazw kolumn nazwą tej tabeli jest niepotrzebne. Wynika to z tego, że tabela nie może mieć kilku kolumn o tej samej nazwie, a więc umieszczane w klauzulach instrukcji SELECT nazwy są jednoznaczne.

W przypadku zapytań odwołujących się do wielu tabel sytuacja wygląda zupełnie inaczej. Skoro kolumny o tej samej nazwie mogą występować w różnych tabelach, serwer bazodanowy nie jest w stanie tylko na podstawie ich nazw określić, do której z nich chcieliśmy się odwołać.

Rozwiązaniem tego problemu mogłoby być poprzedzanie nazw kolumn nazwami ich tabel (listing 4.5).

Listing 4.5. Poprzedzając nazwy kolumn nazwami tabel, nie tylko poprawimy czytelność zapytania, ale również je ujednoznacznimy

SELECT item.description, stock.quantity

FROM stock INNER JOIN item

USING (item_id);

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

| description   | quantity |

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

| Wood Puzzle   |       12 |

| Rubik Cube    |        2 |

| Tissues       |        8 |

| Picture Frame |        3 |

| Fan Large     |        8 |

| Toothbrush    |       18 |

| Carrier Bag   |        1 |

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

Jeżeli nazwy tabel są dość długie, ich ciągłe powtarzanie jest niepraktyczne. Na szczęście (tak jak w klauzuli SELECT) w klauzuli FROM możliwe jest wykorzystywanie aliasów. Ale określone w klauzuli FROM aliasy nazw obowiązują w obrębie całej instrukcji SELECT, również w pierwszej klauzuli SELECT. Jeżeli zdefiniujemy alias dla tabeli, nie będziemy mogli w obrębie całej instrukcji posłużyć się oryginalną nazwą tabeli. Natomiast aliasy zdefiniowane w klauzuli SELECT obowiązują tylko w tej klauzuli i jeśli chcemy na przykład posortować dane według kolumny z aliasem, w klauzuli ORDER BY musimy posłużyć się oryginalną nazwą kolumny lub wyrażeniem (listing 4.6).

Listing 4.6. Zapytanie zwracające stan magazynowy każdego towaru. Tym razem nazwy tabel zostały zastąpione aliasami

SELECT i.description, s.quantity

FROM stock AS s INNER JOIN item AS i

USING (item_id);

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

| description   | quantity |

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

| Wood Puzzle   | 12       |

| Rubik Cube    | 2        |

| Tissues       | 8        |

| Picture Frame | 3        |

| Fan Large     | 8        |

| Toothbrush    | 18       |

| Carrier Bag   | 1        |

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

Używając aliasów nazw tabel, unikniemy trudnych do wykrycia błędów związanych ze sposobem, w jaki dany serwer bazodanowy sprawdza nazwy obiektów. Gdybyśmy pomylili się, wpisując nazwę kolumny, a w którejś ze złączonych tabel istniałaby kolumna o wprowadzonej przez nas nazwie, serwer nie zgłosiłby błędu, tylko odczytał dane z innej kolumny, niż chcieliśmy.

Należy zwrócić uwagę na kilka rzeczy:

  1. Po pierwsze, kolejność tabel w klauzuli FROM jest nieistotna.
  2. Po drugie, definiując alias dla kolumny, możemy (ale nie musimy) użyć słowa kluczowego AS.
  3. Po trzecie, nazwa kolumny może (ale nie musi) być poprzedzona aliasem tabeli.

Złączenia zewnętrzne

Złączenie naturalne eliminuje z wyniku niepasujące (niespełniające warunku złączenia) wiersze. To dobrze, bo w innym przypadku otrzymalibyśmy zawierający mnóstwo powtórzeń i niepotrzebnych danych iloczyn kartezjański. Ale z drugiej strony ten sam warunek złączenia usunął z wyniku rekordy niemające odpowiedników w łączonej tabeli. Czyli wynik poniższej instrukcji wcale nie musi zawierać danych wszystkich naszych klientów (listing 4.7).

Listing 4.7. W wyniku złączenia naturalnego nie znajdziemy nazwisk klientów, którzy nie złożyli przynajmniej jednego zamówienia

SELECT lname, date_placed

FROM customer INNER JOIN orderinfo

ON customer.customer_id = orderinfo.customer_id;

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

| lname   | date_placed |

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

| Matthew | 2000-03-13  |

| Stones  | 2000-06-23  |

| Hudson  | 2000-09-02  |

| Hendy   | 2000-09-03  |

| Stones  | 2000-07-21  |

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

Czasami chcielibyśmy uzyskać komplet danych z jednej tabeli, nawet jeżeli nie są one powiązane z danymi w innych tabelach. Umożliwia nam to złączenie zewnętrzne. Wynikiem lewo- lub prawostronnego złączenia zewnętrznego jest zbiór wierszy łączonych tabel, dla których wartości kolumn określonych jako warunek złączenia są takie same; zbiór ten uzupełniony jest pozostałymi wierszami z lewej lub prawej łączonej tabeli. Nieistniejące wartości reprezentowane są w wyniku złączenia przez wartość NULL (listing 4.8).

Listing 4.8. Kompletna, ale zawierająca powtórzenia lista nazwisk klientów i dat złożenia przez nich zamówień

SELECT lname, date_placed

FROM customer LEFT OUTER JOIN orderinfo

ON customer.customer_id = orderinfo.customer_id;

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

| lname   | date_placed |

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

| Stones  |             |

| Stones  |             |

| Matthew | 2000-03-13  |

| Matthew |             |

| Cozens  |             |

| Matthew |             |

| Stones  |             |

| Stones  | 2000-06-23  |

| Stones  | 2000-07-21  |

| Hickman |             |

| Howard  |             |

| Jones   |             |

| Neill   |             |

| Hendy   | 2000-09-03  |

| Neill   |             |

| Hudson  | 2000-09-02  |

| Wolski  |             |

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

Złączenia zewnętrzne stosowane są do wyświetlania kompletnych informacji o wszystkich obiektach danego typu, nawet jeżeli nie istnieją powiązane z nimi obiekty innego typu.

Wykorzystując wiadomości z poprzedniego odcinka kursu, możemy uporządkować tę listę (listing 4.9).

Listing 4.9. Finalna wersja instrukcji zwracającej nazwiska wszystkich klientów i daty składania przez nich zamówień

SELECT DISTINCT lname, date_placed

FROM customer LEFT JOIN orderinfo

USING (customer_id)

ORDER BY lname;

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

| lname   | date_placed |

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

| Cozens  |             |

| Hendy   | 2000-09-03  |

| Hickman |             |

| Howard  |             |

| Hudson  | 2000-09-02  |

| Jones   |             |

| Matthew |             |

| Matthew | 2000-03-13  |

| Neill   |             |

| Stones  |             |

| Stones  | 2000-07-21  |

| Stones  | 2000-06-23  |

| Wolski  |             |

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

Należy zwrócić uwagę na kilka rzeczy:

  1. Lewostronne złączenie zewnętrzne (LEFT JOIN) powoduje pozostawienie w wyniku niepasujących wierszy z pierwszej (lewej) tabeli. Ponieważ te wiersze nie mają swoich odpowiedników w złączonej tabeli, w kolumnach wyniku zwracającego dane z drugiej (prawej) tabeli wstawiona zostanie wartość NULL.
  2. Tak jak lewostronne złączenie zewnętrzne dodaje do wyniku zapytania niepasujące wiersze z tabeli, której nazwa jest podana jako pierwsza, tak prawostronne złączenie zewnętrzne (RIGHT JOIN) dodaje niepasujące wiersze z prawej tabeli.

Złączenie krzyżowe

Wynikiem złączenia krzyżowego jest iloczyn kartezjański łączonych obiektów. (Iloczynem kartezjańskim dwóch zbiorów jest zbiór zawierający wszystkie kombinacje ich elementów. Ponieważ tabele reprezentują zbiory, iloczynem kartezjańskim dwóch tabel jest tabela zawierająca wszystkie możliwe kombinacje wierszy złączonych tabel). W przeciwieństwie do innych typów złączeń w tym wypadku łączone tabele nie muszą mieć wspólnych kolumn. Złączenia tego typu są rzadko stosowane w znormalizowanych bazach danych i służą raczej do generowania danych testowych niż do wybierania danych (listing 4.10).

Listing 4.10. Wynikiem złączenia krzyżowego jest iloczyn kartezjański

SELECT *

FROM barcode CROSS JOIN stock;

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

| barcode_ean   | item_id | item_id | quantity |

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

| 2239872376872 | 11      | 1       | 12       |

| 2239872376872 | 11      | 2       | 2        |

| 2239872376872 | 11      | 4       | 8        |

| 2239872376872 | 11      | 5       | 3        |

...

| 9879879837489 | 11      | 10      | 1        |

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

98 rows in set (0.00 sec)

Złączenia nienaturalne

Przekonaliśmy się już, że wyniki zapytań nie muszą dokładnie odpowiadać odczytywanym z tabel danym. Tak jak za pomocą umieszczonych w klauzuli SELECT wyrażeń możemy zmienić wartość zwracanych przez zapytania danych, tak za pomocą złączenia nienaturalnego możemy wygenerować zbiór „przypadkowo” powiązanych ze sobą wierszy.

Nienaturalne złączenia równościowe

Złączenia równościowe w warunku złączenia zawierają operator =. Wyniki zapytań ze złączeniem równościowym zawierają te wiersze złączonych tabel, w których wartości użytych do złączenia kolumn są takie same. Ponieważ wartości różnych kluczy podstawowych są z reguły takie same (wynika to z tego, że wartości kluczy podstawowych często są automatycznie generowanymi przez serwery bazodanowe liczbami całkowitymi, prawie zawsze zaczynającymi się od jedynki), zapytanie pokazane na listingu 4.11 zwraca wynik, ale jest to wynik niepoprawny.

Listing 4.11. Przykład nienaturalnego (nieużywającego właściwych kluczy) złączenia równościowego

SELECT c.fname, o.date_placed

FROM customer AS c

JOIN orderinfo o

ON c.customer_id = o.orderinfo_id;

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

| fname  | date_placed |

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

| Jenny  | 2000-03-13  |

| Andrew | 2000-06-23  |

| Alex   | 2000-09-02  |

| Adrian | 2000-09-03  |

| Simon  | 2000-07-21  |

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

Nienaturalne złączenie nierównościowe

Powiązania tabel wykorzystujące dowolny, inny niż równość, operator nazywane są nierównościowymi (ang. Non-equi join). Tego typu złączenia z reguły są używane przy łączeniu tabeli z nią samą albo jako dodatkowe złączenie, obok złączenia równościowego. Samodzielne złączenie nierównościowe zwraca mało intuicyjne wyniki (listing 4.12).

Złączenia nierównościowe są bardzo rzadko używane — jednym z ich nielicznych zastosowań jest analiza danych polegająca na wyszukiwaniu istniejących między tymi danymi zależności. Ponieważ wyniki takich złączeń zawierają mnóstwo powtórzonych wierszy, złączenia nierównościowe z reguły występują razem ze złączeniami naturalnymi.

Listing 4.12. Przykład złączenia nierównościowego

SELECT lname, date_placed

FROM customer INNER JOIN orderinfo

ON customer.customer_id > orderinfo.customer_id

WHERE date_placed BETWEEN '2000=03-01' AND '2000-03-30';

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

| lname   | date_placed |

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

| Matthew | 2000-03-13  |

| Cozens  | 2000-03-13  |

| Matthew | 2000-03-13  |

| Stones  | 2000-03-13  |

| Stones  | 2000-03-13  |

| Hickman | 2000-03-13  |

| Howard  | 2000-03-13  |

| Jones   | 2000-03-13  |

| Neill   | 2000-03-13  |

| Hendy   | 2000-03-13  |

| Neill   | 2000-03-13  |

| Hudson  | 2000-03-13  |

| Wolski  | 2000-03-13  |

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

Zwróć uwagę na warunek w klauzuli WHERE. Żeby wybrać zamówienia z marca 2000 roku, należało określić przedział czasu.

Złączenie tabeli z nią samą

Złączenie tabeli z nią samą wykonywane jest w taki sam sposób, jak omawiane do tej pory złączenia różnych tabel. Chociaż serwery bazodanowe nie tworzą kopii złączonej tabeli, to wszystkie operacje przeprowadzane są tak, jakby dotyczyły dwóch identycznych tabel. Złączenie tabeli z nią samą stosujemy, kiedy chcemy wybrać rekordy z tabeli na podstawie wspólnych wartości atrybutów rekordów tej samej tabeli.

Złączenie tabeli z nią samą jest jedną z technik języka SQL, odpowiadającą użyciu zmiennych w proceduralnych językach programowania.

Przy takim łączeniu należy pamiętać o następujących zasadach:

1.                   Trzeba utworzyć różne aliasy dla łączonej tabeli i w ramach zapytania konsekwentnie odwoływać się do aliasów, a nie do nazwy tabeli.

2.                   Każdy rekord, w którym wartości atrybutu złączenia będą sobie równe, zostanie dodany do wyniku złączenia, co spowoduje powstanie duplikatów rekordów.

Złączenia tabeli z samą sobą są często wykorzystywane do rekurencyjnego odczytania danych, na przykład informacji o podwładnych (podwładny osoby X może być przełożonym osoby Y, która z kolei może być przełożonym osoby Z). W testowej bazie danych nie ma zapisanych takich zależności. Przykład z listingu 4.13 jest czysto szkoleniowy.

Listing 4.13. Złączenie tabeli z nią samą. Zwróć uwagę na liczbę powtórzonych wierszy

SELECT  l.customer_id, r.customer_id, l.lname, r.lname

FROM customer l INNER JOIN customer r

ON l.customer_id = r.customer_id;

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

| customer_id | customer_id | lname   | lname   |

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

| 1           | 1           | Stones  | Stones  |

| 2           | 2           | Stones  | Stones  |

| 3           | 3           | Matthew | Matthew |

| 4           | 4           | Matthew | Matthew |

| 5           | 5           | Cozens  | Cozens  |

| 6           | 6           | Matthew | Matthew |

| 7           | 7           | Stones  | Stones  |

| 8           | 8           | Stones  | Stones  |

| 9           | 9           | Hickman | Hickman |

| 10          | 10          | Howard  | Howard  |

| 11          | 11          | Jones   | Jones   |

| 12          | 12          | Neill   | Neill   |

| 13          | 13          | Hendy   | Hendy   |

| 14          | 14          | Neill   | Neill   |

| 15          | 15          | Hudson  | Hudson  |

| 16          | 16          | Wolski  | Wolski  |

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

Jak wyeliminować te powtórzenia? Na pewno nie pomoże w tym słowo kluczowe DISTINCT — przecież każdy wiersz zawiera niepowtarzalną kombinację danych. Rozwiązanie polega na dodaniu niesymetrycznego warunku (np. WHERE l.lname > r.lname), ale MySQL przy łączeniu tabeli z nią samą nie rozróżnia kolumn pochodzących z lewostronnie i prawostronnie złączonej tabeli, traktując je (niezgodnie ze standardem języka) jako te same. W efekcie dodanie takiego warunku wyeliminuje wszystkie wiersze. Z tego samego powodu serwer MySQL nie pozwala używać złączeń tabeli z nią samą do analizy danych — na przykład zapytanie z listingu 4.14 powinno zwrócić tylko dane zamówień o takim samym koszcie wysłania, a zwraca dane wszystkich zamówień.

Listing 4.14. Serwer MySQL nie rozróżnia obu kopii złączonej tabeli

SELECT T1.orderinfo_id, T1.shipping

FROM orderinfo AS T1 JOIN orderinfo AS T2

USING (orderinfo_id)

WHERE T1.shipping = T2.shipping;

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

| orderinfo_id | shipping |

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

|            1 |     2.99 |

|            2 |     0.00 |

|            3 |     3.99 |

|            4 |     2.99 |

|            5 |     0.00 |

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

Złączenie wyników (operator UNION)

Skoro tabele są specjalnymi zbiorami, to tak jak zbiory można je dodawać, odejmować i wyznaczać ich część wspólną. W wersji 5. MySQL obsługuje tylko jeden operator teoriomnogościowy — sumę.

Do zsumowania wierszy z dowolnej liczby tabel służy operator UNION. Załóżmy, że A i B są zbiorami — wtedy suma zbiorów A i B składa się z elementów obu zbiorów: A ∪ B={x: x ∈ A lub x ∈ B} (rysunek 4.1).

Rysunek 4.1. Na sumę dwóch tabel składają się wszystkie wiersze jednej tabeli i wszystkie wiersze drugiej tabeli

Za pomocą operatora UNION możemy dodać wyniki poszczególnych zapytań (czyli zwiększyć liczbę wierszy wyniku; złączenia JOIN zwiększały liczbę kolumn, złączenie UNION zwiększa liczbę wierszy). Łączone wyniki muszą składać się z takiej samej liczby kolumn, a poszczególne kolumny muszą być tego samego typu, poza tym konieczne jest, aby występowały one w tej samej kolejności w obu wynikach (listing 4.15).

Listing 4.15. Złączenie wyników dwóch prostych zapytań

SELECT fname

FROM customer

UNION

SELECT description

FROM item;

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

| fname           |

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

| Jenny           |

| Andrew          |

| Alex            |

| Adrian          |

| Simon           |

| Neil            |

| Richard         |

| Ann             |

| Christine       |

| Mike            |

| Dave            |

| Laura           |

| Bill            |

| David           |

|                 |

| Wood Puzzle     |

| Rubik Cube      |

| Linux CD        |

| Tissues         |

| Picture Frame   |

| Fan Small       |

| Fan Large       |

| Toothbrush      |

| Roman Coin      |

| Carrier Bag     |

| Speakers        |

| SQL Server 2005 |

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

Operatory teoriomnogościowe (takie jak UNION) automatycznie eliminują z wyniku powtarzające się wiersze, co odpowiada wykorzystaniu opcji DISTINCT w klauzuli SELECT. Jeżeli chcemy otrzymać listę zawierającą wszystkie wiersze z łączonych tabel, należy użyć słowa kluczowego ALL (listing 4.16).

Listing 4.16. Przykład pokazujący domyślne usuwanie duplikatów z wyników złączenia UNION

SELECT item_id

FROM item

UNION

SELECT item_id

FROM stock;

+---------+

| item_id |

+---------+

| 1       |

| 2       |

| 3       |

| 4       |

| 5       |

| 6       |

| 7       |

| 8       |

| 9       |

| 10      |

| 11      |

| 12      |

+---------+

SELECT item_id

FROM item

UNION ALL

SELECT item_id

FROM stock;

+---------+

| item_id |

+---------+

| 1       |

| 2       |

| 3       |

| 4       |

| 5       |

| 6       |

| 7       |

| 8       |

| 9       |

| 10      |

| 11      |

| 12      |

| 1       |

| 2       |

| 4       |

| 5       |

| 7       |

| 8       |

| 10      |

+---------+

Porządkowanie danych

Analogicznie do przypadku wybierania danych z pojedynczej tabeli czy złączenia naturalnego kilku tabel, wyniki złączenia wyników mogą być porządkowane za pomocą klauzuli ORDER BY. W tym przypadku dane będą sortowane według jednej z kolumn wchodzących w skład wyniku zapytania. Klauzula ORDER BY może zostać użyta tylko raz (a nie w każdej instrukcji SELECT). Ponieważ nazwy kolumn wchodzących w skład poszczególnych instrukcji SELECT mogą być różne, parametrem klauzuli ORDER BY jest nie nazwa kolumny, lecz jej pozycja (listing 4.17).

Listing 4.17. Uporządkowany wynik złączenia wyników zapytań

SELECT orderinfo_id, item_id

FROM orderline

UNION

SELECT orderinfo_id, customer_id

FROM orderinfo

ORDER BY 2;

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

| orderinfo_id | item_id |

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

| 2            | 1       |

| 3            | 1       |

| 5            | 1       |

| 3            | 2       |

| 5            | 3       |

| 1            | 3       |

| 1            | 4       |

| 2            | 4       |

| 4            | 5       |

| 1            | 7       |

| 2            | 7       |

| 2            | 8       |

| 5            | 8       |

| 1            | 9       |

| 2            | 10      |

| 4            | 13      |

| 3            | 15      |

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

 

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 : 15754483