4D v13.4

Support of joins

Home

 
4D v13.4
Support of joins

Support of joins  


 

 

The SQL engine of 4D extends the support of joins.

Join operations may be inner or outer, implicit or explicit. Implicit inner joins are support via the SELECT command. You can also generate explicit inner and outer joins using the SQL JOIN keyword.

Note: The current implementation of joins in the 4D SQL engine does not include:

  • natural joins.
  • the USING construct on inner joins.

Join operations are used to make connections between the records of two or more tables and combine the result in a new table, called a join. 

You generate joins via SELECT statements that specify the join conditions. With explicit joins, these conditions can be complex but they must always be based on an equality comparison between the columns included in the join. For example, it is not possible to use the >= operator in an explicit join condition. Any type of comparison can be used in an implicit join.
Internally, equality comparisons are carried out directly by the 4D engine, which ensures rapid execution. 

Note: Usually, in the database engine, the table order is determined by the order specified during the search. However, when you use joins, the order of the tables is determined by the list of tables. In the following example:
SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T2.depID = T1.depID;
... the order of the tables is T1 then T2 (as they appear in the list of tables) and not T2 then T1 (as they appear in the join condition).

An inner join is based on a comparison to find matches between two columns. For example, if we consider the three following tables:

  • Employees
    namedepIDcityID
    Alan1030
    Anne1139
    Bernard1033
    Mark1235
    Martin1530
    PhilipNULL33
    Thomas10NULL
  • Departments
    depIDdepName
    10Program
    11Engineering
    NULLMarketing
    12Development
    13Quality

  • Cities
    cityIDcityName
    30Paris
    33New York
    NULLBerlin

Note: The example structure above will be used throughout this chapter. 

Here is an example of an implicit inner join:

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

In 4D, you can also use the JOIN keyword to specify an explicit inner join:

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

You can insert this query into 4D code as follows:

 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

Here are the results of this join:

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program

Note that neither the employees named Philip or Martin nor the Marketing or Quality departments appear in the resulting join because:

  • Philip does not have a department associated with his name (NULL value),
  • The department ID associated with Martin’s name does not exist in the Departments table,
  • There is no employee associated with the Quality department (ID 13),
  • The Marketing department does not have an ID associated with it (NULL value).

A cross or Cartesian join is an inner join for which no WHERE nor ON clauses have been specified. It consists in associating each row of one table with each row of another table.
The result of a cross join is the Cartesian product of the tables, containing a x b rows, where a is the number of rows in the first table and b is the number of rows in the second table. This product represents every possible combination formed by the concatenation of the rows of both tables. 

Each of the following syntaxes are equivalent:

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

Here is an example of 4D code with a cross join:

 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

Here is the result of this join with our example database:

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1110Program
Bernard1010Program
Mark1210Program
Martin1510Program
PhilipNULL10Program
Thomas1010Program
Alan1011Engineering
Anne1111Engineering
Bernard1011Engineering
Mark1211Engineering
Martin1511Engineering
PhilipNULL11Engineering
Thomas1011Engineering
Alan10NULLMarketing
Anne11NULLMarketing
Bernard10NULLMarketing
Mark12NULLMarketing
Martin15NULLMarketing
PhilipNULLNULLMarketing
Thomas10NULLMarketing
Alan1012Development
Anne1112Development
Bernard1012Development
Mark1212Development
Martin1512Development
PhilippeNULL12Development
Thomas1012Development
Alain1013Quality
Anne1113Quality
Bernard1013Quality
Mark1213Quality
Martin1513Quality
PhilipNULL13Quality
Thomas1013Quality

Note: For performance reasons, cross joins should be used with precaution.

You can now generate outer joins with 4D. With outer joins, it is not necessary for there to be a match between the rows of joined tables. The resulting table contains all the rows of the tables (or of at least one of the joined tables) even if there are no matching rows. This means that all the information of a table can be used, even if the rows are not completely filled in between the different joined tables. 

There are three types of outer joins, specified using the LEFT, RIGHT and FULL keywords. LEFT and RIGHT are used to indicate the table (located to the left or right of the JOIN keyword) where all the data must be processed. FULL indicates a bilateral outer join.

Note: Only explicit outer joins are supported by 4D.

The result of a left outer join (or left join) always contains all the records for the table located to the left of keyword even if the join condition does not find a matching record in the table located to the right. This means that for each row in the left table where the search does not find any matching row in the right table, the join will still contain this row but it will have NULL values in each column of the right table. In other words, a left outer join returns all the rows of the left table plus any of those of the right table that match the join condition (or NULL if none match). Note that if the right table contains more than one row that matches the join predicate for a single row of the left table, the values of the left table will be repeated for each distinct row of the right table. 

Here is an example of 4D code with a left outer join:

 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

Here is the result of this join with our example database (additional rows shown in red):

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program
Martin15NULLNULL
PhilipNULLNULLNULL

A right outer join is the exact opposite of a left outer join. Its result always contains all the records of the table located to the right of the JOIN keyword even if the join condition does not find any matching record in the left table. 

Here is an example of 4D code with a right outer join:

 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

Here is the result of this join with our example database (additional rows shown in red):

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program
NULLNULLNULLMarketing
NULLNULL13Quality

A full outer join simply combines together the results of a left outer join and a right outer join. The resulting join table contains all the records of the left and right tables and fills in the missing fields on each side with NULL values. 

Here is an example of 4D code with a full outer join:

 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

Here is the result of this join with our example database (additional rows shown in red):

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program
Martin15NULLNULL
PhilipNULLNULLNULL
NULLNULLNULLMarketing
NULLNULL13Quality

It is possible to combine several joins in the same SELECT statement. It is also possible to mix implicit or explicit inner joins and explicit outer joins.

Here is an example of 4D code with multiple joins:

 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

Here is the result of this join with our example database:

aNameaEmpDepIDaEmpCityIDaDepIDaDepNameaCityIDaCityName
Alan103010Program30Paris
Anne113911Engineering0    
Bernard103310Program33New York
Mark123512Development 0
Thomas10NULL10Program0   

 
PROPERTIES 

Product: 4D
Theme: Using SQL in 4D