PostgreSQL - DOŁĄCZA

PostgreSQL JoinsKlauzula służy do łączenia rekordów z dwóch lub więcej tabel w bazie danych. JOIN to sposób łączenia pól z dwóch tabel przy użyciu wspólnych wartości.

Typy złączeń w PostgreSQL to -

CROSS JOIN
WEWNĘTRZNE DOŁĄCZENIE
POŁĄCZENIE LEWE ZEWNĘTRZNE
WŁAŚCIWE POŁĄCZENIE ZEWNĘTRZNE
PEŁNE DOŁĄCZENIE ZEWNĘTRZNE

Zanim przejdziemy dalej, rozważmy dwie tabele, FIRMA i DZIAŁ. Widzieliśmy już instrukcje INSERT wypełniające tabelę COMPANY. Przyjmijmy więc listę rekordów dostępnych w tabeli FIRMA -

id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13

Kolejna tabela to DEPARTMENT, ma następującą definicję -

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Oto lista instrukcji INSERT do wypełnienia tabeli DEPARTMENT -

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

Na koniec mamy następującą listę rekordów dostępnych w tabeli DEPARTMENT -

id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7

CROSS JOIN

CROSS JOIN dopasowuje każdy wiersz pierwszej tabeli do każdego wiersza drugiej tabeli. Jeśli tabele wejściowe mają odpowiednio kolumny x i y, wynikowa tabela będzie miała kolumny x + y. Ponieważ połączenia CROSS JOIN mogą generować bardzo duże tabele, należy uważać, aby ich używać tylko wtedy, gdy jest to właściwe.

Poniżej znajduje się składnia CROSS JOIN -

SELECT ... FROM table1 CROSS JOIN table2 ...

Na podstawie powyższych tabel możemy napisać CROSS JOIN w następujący sposób -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

Powyższe zapytanie da następujący wynik -

emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance

WEWNĘTRZNE DOŁĄCZENIE

INNER JOIN tworzy nową tabelę wynikową, łącząc wartości kolumn z dwóch tabel (tabela1 i tabela2) na podstawie predykatu łączenia. Zapytanie porównuje każdy wiersz tabeli tabela1 z każdym wierszem tabeli tabela2, aby znaleźć wszystkie pary wierszy, które spełniają predykat łączenia. Gdy predykat łączenia jest spełniony, wartości kolumn dla każdej dopasowanej pary wierszy tabela1 i tabela2 są łączone w wiersz wynikowy.

SPRZĘŻENIE WEWNĘTRZNE jest najpowszechniejszym typem sprzężenia i domyślnym typem sprzężenia. Opcjonalnie można użyć słowa kluczowego INNER.

Poniżej przedstawiono składnię INNER JOIN -

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

Na podstawie powyższych tabel możemy napisać INNER JOIN w następujący sposób -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

Powyższe zapytanie da następujący wynik -

emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering

POŁĄCZENIE LEWE ZEWNĘTRZNE

OUTER JOIN jest rozszerzeniem INNER JOIN. Standard SQL definiuje trzy typy POŁĄCZEŃ ZEWNĘTRZNYCH: LEFT, RIGHT i FULL, a PostgreSQL obsługuje je wszystkie.

W przypadku LEFT OUTER JOIN, najpierw wykonywane jest sprzężenie wewnętrzne. Następnie dla każdego wiersza w tabeli T1, który nie spełnia warunku łączenia z żadnym wierszem w tabeli T2, do połączonego wiersza dodawane są wartości null w kolumnach T2. Zatem połączona tabela zawsze ma co najmniej jeden wiersz na każdy wiersz w T1.

Poniżej przedstawiono składnię LEFT OUTER JOIN -

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

Na podstawie powyższych tabel możemy napisać sprzężenie wewnętrzne w następujący sposób -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Powyższe zapytanie da następujący wynik -

emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

WŁAŚCIWE POŁĄCZENIE ZEWNĘTRZNE

Najpierw wykonywane jest sprzężenie wewnętrzne. Następnie dla każdego wiersza w tabeli T2, który nie spełnia warunku łączenia z żadnym wierszem w tabeli T1, do połączonego wiersza dodawane są wartości null w kolumnach T1. To jest odwrotność złączenia lewostronnego; tabela wynikowa zawsze będzie miała wiersz dla każdego wiersza w T2.

Oto składnia RIGHT OUTER JOIN -

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

Na podstawie powyższych tabel możemy napisać sprzężenie wewnętrzne w następujący sposób -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Powyższe zapytanie da następujący wynik -

emp_id | name  | dept
--------+-------+--------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance

PEŁNE DOŁĄCZENIE ZEWNĘTRZNE

Najpierw wykonywane jest sprzężenie wewnętrzne. Następnie dla każdego wiersza w tabeli T1, który nie spełnia warunku łączenia z żadnym wierszem w tabeli T2, do połączonego wiersza dodawane są wartości null w kolumnach T2. Ponadto dla każdego wiersza T2, który nie spełnia warunku łączenia z żadnym wierszem w T1, dodawany jest połączony wiersz z wartościami null w kolumnach T1.

Poniżej znajduje się składnia FULL OUTER JOIN -

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

Na podstawie powyższych tabel możemy napisać sprzężenie wewnętrzne w następujący sposób -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Powyższe zapytanie da następujący wynik -

emp_id | name  | dept
--------+-------+---------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |