Suporte de joins

4D v19

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.
joins cruzadas 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.

A partir de 4D v15 R4, joins externas envolvendo duas tabelas e joins externas envolvendo três ou m ais tabelas são diferentes implementações e não seguem as mesmas regras. Veja abaixo a seção que corresponde a suas necessidades.

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

Banco de dados exemplo

Para ilustrar como funcionam as junções, vamos usar os bancos de dados abaixo nessa seção:

Employees
name depID cityID
Alan 10 30
Anne 11 39
Bernard 10 33
Fabrice 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

Se quiser, pode gerar este banco de dados automaticamente ao executar o código abaixo:

 Begin SQL

         DROP TABLE IF EXISTS Employees;

         CREATE TABLE Employees ( depID INT32, name VARCHAR, cityID INT32);

         INSERT INTO Employees (name, depID, cityID) VALUES ('Alan', 10, 30);

         INSERT INTO Employees (name, depID, cityID) VALUES ('Anne', 11, 39);

         INSERT INTO Employees (name, depID, cityID) VALUES ('Bernard', 10, 33);

         INSERT INTO Employees (name, depID, cityID) VALUES ('Fabrice', 12, 35);

         INSERT INTO Employees (name, depID, cityID) VALUES ('Martin', 15, 30);

         INSERT INTO Employees (name, depID, cityID) VALUES ('Philip', NULL, 33);

         INSERT INTO Employees (name, depID, cityID) VALUES ('Thomas', 10, NULL);

 

         DROP TABLE IF EXISTS Departments;

         CREATE TABLE Departments ( depID INT32, depName VARCHAR );

         INSERT INTO Departments (depID, depName) VALUES (10, 'Program');

         INSERT INTO Departments (depID, depName) VALUES (11, 'Engineering');

         INSERT INTO Departments (depID, depName) VALUES (NULL, 'Marketing');

         INSERT INTO Departments (depID, depName) VALUES (12, 'Development');

         INSERT INTO Departments (depID, depName) VALUES (13, 'Quality');

 

         DROP TABLE IF EXISTS Cities;

         CREATE TABLE Cities ( cityID INT32, cityName VARCHAR );

         INSERT INTO Cities (cityID, cityName) VALUES (30, 'Paris');

         INSERT INTO Cities (cityID, cityName) VALUES (33, 'New York');

         INSERT INTO Cities (cityID, cityName) VALUES (NULL, 'Berlin');

 End SQL

Junções internas explícitas

Uma junção interna (INNER JOIN)é baseada em uma comparação para achar coincidências entre duas colunas.

Aqui é um exemplo de uma junção interna implícita:

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

Em 4D, pode também usar a palavra chave JOIN para especificar uma junção interna explícita:

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

Pode inserir esta pesquisa no código 4D como mostrado abaixo:

 ARRAY TEXT(aName;0)

 ARRAY TEXT(aDepName;0)

 ARRAY INTEGER(aEmpDepID;0)

 ARRAY INTEGER(aDepID;0)

 Begin SQL

        SELECT Employees.name, Employees.depID, Departments.depID, Departments.depName

        FROM Employees

                INNER JOIN Departments

                    ON Employees.depID = Departments.depID

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

 End SQL

Aqui estão os resultado a unir:

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 chamados Philip ou Martin nem os departamentos de Marketing ou Qualidade aparecem nos resultados unidos porque:

Philip não tem um departamento associado com seu nome (NULL value),
A ID do departamento associado com o nome de Martin não existe na tabela Departamentos,
Não há nenhum empregado associado com o Departamento de Qualidade (ID 13),
O departamento de Marketing não tem nenhuma ID associada (NULL value).

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.

Com joinx exernas para duas-tabelas, condições podem ser complexas, mas devem sermpre estar baseadas em uma comparação de igualdade entre as colunas incluidas na join. Por exemplo, não é possível usar o operador >= em uma condição join explicita. Qualquer tipo de comparação pode ser usada em uma join implícita. Internamente, comparações de igualdade pode ser realizadas diretamente no motor 4D , o que assegura execução rápida.

Junção externa esquerda

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 Employees.name, Employees.depID, Departments.depID, Departments.depName

            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

Junção externa direita

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 Employees.name, Employees.depID, Departments.depID, Departments.depName

            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

Junção externa bilateral

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 Employees.name, Employees.depID, Departments.depID, Departments.depName

            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 externas com duas ou mais tabelas

