JOIN clauses

JOIN clauses allow records from multiple statements to be combined.

JOIN clauses, which conform to a subset of the SQL standard, do a join with the specified join condition. The join condition may be an arbitrary Boolean expression referring to the attributes in the FROM statement. The expression must be enclosed in parentheses.

The JOIN clause always modifies a FROM clause. Two named sources can be indicated in the FROM clause. Fields must be dot-qualified to indicate which source they come from, except in queries from a single table.

Self-join is supported. Statement aliasing is required for self-join.

Both input tables must result from DEFINE or RETURN statements (that is, from intermediate results). AllBaseRecords and NavStateRecords cannot be joined.

Any number of joins can be performed in a single statement.

The syntax of JOIN is as follows:

FROM <Statement> [alias]
[LEFT,RIGHT,FULL] JOIN <Statement2> [alias]
ON (Boolean expression) [, JOIN <StatementN> [alias] ON (Boolean expression)]*

If there is more than one JOIN, each statement is joined with a FROM statement.

Types of joins

EQL supports the following types of joins:

INNER JOIN: INNER JOIN joins records on the left and right sides, then filters the result records by the join condition. That means that only rows for which the join condition is TRUE are included. If you do not specify the join type, JOIN defaults to INNER JOIN.
LEFT JOIN, RIGHT JOIN, and FULL JOIN: LEFT JOIN, RIGHT JOIN, and FULL JOIN (collectively called outer joins) extend the result of an INNER JOIN with records from a side for which no record on the other side matched the join condition. When such an additional record is included from one side, the record in the join result contains NULLs for all attributes from the other side. LEFT JOIN includes all such rows from the left side, RIGHT JOIN includes all such rows from the right side, and FULL JOIN includes all such rows from either side.
CROSS JOIN: The result of CROSS JOIN is the Cartesian product of the left and right sides. Each result record has the assignments from both of the corresponding records from the two sides.
Important: CROSS JOIN should be used with caution, because it can generate very large numbers of records. For example, a CROSS JOIN of a result with 100 records and a result with 200 records would contain 20,000 records.

JOIN examples

The following INNER JOIN example finds employees whose sales in a particular subcategory account for more than 10% of that subcategory's total:

DEFINE EmployeeTotals AS 
SELECT 
   DimEmployee_FullName AS Name, 
   SUM(FactSales_SalesAmount) AS Total 
GROUP BY DimEmployee_EmployeeKey, ProductSubcategoryName;

DEFINE SubcategoryTotals AS 
SELECT 
   SUM(FactSales_SalesAmount) AS Total 
GROUP BY ProductSubcategoryName;

RETURN Stars AS 
SELECT 
   EmployeeTotals.Name AS Name, 
   EmployeeTotals.ProductSubcategoryName AS Subcategory, 
   100 * EmployeeTotals.Total / SubcategoryTotals.Total AS Pct 
FROM EmployeeTotals 
   JOIN SubcategoryTotals 
   ON (EmployeeTotals.ProductSubcategoryName = SubcategoryTotals.ProductSubcategoryName) 
HAVING Pct > 10

The following self-join using INNER JOIN computes cumulative daily sales totals per employee:

DEFINE Days AS 
SELECT 
   FactSales_OrderDateKey AS DateKey, 
   DimEmployee_EmployeeKey AS EmployeeKey, 
   DimEmployee_FullName AS EmployeeName, 
   SUM(FactSales_SalesAmount) AS DailyTotal 
GROUP BY DateKey, EmployeeKey;

RETURN CumulativeDays AS 
SELECT 
   SUM(PreviousDays.DailyTotal) AS CumulativeTotal, 
   Day.DateKey AS DateKey, 
   Day.EmployeeKey AS EmployeeKey, 
   Day.EmployeeName AS EmployeeName 
FROM Days Day 
   JOIN Days PreviousDays 
   ON (PreviousDays.DateKey <= Day.DateKey) 
GROUP BY DateKey, EmployeeKey

The following LEFT JOIN example computes the top 5 subcategories along with an Other bucket, for use in a pie chart:

DEFINE Totals AS 
SELECT 
   SUM(FactSales_SalesAmount) AS Total 
GROUP BY ProductSubcategoryName;

