PostgreSQL - JOINS

O PostgreSQL Joinscláusula é usada para combinar registros de duas ou mais tabelas em um banco de dados. Um JOIN é um meio de combinar campos de duas tabelas usando valores comuns a cada uma.

Os tipos de junção no PostgreSQL são -

O CROSS JOIN
O INNER JOIN
O LEFT OUTER JOIN
O DIREITO OUTER JOIN
O FULL OUTER JOIN

Antes de prosseguirmos, consideremos duas tabelas, EMPRESA e DEPARTAMENTO. Já vimos instruções INSERT para preencher a tabela COMPANY. Portanto, vamos supor que a lista de registros disponíveis na tabela COMPANY -

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

Outra tabela é DEPARTAMENTO, tem a seguinte definição -

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

Aqui está a lista de instruções INSERT para preencher a tabela 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 );

Finalmente, temos a seguinte lista de registros disponíveis na tabela DEPARTAMENTO -

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

O CROSS JOIN

Um CROSS JOIN corresponde a cada linha da primeira tabela com cada linha da segunda tabela. Se as tabelas de entrada tiverem colunas xey, respectivamente, a tabela resultante terá colunas x + y. Como os CROSS JOINs têm o potencial de gerar tabelas extremamente grandes, deve-se tomar cuidado para usá-los somente quando apropriado.

A seguir está a sintaxe de CROSS JOIN -

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

Com base nas tabelas acima, podemos escrever um CROSS JOIN da seguinte forma -

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

A consulta fornecida acima produzirá o seguinte resultado -

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

O INNER JOIN

Um INNER JOIN cria uma nova tabela de resultados combinando valores de coluna de duas tabelas (tabela1 e tabela2) com base no predicado de junção. A consulta compara cada linha da tabela1 com cada linha da tabela2 para encontrar todos os pares de linhas que satisfazem o predicado de junção. Quando o predicado de junção é satisfeito, os valores da coluna para cada par correspondente de linhas de table1 e table2 são combinados em uma linha de resultado.

Um INNER JOIN é o tipo mais comum de junção e é o tipo padrão de junção. Você pode usar a palavra-chave INNER opcionalmente.

A seguir está a sintaxe de INNER JOIN -

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

Com base nas tabelas acima, podemos escrever um INNER JOIN da seguinte forma -

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

A consulta fornecida acima produzirá o seguinte resultado -

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

O LEFT OUTER JOIN

O OUTER JOIN é uma extensão do INNER JOIN. O padrão SQL define três tipos de OUTER JOINs: LEFT, RIGHT e FULL e o PostgreSQL oferece suporte a todos eles.

No caso de LEFT OUTER JOIN, uma junção interna é executada primeiro. Então, para cada linha na tabela T1 que não satisfaz a condição de junção com nenhuma linha na tabela T2, uma linha junta é adicionada com valores nulos nas colunas de T2. Portanto, a tabela associada sempre tem pelo menos uma linha para cada linha em T1.

A seguir está a sintaxe de LEFT OUTER JOIN -

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

Com base nas tabelas acima, podemos escrever uma junção interna da seguinte maneira -

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

A consulta fornecida acima produzirá o seguinte resultado -

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

O DIREITO OUTER JOIN

Primeiro, uma junção interna é executada. Então, para cada linha na tabela T2 que não satisfaz a condição de junção com nenhuma linha na tabela T1, uma linha junta é adicionada com valores nulos nas colunas de T1. Este é o inverso de uma junção à esquerda; a tabela de resultados sempre terá uma linha para cada linha em T2.

A seguir está a sintaxe de RIGHT OUTER JOIN -

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

Com base nas tabelas acima, podemos escrever uma junção interna da seguinte maneira -

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

A consulta fornecida acima produzirá o seguinte resultado -

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

O FULL OUTER JOIN

Primeiro, uma junção interna é executada. Então, para cada linha na tabela T1 que não satisfaz a condição de junção com nenhuma linha na tabela T2, uma linha junta é adicionada com valores nulos nas colunas de T2. Além disso, para cada linha de T2 que não satisfaz a condição de junção com nenhuma linha em T1, uma linha junta com valores nulos nas colunas de T1 é adicionada.

A seguir está a sintaxe de FULL OUTER JOIN -

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

Com base nas tabelas acima, podemos escrever uma junção interna da seguinte maneira -

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

A consulta fornecida acima produzirá o seguinte resultado -

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