A partir de 4D v15 R4, o servidor built-in SQL server extende o suporte de joins externas SQL para pesqusias envolvendo três ou mais tabelas. Esta implementação específica tem suas próprias regras e limitações, que estão descritas nesta seção.

Da mesma maneira que junções externas com três ou mais tabelas podem ser LEFT, RIGHT, ou FULL. Para informação geral sobre junções externas, veja o parágrafo Joins externas.

Ao contrário de junções externas de duas tabelas, junções externas com três ou mais tabelas suportam diversos operadores de comparação, além da igualdade (=): <, >, >=, ou <=. Estes operadores podem ser misturadso com as cláusulas ON.

Regras básicas

Cada junção externa explícita cláusula ON deve referenciar exatamente duas tabelas, não mais e não menos. Cada tabela joined deve ser referenciada ao menos uma vez na cláusula ON.
Uma das tabelas devem vir da parte imediatamente a esquerda da cláusula JOIN e outra, da diietamente imediata.

Por exemplo, a pesquisa abaixo podem ser executads com sucesso:

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T3.ID) -- here T2 is on the left and T3 is on the right
ON T1.ID=T3.ID -- here T1 is on the left and T3 is on the right

Com nossas 3 tabelas, este exemplo poderia ser:

 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 Employees.name, Employees.depID, Employees.cityID, Departments.depID, Departments.depName, Cities.cityID, Cities.cityName

            FROM Departments

                LEFT JOIN

                (Employees LEFT JOIN Cities ON Employees.cityID=Cities.cityID)

                ON Departments.depID=Employees.depID

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

 End SQL

Aqui estão os resultados:

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

Por outro lado, as 3 pesquisas abaixo podem ser rejeitadas já que violam as regras:

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T1.ID) -- aqui T2 está a esquerda, mas T1 não está imediatamente a direita
ON T1.ID=T3.ID

SELECT * FROM
(T1 LEFT JOIN T2 ON T1.ID=T2.ID)
LEFT JOIN
(T3 LEFT JOIN T4 ON T3.ID=T4.ID)
ON T3.Name=T4.Name -- aqui tanto  T3 quanto T4 estao do lado direito da clausula JOIN e nenhuma tabela está do lado esquerdo

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T3.ID)
ON T1.ID=T3.ID AND T1.ID=T2.ID -- aqui mais de duas tabelas estão sendo usadas na clausula ON: T1, T2, e T3

Suporte da condição ON

Em geral, se tabelas (Tx1, Tx2..., Txn) a esquerda da clausula JOIN e tabelas (Ty1, Ty2..., Tym) a esquerda estiverem sendo combinadas, então a expressão ON deve referenciar uma tablea esquerda Txa e exatamente uma tabela direita Tyb.

	Não compatível na cláusula ON	Compatível na cláusula ON
Boolean operations	OR	AND e NOT
Predicados e funções	IS NULL, COALESCE	Todos outros predicados e funções built-in (podem ser usadas em qualquer combinação desejada)
4D variable references	-	Suportada sem restrições
4D method calls	Quando o lado esquerdo o u direito da claúsula atual JOIN for uma join externa explícita	Qualquer outro caso (ver exemplo abaixo

O exemplo abaixo com um método 4D é suportado porque não há sub-joins internas para combinar:

SELECT * FROM T1
LEFT JOIN T2
ON T1.ID={FN My4DCall (T2.ID) AS INT32}

Por outro lado, este exemplo de método de chamada 4D não é suportada porque sub-joins não internas estão sendo combinadas:

SELECT * FROM
(T1 LEFT JOIN T2 ON T1.ID=T2.ID)
LEFT JOIN -- Tanto os lados esquerdo e direitos da clausula join contém joins explícitas LEFT
(T3 LEFT JOIN T4 ON T3.ID=T4.ID)
ON T1.ID={FN My4DCall (T4.ID) AS INT32} -- sub-joins não internas estão sendo combinadas

Limitações gerais

Referências a Vistas SQL não estão permitidas na declaração join explícita
Subpesquisas que usam joins externas não são compatíveis. O seguinte seria rejeitado:
SELECT T2.ID FROM T2 WHERE T2.ID=( SELECT COUNT ( * ) FROM (T1 LEFT JOIN T3 ON T1.ID=T3.ID) RIGHT JOIN T4 ON T3.ID=T4.ID)