4D v16Support of joins |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
4D v16
Support of joins
|
name | depID | cityID |
Alan | 10 | 30 |
Anne | 11 | 39 |
Bernard | 10 | 33 |
Fabrice | 12 | 35 |
Martin | 15 | 30 |
Philip | NULL | 33 |
Thomas | 10 | NULL |
depID | depName |
10 | Program |
11 | Engineering |
NULL | Marketing |
12 | Development |
13 | Quality |
cityID | cityName |
30 | Paris |
33 | New York |
NULL | Berlin |
If you want, you can generate this database automatically by executing the following code:
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
An inner join is based on a comparison to find matches between two columns.
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 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
Here are the results of this 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 that neither the employees named Philip or Martin nor the Marketing or Quality departments appear in the resulting join because:
You can 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.
With two-table outer joins, 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
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 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
Here is the result of this join with our example database (additional rows shown in red):
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 |
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 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
Here is the result of this join with our example database (additional rows shown in red):
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 |
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 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
Here is the result of this join with our example database (additional rows shown in red):
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 |
Starting with 4D v15 R4, the built-in SQL server extends the support of SQL outer joins to queries involving three or more tables. This specific implementation has its own rules and limitations, which are described in this section.
Like two-table outer joins, outer joins with three or more tables can be LEFT, RIGHT, or FULL. For general information on outer joins, please refer to the Outer joins with two tables paragraph above.
Unlike two-table outer joins, outer joins with three or more tables support several comparison operators, in addition to the equality (=): <, >, >=, or <=. These operators can be mixed within the ON clauses.
For example, the following query will be executed with success:
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
With our three tables, this example could be:
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
Here are the results:
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 |
On the other hand, the following three queries will be rejected since they violate certain rules:
SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T1.ID) -- here T2 is on the left but T1 is not present in the immediate right
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 -- here both T3 and T4 come from the right side of the JOIN clause and no tables at all come from the left side
SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T3.ID)
ON T1.ID=T3.ID AND T1.ID=T2.ID -- here more than two tables are being used in the ON clause: T1, T2, and T3
In general, if tables (Tx1, Tx2..., Txn) on the left of JOIN clause and tables (Ty1, Ty2..., Tym) on the right are being joined, then the ON expression must reference exactly one left table Txa and exactly one right table Tyb.
Not supported in the ON clause | Supported in the ON clause | |
Boolean operations | OR | AND and NOT |
Predicate and functions | IS NULL, COALESCE | All other predicates and built-in functions (can be used in any combination desired) |
4D variable references | - | Supported without restriction |
4D method calls | When either left or right side of the current JOIN clause is an explicit outer join | Any other cases (see example below) |
The following example with a 4D method call is supported because there are no non-inner sub-joins to join:
SELECT * FROM T1
LEFT JOIN T2
ON T1.ID={FN My4DCall (T2.ID) AS INT32}
On the other hand, this example of 4D method call is not supported because non-inner sub-joins are being joined:
SELECT * FROM
(T1 LEFT JOIN T2 ON T1.ID=T2.ID)
LEFT JOIN -- Both left and right sides of this join clause contain explicit LEFT joins
(T3 LEFT JOIN T4 ON T3.ID=T4.ID)
ON T1.ID={FN My4DCall (T4.ID) AS INT32} -- non-inner sub-joins are being joined
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)
Produkt: 4D
Thema: Using SQL in 4D
4D - SQL Reference ( 4D v16)