4D Doc Center : Soporte de joins

4D v14

Soporte de joins

El motor SQL de 4D amplía el soporte de las sentencias join.

Las sentencias join puede ser internas o externas, implícitas o explícitas. Las joins internas (INNER joins) implicitas son soportadas por el comando SELECT. También puede generar joins internas y externas explícitas utilizando la palabra clave SQL JOIN.

Nota: la implementación actual de joins en el motor SQL de 4D no incluye:

joins naturales.
el constructor USING en las joins internas.

Presentación

Las sentencias join permiten hacer conexiones entre los registros de dos o más tablas y combinar el resultado en una tabla nueva, llamada join.

Genere joins vía las instrucciones SELECT que especifican las condiciones de join. Con las joins explícitas, estas condiciones pueden ser complejas, pero siempre deben basarse en una comparación de igualdad entre las columnas incluidas en la join. Por ejemplo, no es posible utilizar el operador >= en una condición de join explícita. Todo tipo de comparación se puede utilizar en una join implícita.
Internamente, las comparaciones de igualdad son efectuadas directamente por el motor de 4D, que garantiza una rápida ejecución.

Nota: Por lo general, en el motor de base de datos, el orden de las tablas está determinado por el orden definido durante la búsqueda. Sin embargo, al usar combinaciones, el orden de las tablas se determina por la lista de tablas. En el ejemplo siguiente:
SELECT * FROM T1 RIGHT OUTER JOIN T2 ON = T2.depID T1.depID;
... el orden de las tablas es T1 y luego T2 (tal como aparecen en la lista de tablas) y no T1 y luego T2 (tal como aparecen en la condición de join).

Joins internas explícitas

Una join interna (inner join) está basada en una comparación de igualdad entre dos columnas. Por ejemplo, si consideramos las tres tablas siguientes:

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 estructura de ejemplo se utilizará durantes este capítulo.

Este es un ejemplo de join interna explícita:

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

En 4D, puede también utilizar la palabra clave JOIN para especificar una join interna explícita:

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

Esta búsqueda puede insertarse en el código 4D de la siguiente manera:

 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 el resultado de esta 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 ni los empleados Philip o Martin ni los departamentos Marketing o Quality aparecen en la join resultante porque:

Philip no tiene un departamento asociado con su nombre (valor NULL),
El ID de departamento de Martin no existe en la tabla Departamentos,
No hay empleado asociado al departamento de ID 13,
El departamento de Marketing no tiene un ID asociado (valor NULL).

Joins cruzadas (cross)

Una join interna en la cual la cláusula WHERE y la cláusula ON se omiten se llama join cruzada o cartesiana. Efectuar una join cruzada consiste en asociar cada línea de una tabla a cada línea de otra tabla.
El resultado de una join cruzada es el producto cartesiano de las tablas, con a x b líneas, donde a es el número de líneas de la primera tabla y b es el número de líneas de la segunda tabla. Este producto representa todas las combinaciones posibles formadas por la concatenación de líneas de ambas tablas.

Cada una de las siguientes sintaxis son equivalentes:

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

Este es un ejemplo de código 4D integrando una 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 de esta join con nuestra base de ejemplo:

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 razones de rendimiento, las joins cruzadas deben utilizarse con cuidado.

Joins externas

Ahora puede generar joins externas con 4D (OUTER JOINs). En una join externa, no es necesario que haya una correspondencia entre las líneas de las tablas combinadas. La tabla resultante contiene todas las líneas de las tablas (o de al menos una de las tablas combinadas), incluso si no hay líneas correspondientes. Esto significa que toda la información de una tabla puede ser utilizada, aunque las líneas no se llenan por completo entre las diferentes tablas unidas.

Hay tres tipos de joins externas, definidas por las palabras claves LEFT, RIGHT y FULL. LEFT y RIGHT se utilizan para indicar la tabla (ubicada a la izquierda o a la derecha de la palabra clave JOIN) en la que todos los datos deben ser procesados. FULL indica una join externa bilateral.

Nota: sólo las joins externas explícitas son soportadas por 4D.

Joins externas izquierdas (Left outer joins)

El resultado de una join externa izquierda (o left join) siempre contiene todos los registros de la tabla situada a la izquierda de la palabra clave, incluso si la condición de join no encuentra un registro correspodiente en la tabla a la derecha. Esto significa que para cada línea de la tabla de la izquierda, donde la búsqueda no encuentra ninguna línea correpondiente en la tabla de la derecha, la join contendrá la línea con valores NULL para cada columna de la tabla de la derecha. En otras palabras, una join externa izquierda devuelve todas las líneas de la tabla de la izquierda, además de las de la tabla de la derecha que correspondan a la condición de join (o NULL si ninguna corresponde). Tenga en cuenta que si la tabla de la derecha contiene más de una línea que corresponde con el predicado de la join para una línea de la tabla de la izquierda, los valores de la tabla izquierda se repetirán para cada línea distinta de la tabla derecha.

Este es un ejemplo de código 4D con una join externa izquierda:

 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 es el resultado de esta join con nuestra base de ejemplo (las líneas adicionales se muestran en rojo):

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

Joins externas derechas (Right outer joins)

Una join externa derecha es el opuesto exacto de una join externa izquierda. Su resultado siempre contiene todos los registros de la tabla ubicada a la derecha de la palabra clave JOIN incluso si la condición join no encuentra un registro correspondiente en la tabla izquierda.

Este es un ejemplo de código 4D con una join externa derecha:

 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 es el resultado de esta join con nuestra base de ejemplo (las líneas adicionales están en rojo):

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

Joins externas bilaterales (Full outer joins)

Una join externa bilateral combina los resultados de una join externa izquierda y de una join externa derecha. La tabla join resultante contiene todos los registros de las tablas izquierda y derecha y llena los campos faltantes de cada lado valores NULL.

Este es un ejemplo de código 4D con una 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 es el resultado de esta join con nuestra base de ejemplo (las líneas adicionales se muestran en rojo):

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últiples en una sola petición

Es posible combinar varias joins en la misma instrucción SELECT. También es posible combinar joins internas implícitas o explicitas y joins externas explícitas.

Este es un ejemplo de código 4D con joins múltiples:

 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 es el resultado de esta join con nuestra base de ejemplo:

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