4D Doc Center : Suporte de joins

4D v14

Suporte de joins

O motor SQL de 4D amplia o suporte das sentenças join.

As sentenças join podem ser internas ou externas, implícitas ou explícitas. As joins internas (INNER joins) implícitas são suportadas pelo comando SELECT. Também pode gerar joins internas e externas explícitas utilizando a palavra chave SQL JOIN.

Nota: A implementação atual de joins no motor SQL de 4D não inclui:

joins naturais.
o construtor USING nas joins internas.

Apresentação

As sentenças join permitem fazer conexões entre os registros de duas ou mais tabelas e combinar o resultado em uma tabela nova, chamada join.

Gere joins via as instruções SELECT que especificam as condições de join. Com as joins explícitas, estas condições podem ser complexas, mas sempre devem ser baseadas em uma comparação de igualdade entre as colunas incluídas na join. Por exemplo, não é possível utilizar o operador >= em uma condição de join explícita. Todo tipo de comparação pode ser utilizada em uma join implícita.
Internamente, as comparações de igualdade são realizadas diretamente pelo motor de 4D, que garante uma rápida execução.

Nota: Geralmente, no motor do banco de dados, a ordem das tabelas está determinada pela ordem definida durante a pesquisa. No entanto, ao usar combinações, a ordem das tabelas são determinadas pela lista de tabelas. No seguinte exemplo:
SELECT * FROM T1 RIGHT OUTER JOIN T2 ON = T2.depID T1.depID;
... a ordem das tabelas é T1 e depois T2 (tal como aparecem na lista de tabelas) e não T1 e depois T2 (tal como aparecem na condição de join).

Joins internas explícitas

Uma join interna (inner join) está baseada em uma comparação de igualdade entre duas colunas. Por exemplo, se consideramos as seguintes três tabelas:

Employees
name depID cityID
Alan 10 30
Anne 11 39
Bernard 10 33
Mark 12 35
Martin 15 30
Philip NULL 33
Thomas 10 NULL

Departments
depID depName
10 Program
11 Engineering
NULL Marketing
12 Development
13 Quality

Cities
cityID cityName
30 Paris
33 New York
NULL Berlin

Nota: Esta estrutura de exemplo será utilizada durante este capítulo.

Este é um exemplo de join interna explícita:

SELECT *  
        FROM employees, departments
        WHERE employees.DepID = departments.DepID;

Em 4D, pode também utilizar a palavra chave JOIN para especificar uma join interna explícita:

SELECT *
        FROM employees
        INNER JOIN departments
            ON employees.DepID = departments.DepID;

