4D v16

Soporte de combinaciones

Inicio

 
Inicio
4D v16
Manual de SQL
Utilizar SQL en 4D
Soporte de combinaciones

Soporte de combinaciones  


 

 

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 uniones internas (INNER JOIN) implícitas 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.
  • JOINs cruzados.

La sentencia JOIN permite hacer combinaciones entre los registros de dos o más tablas y combinar el resultado en una tabla nueva, llamada JOIN.

Genere combinaciones con la instrucción SELECT que especifica las condiciones de la combinación.

A partir de 4D v15 R4, las combinaciones externas donde participan dos tablas y las combinaciones externas donde participan tres o más tablas son diferentes implementaciones y no siguen las mismas reglas. Por favor, consulte a continuación la sección que corresponda a sus necesidades.

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

Para ilustrar cómo funcionan las uniones, vamos a utilizar la siguiente base de datos a lo largo de esta sección:

  • Employees
    namedepIDcityID
    Alan1030
    Anne1139
    Bernard1033
    Fabrice1235
    Martin1530
    PhilipNULL33
    Thomas10NULL
  • Departments
    depIDdepName
    10Program
    11Engineering
    NULLMarketing
    12Development
    13Quality
  • Cities
    cityIDcityName
    30Paris
    33New York
    NULLBerlin

Si lo desea, puede generar esta base automáticamente ejecutando el siguiente código:

 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

Una combinación interna (inner join) está basada en una comparación de igualdad entre dos columnas.

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

Este el resultado de esta join:

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program

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

Puede generar combinaciones externas con 4D (OUTER JOINs). En una combinación 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 combinaciones 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 combinaciones externas explícitas son soportadas por 4D.

Con combinaciones externas de dos tablas, las condiciones pueden ser complejas, pero siempre deben basarse en una comparación de igualdad entre las columnas incluidas en la unión. Por ejemplo, no es posible utilizar el operador >= en una condición de combinación explícita. Todo tipo de comparación se puede utilizar en una combinación implícita. Internamente, las comparaciones de igualdad se llevan a cabo directamente por el motor 4D, lo que asegura una ejecución rápida.

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

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program
Martin15NULLNULL
PhilipNULLNULLNULL

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

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program
NULLNULLNULLMarketing
NULLNULL13Quality

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

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program
Martin15NULLNULL
PhilipNULLNULLNULL
NULLNULLNULLMarketing
NULLNULL13Quality

A partir de 4D v15 R4, el servidor SQL integrado extiende el soporte de combinaciones externas SQL a búsquedas que involucran tres o más tablas. Esta implementación específica tiene sus propias reglas y limitaciones, que se describen en esta sección.

Como las combinaciones externas de dos tablas, las combinaciones externas con tres o más tablas pueden ser LEFT, RIGHT, o FULL. Para obtener información general sobre las combinaciones externas, consulte el párrafo anterior Combinaciones externas con dos tablas.

A diferencia de las combinaciones externas con dos tablas, las combinaciones externas con tres o más tablas soportan varios operadores de comparación, además de los de igualdad (=): <, >, >=, o <=. Estos operadores se pueden combinar dentro de las cláusulas ON.

  • Cada cláusula ON combinación externa explícita debe referenciar exactamente dos tablas, ni más ni menos. Cada tabla combinada debe ser referenciada al menos una vez en las cláusulas ON.
  • Una de las tablas debe provenir de la parte izquierda inmediata de la cláusula JOIN y la otra, de la derecha inmediata.

Por ejemplo, la siguiente búsqueda se ejecutará con éxito:

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T3.ID) -- acá T2 está a la izquierda y T3 a la derecha
ON T1.ID=T3.ID -- acá T1 está a la izquierda y T3 a la derecha

Con nuestras tres tablas, este ejemplo podría 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

Estos son los resultados:

aNameaEmpDepIDaEmpCityIDaDepIDaDepNameaCityIDaCityName
Alan103010ProgramNULLNULL
Bernard103310Program30Paris
Anne113911Engineering33New York
Fabrice123512DevelopmentNULLNULL
Thomas10NULL10ProgramNULLNULL
NULLNULLNULLNULLMarketingNULLNULL
NULLNULLNULL13QualityNULLNULL

Por otra parte, las siguientes  tres búsquedas se rechazarán ya que violan ciertas reglas:

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T1.ID) -- acá T2 está a la izquierda pero T1 no está presente en la derecha inmediata
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 -- acá T3 y T4 vienen del lado derecho de la cláusula JOIN y ninguna tabla del lado izquierdo

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T3.ID)
ON T1.ID=T3.ID AND T1.ID=T2.ID -- acá más de dos tablas están siendo utilizadas en la cláusula ON: T1, T2 y T3

En general, si las tablas (Tx1, Tx2 ..., TXN) a la izquierda de la cláusula JOIN y las tablas (Ty1, Ty2 ..., Tym) a la derecha se combinaron, a continuación la expresión ON debe hacer referencia exactamente a una tabla de la izquierda Txa y exactamente una tabla Tyb a la derecha.

No soportado en la cláusula ONSoportado en la cláusula ON
Operaciones booleanasORAND y NOT
Predicado y funcionesIS NULL, COALESCETodos los demás predicados y funciones integradas (se pueden usar en cualquier combinación deseada)
Referencias a variables 4D-Soportadas sin restricciones
Llamadas a métodos 4DCuando la cláusula JOIN actual de ambos lados izquierdo o derecho es una combinación externa explícitaTodos los demás casos (ver el ejemplo a continuación)
El siguiente ejemplo con una llamada al método 4D es soportado porque no hay sub combinaciones no-internas a combinar:

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

Por otra parte, este ejemplo de llamada al método 4D no se soporta porque no se están combinando sub combinaciones no internas:

SELECT * FROM
(T1 LEFT JOIN T2 ON T1.ID=T2.ID)
LEFT JOIN -- Ambos lados izquierdo y derecho de esta cláusula de combinación contienen combinaciones LEFT explícitas
(T3 LEFT JOIN T4 ON T3.ID=T4.ID)
ON T1.ID={FN My4DCall (T4.ID) AS INT32} -- no están siendo combinadas sub combinaciones internas

  • Referencias a Vistas SQL no están permitidas en la declaración de combinación explícita
  • No se soportan sub búsquedas que utilizan combinaciones externas. Las siguientes serán rechazadas:
    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)

anterior
siguiente

 
PROPIEDADES 

Producto: 4D
Tema: Utilizar SQL en 4D

 
HISTORIA 

 
ARTICLE USAGE

Manual de SQL ( 4D v16)