W Oracle LEFT JOIN oraz innej rodzaje złączeń zewnętrznych (RIGHT JOIN, FULL OUTER JOIN) służą do łączenia ze sobą tabel i rekordów które nie spełniają warunku złączenia.

W tym kursie poznasz:

  • Czym jest złączenie zewnętrzne
  • Oracle LEFT JOIN – składania i przykłady
  • Oracle RIGHT JOIN – składania i przykłady
  • FULL OUTER JOIN – składania i przykłady
  • Składnię i przykłady złączenia zewnętrznego w Oracle wykorzystując operator (+)
  • Złączenia zewnętrzne wielu tabel
  • Podsumowanie

Ten kurs jest częścią cyklu Kurs SQL

Zapisz się na autorskie Szkolenie SQL

Przygotowanie bazy danych

Aby pokazać działanie operacji złączeń zewnętrznych w pierwszej kolejności musimy przygotować odpowiednio bazę danych. Aby lepiej zrozumieć ten wpis polecam zapoznać się z kursami:

Do testów przygotujemy testową bazę danych. Aby ją utworzyć wystarczy wykonać poniższy skrypt

CREATE TABLE PRODUCT (
    ID   NUMBER GENERATED ALWAYS AS IDENTITY,
    NAME VARCHAR2(100 CHAR),

    CONSTRAINT PRODUCT_ID_PK PRIMARY KEY (ID)
);

CREATE TABLE CUSTOMER (
    ID NUMBER GENERATED ALWAYS AS IDENTITY,
    FIRST_NAME VARCHAR2(100 CHAR),
    
    CONSTRAINT CUSTOMER_ID_PK PRIMARY KEY (ID)
);

CREATE TABLE ORDERS (
    PRODUCT_ID  NUMBER,
    CUSTOMER_ID NUMBER,
    ORDER_DATE  DATE,
    
    CONSTRAINT PRODUCT_ID_FK  FOREIGN KEY (PRODUCT_ID)  REFERENCES PRODUCT(ID),
    CONSTRAINT CUSTOMER_ID_FK FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(ID)
);

Diagram powyższej bazy wygląda jak poniżej

a do wypełnienia powyższej struktury danymi możesz wykonać poniższe inserty:

INSERT INTO PRODUCT (NAME) VALUES ('pomarańcze');
INSERT INTO PRODUCT (NAME) VALUES ('kiwi');
INSERT INTO PRODUCT (NAME) VALUES ('mango');

INSERT INTO CUSTOMER (FIRST_NAME) VALUES ('Jan');
INSERT INTO CUSTOMER (FIRST_NAME) VALUES ('Anna');
INSERT INTO CUSTOMER (FIRST_NAME) VALUES ('Marcin');

INSERT INTO ORDERS (PRODUCT_ID, CUSTOMER_ID, ORDER_DATE) VALUES (1, 1, SYSDATE);
INSERT INTO ORDERS (PRODUCT_ID, CUSTOMER_ID, ORDER_DATE) VALUES (2, 1, SYSDATE);
INSERT INTO ORDERS (PRODUCT_ID, CUSTOMER_ID, ORDER_DATE) VALUES (2, 2, SYSDATE);

COMMIT;

Po wykonaniu powyższych skryptów mamy bazę na której możemy uczyć się operacji LEFT JOIN, RIGHT JOIN oraz FULL OIUTER JOIN. W bardziej biznesowym skrócie możemy opowiedzieć, że baza zawiera:

  • Trzy produkty
  • Trzech klientów
  • Dwa zamówienia z czego:
    • Klient JAN zakupił pomarańcze i kiwi
    • Klient Anna zamówiła pomarańcze

Czym są złączenia zewnętrzne?

Przymnijmy najpierw, że złączenie wewnętrze (JOIN) służy łączeniu wierszy z dwóch tabel wg. warunku złączenia. Złączenie wewnętrzne zwróci tylko rekordy spełniające ten warunek z obu tabel. Oznacza to, że jeżeli któryś z rekordów nie spełnia warunku złączenie to nie zostanie zwrócony do wyniku zapytania.

Złączenie zewnętrzne w Oracle (LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN) również służy łączenia wierszy z dwóch tabel zgonie z warunkiem złączenia. Złączenie zewnętrzne jednak zwraca rekordy spełniające warunek złączenia ale również rekordy które ich nie spełniają. To które rekordy zostaną zwrócone poza rekordami z warunki zależy od typu złączenia.

Oracle FULL OUTER JOIN

Oracle FULL OUTER JOIN jest złączeniem zewnętrznym w którym do wyniku zapytania zwracane są rekordy spełniające warunek filtrowania oraz rekordy z obu tabel które nie spełniają warunku złączenia.

Zobaczmy więc jak wygląda składnia Oracle FULL OUTER JOIN:

SELECT
  kolumny
FROM tabela_1
FULL OUTER JOIN tabela_2 ON t1.id = t2.fk_id
where
  pozostale_warunki;

Kluczowa jest klauzula FULL OUTER JOIN ponieważ mówi ona, że do wyniku pobrane zostaną rekordy z obu tabel spełniających oraz nie spełniających warunek złączenia.