DEFINE Top5 AS 
SELECT 
   Total AS Total 
FROM Totals 
GROUP BY ProductSubcategoryName 
ORDER BY Total DESC PAGE(0,5);
RETURN Chart AS 
SELECT 
   COALESCE(Top5.ProductSubcategoryName, 'Other') AS Subcategory, 
   SUM(Totals.Total) AS Total 
FROM Totals 
   LEFT JOIN Top5 
   ON (Totals.ProductSubcategoryName = Top5.ProductSubcategoryName) 
GROUP BY Subcategory

The following LEFT JOIN computes metrics for each product in a particular region, ensuring all products appear in the list even if they have never been sold in that region:

DEFINE Product AS 
SELECT 
   ProductAlternateKey AS Key, 
   ProductName AS Name GROUP BY Key;

DEFINE RegionTrans AS 
SELECT 
   ProductAlternateKey AS ProductKey, 
   FactSales_SalesAmount AS Amount 
WHERE DimSalesTerritory_SalesTerritoryRegion='United Kingdom';


RETURN Results AS 
SELECT 
   Product.Key AS ProductKey, 
   Product.Name AS ProductName, 
   COALESCE(SUM(RegionTrans.Amount), 0) AS SalesTotal, 
   COUNT(RegionTrans.Amount) AS TransactionCount 
FROM Product 
   LEFT JOIN RegionTrans 
   ON (Product.Key = RegionTrans.ProductKey) 
GROUP BY ProductKey

The following FULL JOIN computes the top 10 employees' sales totals for the top 10 products, ensuring that each employee and each product appears in the result:

DEFINE TopEmployees AS 
SELECT 
   DimEmployee_EmployeeKey AS Key, 
   DimEmployee_FullName AS Name, 
   SUM(FactSales_SalesAmount) AS SalesTotal 
GROUP BY Key 
ORDER BY SalesTotal DESC 
PAGE (0,10);


DEFINE TopProducts AS 
SELECT 
   ProductAlternateKey AS Key, 
   ProductName AS Name, 
   SUM(FactSales_SalesAmount) AS SalesTotal 
GROUP BY Key 
ORDER BY SalesTotal DESC 
PAGE (0,10);

DEFINE EmployeeProductTotals AS 
SELECT 
   DimEmployee_EmployeeKey AS EmployeeKey, 
   ProductAlternateKey AS ProductKey, 
   SUM(FactSales_SalesAmount) AS SalesTotal 
GROUP BY EmployeeKey, ProductKey 
HAVING [EmployeeKey] IN TopEmployees AND [ProductKey] IN TopProducts;


RETURN Results AS 
SELECT 
   TopEmployees.Key AS EmployeeKey, 
   TopEmployees.Name AS EmployeeName, 
   TopEmployees.SalesTotal AS EmployeeTotal, 
   TopProducts.Key AS ProductKey, 
   TopProducts.Name AS ProductName, 
   TopProducts.SalesTotal AS ProductTotal, 
   EmployeeProductTotals.SalesTotal AS EmployeeProductTotal 
FROM EmployeeProductTotals 
   FULL JOIN TopEmployees 
   ON (EmployeeProductTotals.EmployeeKey = TopEmployees.Key) 
   FULL JOIN TopProducts 
   ON (EmployeeProductTotals.ProductKey = TopProducts.Key)

The following CROSS JOIN example finds the percentage of total sales each product subcategory represents:

DEFINE GlobalTotal AS 
SELECT 
   SUM(FactSales_SalesAmount) AS GlobalTotal
GROUP;

DEFINE SubcategoryTotals AS 
SELECT 
   SUM(FactSales_SalesAmount) AS SubcategoryTotal 
GROUP BY ProductSubcategoryName;


RETURN SubcategoryContributions AS 
SELECT 
   SubcategoryTotals.ProductSubcategoryName AS Subcategory, 
   SubcategoryTotals.SubcategoryTotal / GlobalTotal.GlobalTotal AS Contribution 
FROM SubcategoryTotals 
   CROSS JOIN GlobalTotal

Important: Joins can cause the Endeca Server to grow beyond available RAM. Going beyond the scale capabilities will cause very, very large materializations, intense memory pressure, and can result in an unresponsive Endeca Server.