Oracle LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN - złączenia zewnętrzne - OracleDev

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 LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN – złączenia zewnętrzne