Przygotujmy więc przykładowe zapytanie na naszej bazie które pokaże wszystkich klientów oraz produkty w ramach zamówień. Ale w taki sposób aby zapytanie zwróciło również produkty które nie zostały nigdy zamówione oraz klientów którzy nigdy nic zamówili.

SELECT
    P.ID P_ID
  , P.NAME
  , O.PRODUCT_ID
  , O.ORDER_DATE
  , O.CUSTOMER_ID
  , C.ID C_ID
  , C.FIRST_NAME
FROM PRODUCT P
FULL OUTER JOIN ORDERS O ON O.PRODUCT_ID = P.ID
FULL OUTER JOIN CUSTOMER C ON C.ID = O.CUSTOMER_ID;

Oraz wynik zapytania:

Powyższy wynik na pierwszy rzut oka może nie być intuicyjny. Rozbierzmy więc powyższy wynik na mniejsze części i przeanalizujmy.

  • Rekordy 1,2,3 są rekordami spełniającymi wszystkie warunki złączenia tj. są to klienci którzy zamówili określone produkty
    • Jan zamówił pomarańcze i kiwi
    • Anna zamówiła kiwi
  • Rekord nr. 4 zwraca jedynie produkt mango. W pozostałych kolumnach widnieją wartości null ponieważ rekord ten nie spełnia żadnego z warunków złączenia
  • Rekord nr 5 zwraca jedynie klienta Marcina. Marcin nic nie zamówił dlatego też nie spełnia żadnego z warunków złączenia.

Gdybyśmy w warunku złączenia użyli zwykłego JOIN’a zamiast FULL OUTER JOIN baza danych zwróciłaby jedynie rekordy 1,2 i 3.

Oracle LEFT JOIN

Oracle LEFT JOIN jest złączeniem zewnętrznym w którym do wyniku zapytania zwracane są rekordy spełniające warunek filtrowania oraz rekordy z tabeli po “lewej”stronie niespełniające warunku złączenia.

Zobaczmy więc składnię:

SELECT
    kolumna_1
...
  , kolumna_x 
FROM tabela_1
LEFT JOIN tabela_2 ON t1.id = t2.fk_id
where
  pozostale_warunki;

W Oracle LEFT JOIN można powiedzieć, że po lewej stronie jest tabela główna do której dodajemy rekordy po prawej spełniające warunek złączenia.

Napiszmy więc przykładowe zapytanie wykorzystujące klauzulę LEFT JOIN:

SELECT
    P.ID P_ID
  , P.NAME
  , O.PRODUCT_ID
  , O.ORDER_DATE
  , O.CUSTOMER_ID
  , C.ID C_ID
  , C.FIRST_NAME
FROM PRODUCT P
LEFT JOIN ORDERS O ON O.PRODUCT_ID = P.ID
LEFT JOIN CUSTOMER C ON C.ID = O.CUSTOMER_ID;

oraz wynik zapytania:

Jak widzimy, powyższe zapytanie zwróciło wszystkie produkty spełniające warunek złączenia oraz jeden rekord (nr. 4) który nie spełnia warunku. Brakuje natomiast rekordu z klientem Marcinem który nie zamówił żadnego owocu. Dzieje się tak ponieważ po lewej mamy tabele “główną” i tylko z niej dobieramy rekordy nie spełniające warunku.

Oracle RIGHT JOIN

Oracle RIGHT JOIN jest złączeniem zewnętrznym w którym do wyniku zapytania zwracane są rekordy spełniające warunek filtrowania oraz rekordy z tabeli po “prawej”stronie niespełniające warunku złączenia.

zobaczmy więc składnie:

SELECT
    kolumna_1
...
  , kolumna_x 
FROM tabela_1
RIGHT JOIN tabela_2 ON t1.id = t2.fk_id
where
  pozostale_warunki;

Napiszmy więc zapytanie które zwróci nam wszystkich klientów wraz z zamówieniami ale niezależnie czy dokonali zamówienia czy nie. Zapytanie takie może wyglądać jak poniżej:

SELECT
    P.ID P_ID
  , P.NAME
  , O.PRODUCT_ID
  , O.ORDER_DATE
  , O.CUSTOMER_ID
  , C.ID C_ID
  , C.FIRST_NAME
FROM PRODUCT P
RIGHT JOIN ORDERS O ON O.PRODUCT_ID = P.ID
RIGHT JOIN CUSTOMER C ON C.ID = O.CUSTOMER_ID;

oraz wynik zapytania:

Jak widzimy po powyższym wyniku baza zwróciła wszystkich klientów spełniających warunek złączenia oraz dodała rekord nr 4 niespełniający warunku. Baza danych dodała rekord 4 ponieważ był po “prawej” stronie złączenia.

Jak widać po wynikach w bazie danych Oracle LEFT JOIN oraz RIGHT JOIN różnią się jedynie stroną. Osobiście bardzo często korzystam z LEFT JOIN natomiast prawie nigdy z RIGHT JOINa 🙂

