An apply expression is an easy way to specify joins where the right side is dependent upon the left. For example, use an apply expression to evaluate a procedure or derived table once for each row in a table expression. Apply expressions are placed in the FROM clause of a SELECT statement, and do not permit the use of an ON clause.

An APPLY combines rows from multiple sources, similar to a JOIN except that you cannot specify an ON condition for APPLY. The main difference between an APPLY and a JOIN is that the right side of an APPLY can change depending on the current row from the left side. For each row on the left side, the right side is recalculated and the resulting rows are joined with the row on the left. In the case where a row on the left side returns more than one row on the right, the left side is duplicated in the results as many times as there are rows returned from the right.

There are two types of APPLY you can specify: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows on the left side that produce results on the right side. OUTER APPLY returns all rows that a CROSS APPLY returns, plus all rows on the left side for which the right side does not return rows (by supplying NULLs for the right side).

The syntax of an apply expression is as follows:

table-expression { CROSS | OUTER } APPLY table-expression

 Example

CREATE PROCEDURE EmployeesWithHighSalary( IN dept INTEGER )
  RESULT ( Name LONG VARCHAR )
   BEGIN
    SELECT E.GivenName || ' ' || E.Surname 
        FROM Employees E
        WHERE E.DepartmentID = dept AND E.Salary > 80000;
   END;

SELECT D.DepartmentName, HS.Name
    FROM Departments D
    OUTER APPLY EmployeesWithHighSalary( D.DepartmentID ) AS HS;

SELECT D.DepartmentName, HS.Name
    FROM Departments D
    CROSS APPLY EmployeesWithHighSalary( D.DepartmentID ) AS HS;

SELECT D.DepartmentName, HS.Name
   FROM Departments D
    CROSS APPLY (
        SELECT E.GivenName || ' ' || E.Surname
            FROM Employees E
            WHERE E.DepartmentID = D.DepartmentID AND E.Salary > 80000
    ) HS( Name );

 See also