Esta pesquisa pode ser inserida no código 4D da seguinte forma:

 ARRAY TEXT(aName;0)

 ARRAY TEXT(aDepName;0)

 ARRAY INTEGER(aEmpDepID;0

 ARRAY INTEGER(aDepID;0)

 Begin SQL

        SELECT *

        FROM employees

                INNER JOIN departments

                    ON employees.depID = departments.depID

                INTO :aName, :aEmpDepID, :aDepID, :aDepName;

 End SQL

Este é o resultado desta join:

aName	aEmpDepID	aDepID	aDepName
Alan	10	10	Program
Anne	11	11	Engineering
Bernard	10	10	Program
Mark	12	12	Development
Thomas	10	10	Program

Note que nem os empregados Philip ou Martin nem os departamentos Marketing ou Quality aparecem na join resultante porque:

Philip não tem um departamento associado com seu nome (valor NULL),
O ID de departamento de Martin não existe na tabela Departamentos,
Não há empregado associado ao departamento de ID 13,
O departamento de Marketing não tem um ID associado (valor NULL).

Joins cruzadas (cross)

Uma join interna na qual a cláusula WHERE e a cláusula ON são omitidas, se chamam join cruzada ou cartesiana. Realizar uma join cruzada consiste em associar cada linha de uma tabela a cada linha de outra tabela.
O resultado de uma join cruzada é o produto cartesiano das tabelas, com a x b linhas, onde a é o número de linhas da primeira tabela e b é o número de linhas da segunda tabela. Este produto representa todas as combinações possíveis formadas pela concatenação de linhas de ambas tabelas.

Cada uma das seguintes sintaxes são equivalentes:

    SELECT * FROM T1 INNER JOIN T2
    SELECT * FROM T1, T2
    SELECT * FROM T1 CROSS JOIN T2;

Este é um exemplo de código 4D integrando uma join cruzada:

 ARRAY TEXT(aName;0)

 ARRAY TEXT(aDepName;0)

 ARRAY INTEGER(aEmpDepID;0

 ARRAY INTEGER(aDepID;0)

 Begin SQL

        SELECT *

        FROM employees CROSS JOIN departments

                INTO :aName, :aEmpDepID, :aDepID, :aDepName;

 End SQL

Resultado desta join com nossa base de exemplo:

aName	aEmpDepID	aDepID	aDepName
Alan	10	10	Program
Anne	11	10	Program
Bernard	10	10	Program
Mark	12	10	Program
Martin	15	10	Program
Philip	NULL	10	Program
Thomas	10	10	Program
Alan	10	11	Engineering
Anne	11	11	Engineering
Bernard	10	11	Engineering
Mark	12	11	Engineering
Martin	15	11	Engineering
Philip	NULL	11	Engineering
Thomas	10	11	Engineering
Alan	10	NULL	Marketing
Anne	11	NULL	Marketing
Bernard	10	NULL	Marketing
Mark	12	NULL	Marketing
Martin	15	NULL	Marketing
Philip	NULL	NULL	Marketing
Thomas	10	NULL	Marketing
Alan	10	12	Development
Anne	11	12	Development
Bernard	10	12	Development
Mark	12	12	Development
Martin	15	12	Development
Philippe	NULL	12	Development
Thomas	10	12	Development
Alain	10	13	Quality
Anne	11	13	Quality
Bernard	10	13	Quality
Mark	12	13	Quality
Martin	15	13	Quality
Philip	NULL	13	Quality
Thomas	10	13	Quality

Nota: Por razões de rendimento, as joins cruzadas devem ser utilizadas com cuidado.

Joins externas

Agora pode gerar joins externas com 4D (OUTER JOINs). Em uma join externa, não é necessário que tenha uma correspondência entre as linhas das tabelas combinadas. A tabela resultante contém todas as linhas das tabelas (ou de pelo menos uma das tabelas combinadas), inclusive se não há linhas correspondentes. Isto significa que toda a informação de uma tabela pode ser utilizada, ainda que as linhas não enchem completamente entre as diferentes tabelas unidas.

Há três tipos de joins externas, definidas pelas palavras chaves LEFT, RIGHT e FULL. LEFT e RIGHT se utilizam para indicar a tabela (localizada a esquerda ou a direita da palavra chave JOIN) na que todos os dados devem ser processados. FULL indica uma join externa bilateral.

Nota: Somente as joins externas explícitas são suportadas por 4D.

(Left outer joins)

O resultado de uma join externa esquerda (ou left join) sempre contém todos os registros da tabela situada a esquerda da palavra chave, inclusive se a condição de join não encontra um registro correspondente na tabela a direita. Isto significa que para cada linha da tabela da esquerda, onde a pesquisa não encontra nenhuma linha correspondente na tabela da direita, a join vai conter a linha com valores NULL para cada coluna da tabela da direita. Em outras palavras, uma join externa esquerda devolve todas as linhas da tabela da esquerda, além das da tabela da direita que correspondam a condição de join (ou NULL se nenhuma corresponde). Tenha em conta que se a tabela da direita contém mais de uma linha que corresponde com o predicado da join para uma linha da tabela da esquerda, os valores da tabela esquerda serão repetidas para cada linha diferente da tabela direita.

Este é um exemplo de código 4D com uma join externa esquerda:

 ARRAY TEXT(aName;0)

 ARRAY TEXT(aDepName;0)

 ARRAY INTEGER(aEmpDepID;0

 ARRAY INTEGER(aDepID;0)

 Begin SQL

        SELECT *

            FROM employees

            LEFT OUTER JOIN departments

                ON employees.DepID = departments.DepID;

                INTO :aName, :aEmpDepID, :aDepID, :aDepName;

 End SQL

Este é o resultado desta join com nossa base de exemplo (as linhas adicionais são mostradas em vermelho):

aName	aEmpDepID	aDepID	aDepName
Alan	10	10	Program
Anne	11	11	Engineering
Bernard	10	10	Program
Mark	12	12	Development
Thomas	10	10	Program
Martin	15	NULL	NULL
Philip	NULL	NULL	NULL

Right outer joins

Uma join externa direita é o oposto exato de uma join externa esquerda. Seu resultado sempre contém todos os registros da tabela localizada a direita da palavra chave JOIN inclusive se a condição join não encontra um registro correspondente na tabela esquerda.

Este é um exemplo de código 4D com uma join externa direita:

 ARRAY TEXT(aName;0)

 ARRAY TEXT(aDepName;0)

 ARRAY INTEGER(aEmpDepID;0

 ARRAY INTEGER(aDepID;0)

 Begin SQL

        SELECT *

            FROM employees

            RIGHT OUTER JOIN departments

                ON employees.DepID = departments.DepID;

                INTO :aName, :aEmpDepID, :aDepID, :aDepName;

 End SQL

Este é o resultado desta join com nossa base de exemplo (as linhas adicionais estão em vermelho):

aName	aEmpDepID	aDepID	aDepName
Alan	10	10	Program
Anne	11	11	Engineering
Bernard	10	10	Program
Mark	12	12	Development
Thomas	10	10	Program
NULL	NULL	NULL	Marketing
NULL	NULL	13	Quality

Full outer joins

Uma join externa bilateral combina os resultados de uma join externa esquerda e de uma join externa direita. A tabela join resultante contém todos os registros das tabelas esquerda e direita e enche os campos que faltam de cada lado valores NULL.

Este é um exemplo de código 4D com uma join externa bilateral:

 ARRAY TEXT(aName;0)

 ARRAY TEXT(aDepName;0)

 ARRAY INTEGER(aEmpDepID;0

 ARRAY INTEGER(aDepID;0)

 Begin SQL

        SELECT *

            FROM employees

            FULL OUTER JOIN departments

                ON employees.DepID = departments.DepID;

                INTO :aName, :aEmpDepID, :aDepID, :aDepName;

 End SQL

Este é o resultado desta join com nossa base de exemplo (as linhas adicionais são mostradas em vermelho):

aName	aEmpDepID	aDepID	aDepName
Alan	10	10	Program
Anne	11	11	Engineering
Bernard	10	10	Program
Mark	12	12	Development
Thomas	10	10	Program
Martin	15	NULL	NULL
Philip	NULL	NULL	NULL
NULL	NULL	NULL	Marketing
NULL	NULL	13	Quality

Joins múltiplas em somente uma petição

É possível combinar várias joins na mesma instrução SELECT. Também é possível combinar joins internas implícitas ou explicitas e joins externas explícitas.

Este é um exemplo de código 4D com joins múltiplas:

 ARRAY TEXT(aName;0)

 ARRAY TEXT(aDepName;0)

 ARRAY TEXT(aCityName;0)

 ARRAY INTEGER(aEmpDepID;0

 ARRAY INTEGER(aEmpCityID;0)

 ARRAY INTEGER(aDepID;0)

 ARRAY INTEGER(aCityID;0)

 Begin SQL

        SELECT *

            FROM (employees RIGHT OUTER JOIN departments

                        ON employees.depID = departments.depID)

                        LEFT OUTER JOIN cities

                        ON employees.cityID = cities.cityID

            INTO :aName, :aEmpDepID, :aEmpCityID, :aDepID, :aDepName, :aCityID, :aCityName;

 End SQL

Este é o resultado desta join com nossa base de exemplo:

aName	aEmpDepID	aEmpCityID	aDepID	aDepName	aCityID	aCityName
Alan	10	30	10	Program	30	Paris
Anne	11	39	11	Engineering	0
Bernard	10	33	10	Program	33	New York
Mark	12	35	12	Development	0
Thomas	10	NULL	10	Program	0