Oracle (+)

W Oracle występuje jeszcze jeden operator jakim jest znak “(+)” do określenia LEFT, RIGHT joina. Jest to składnia występująca jedynie w bazie danych Oracle jednak jest równoważna z użyciem powyższych klauzul. Operatora (+) określamy do określenia strony “dodawanej”. Zobaczmy składnię:

SELECT
    kolumna_1
...
  , kolumna_x 
FROM tabela_1,
tabela_2 ON 
where
  t1.id (+) = t2.fk_id;

Oraz przykład:

SELECT
    P.ID P_ID
  , P.NAME
  , O.PRODUCT_ID
  , O.ORDER_DATE
  , O.CUSTOMER_ID
  , C.ID C_ID
  , C.FIRST_NAME
FROM PRODUCT P,
ORDERS O ,
CUSTOMER C 
WHERE O.PRODUCT_ID (+) = P.ID
  AND C.ID (+) = O.CUSTOMER_ID;

Oznaczenie (+) w powyższym przykładnie jest identyczne jakbyśmy użyli klauzuli LEFT JOIN. Zobaczmy wiec wynik:

Jak widzimy, baza zachowała się identycznie jakbyśmy użyli operatora LEFT JOIN. Operator (+) stawiamy po stronie dodawanej do tabeli. Oznacza to, że tabela przy której nie ma (+) zwrócić powinna wszystkie swoje rekordy.

Dodam, że stosowanie operatora (+) zamiast RIGHT/LEFT joina jest coraz rzadziej stosowane. W większości nowych systemów nie spotyka się już tego operatora jednak warto wiedzieć o jego istnieniu i warto znać zasady jego interpretacji 🙂

Złączenia zewnętrzne wielu tabel

Jednym z najczęstszych problemów dla osób zaczynających swoją przygodę z SQL i złączeniami są kombinacje złączeń zewnętrznych i wewnętrznych. Należy pamiętać, że jeżeli do tabeli X złączmy zewnętrznie tablę Y a do niej następnie wewnętrznie złączamy tabelę Z to finalnie wynik będzie jakbyśmy obie tabele złączyli wewnętrznie. Rozważmy więc poniższy przykład:

SELECT
    P.ID P_ID
  , P.NAME
  , O.PRODUCT_ID
  , O.ORDER_DATE
  , O.CUSTOMER_ID
  , C.ID C_ID
  , C.FIRST_NAME
FROM PRODUCT P
LEFT JOIN ORDERS O ON O.PRODUCT_ID = P.ID
JOIN CUSTOMER C ON C.ID = O.CUSTOMER_ID;

zobaczmy od razu wynik:

Zapytanie oraz jego wynik może nie być oczywisty na pierwszy rzut oka. Można w teorii stwierdzić, że baza wpierw zwróci do wyniku wszystkie rekordy z tabeli PRODUCT następnie doda do niej rekordy z tabeli ORDERS a następnie do tabeli ORDERS doda rekordy z tabeli CUSTOMER co w konsekwencji powinno zwrócić wszystkie produkty? Baza w ten sposób nie zadziała! Baza danych Oracle sprawdzi jakie są warunki złączenia i dopasuje do nich zapytanie.

Zastanówmy się dlaczego baza danych nie zwróci rekordu “Mango”. Rekord “Mango” co prawda spełnia warunek LEFT JOIN ORDERS O ON O.PRODUCT_ID = P.ID natomiast nie spełnia warunku C.ID = O.CUSTOMER_ID ponieważ nie zostało nigdy zamówione co oznacza, ze w O.CUSTOMER_ID będzie wartość null a null przyrównany do czegokolwiek zwraca null!

Powyższy schemat łączenia LEFT JOINA i JOIN jest często błędnie interpretowany i powoduje niejednokrotnie inne wyniki od spodziewanych. Z tego też względu dodałem go do swojej osobistej listy CODE REVIEW w SQL.

Podsumowanie ORACLE LEFT JOIN

  • Baza danych pozwala łączyć zewnętrznie tabele co oznacza, że do tabeli zwrócone zostaną rekordy niespełniające warunku.
  • FULL OUTER JOIN zwróci wszystkie rekordy spełniające warunek złączenia oraz rekordy z obu tabel go nie spełniającego
  • LEFT i RIGHT JOIN zwrócą wszystkie rekordy spełniające warunek złączenia oraz rekordy z jeden z tabel go nie spełniających
  • Operator (+) jest specyficzny dla bazy danych Oracle i oznacza tabele “dodawaną” do tabeli “głównej”
  • Należy uważać na łączenie operatorów LEFT JOIN a następnie JOIN
  • Poniżej małe graficzne przedstawienie idei złączeń:
oracle outer joins
  • Jeżeli chcesz znaleźć więcej kursów dla średniozaawansowanych oraz poznać zasady optymalizacji zapytań zapoznaj się z pozostałymi kursami, sprawdź: Kurs Oracle SQL
  • Jeżeli chcesz poznać podstawy baz danych i kurs dla początkujących w SQL odwiedź Kurs SQL

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *