SQLite - JOINS

SQLite 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.

SQL define três tipos principais de junções -

O CROSS JOIN
O INNER JOIN
O OUTER JOIN

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

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Outra tabela é DEPARTAMENTO com 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 );

Por fim, 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

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

A seguir está a sintaxe de CROSS JOIN -

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

Com base nas tabelas acima, você pode escrever um CROSS JOIN da seguinte forma -

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

A consulta acima produzirá o seguinte resultado -

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineering
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineering
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineering
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineering
7           Mark        Finance
1           David       IT Billing
2           David       Engineering
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineering
7           Kim         Finance
1           James       IT Billing
2           James       Engineering
7           James       Finance

O INNER JOIN

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 de linhas correspondentes de A e B são combinados em uma linha de resultado.

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

A seguir está a sintaxe de INNER JOIN -

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

Para evitar redundância e manter o fraseado mais curto, as condições INNER JOIN podem ser declaradas com um USINGexpressão. Esta expressão especifica uma lista de uma ou mais colunas.

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

Um NATURAL JOIN é semelhante a um JOIN...USING, apenas testa automaticamente a igualdade entre os valores de cada coluna que existe em ambas as tabelas -

SELECT ... FROM table1 NATURAL JOIN table2...

Com base nas tabelas acima, você pode escrever um INNER JOIN da seguinte forma -

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

A consulta acima produzirá o seguinte resultado -

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
7           James       Finance

O OUTER JOIN

OUTER JOIN é uma extensão de INNER JOIN. Embora o padrão SQL defina três tipos de OUTER JOINs: LEFT, RIGHT e FULL, o SQLite suporta apenas oLEFT OUTER JOIN.

OUTER JOINs têm uma condição idêntica a INNER JOINs, expressa usando uma palavra-chave ON, USING ou NATURAL. A tabela de resultados iniciais é calculada da mesma forma. Depois que o JOIN primário é calculado, um OUTER JOIN pegará todas as linhas não unidas de uma ou de ambas as tabelas, preencherá-as com NULLs e as anexará à tabela resultante.

A seguir está a sintaxe de LEFT OUTER JOIN -

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

Para evitar redundância e manter o fraseado mais curto, as condições OUTER JOIN podem ser declaradas com uma expressão USING. Esta expressão especifica uma lista de uma ou mais colunas.

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

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

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

A consulta acima produzirá o seguinte resultado -

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
            Teddy
            Mark
            David
            Kim
7           James       Finance