This post was originally written by Glenn Paulley and posted to sybase.com in July of 2008. It demonstrates the depth of SQL support provided inside SQL Anywhere.
I'd like to draw your attention to an SQL language feature that is unlikely to make any analyst's top-ten list. This SQL feature is APPLY. APPLY is used in an SQL statement's FROM clause as follows:
table-expression [CROSS | OUTER] APPLY table-expression
An APPLY operator is used in place of a join operator. Like a join, APPLY acts on two table expressions: a left table expression and a right table expression. However, the APPLY operator does not use an ON clause - the ON condition is implicitly 1=1. Instead on an explicit join condition, the right table expression may (and usually does) contain outer references to the left table expression. The right table expression is evaluated once for each row from the left table expression, and the final result is the combination of all the resulting rows.
An example:
Consider the following procedure, which returns the names of all employees in a given department whose salaries are greater than $80,000.
CREATE PROCEDURE high_salary( IN dept INTEGER )
RESULT ( name LONG VARCHAR )
BEGIN
SELECT E.emp_fname || ' ' || E.emp_lname
FROM Employee E
WHERE E.dept_id = dept AND E.salary > 80000;
END
The following query uses a CROSS APPLY to join each row in the department table to the result of the high_salary procedure. The procedure is evaluated once for each row in the department table.
SELECT D.dept_name, HS.name
FROM Department D CROSS APPLY high_salary( D.dept_id ) as HS
The semantics of CROSS APPLY are similar to Cartesian product or inner join. For left-outer join semantics, one can utilize OUTER APPLY. As an example, the following statement uses an OUTER APPLY to join each row in the department table to the results of the high_salary procedure. This will preserve the rows in the department table for which the high_salary procedure returns no rows.
SELECT D.dept_name, HS.name
FROM Department D OUTER APPLY high_salary( D.dept_id ) as HS
The astute reader will recognize that there is little difference between APPLY and other types of table expression constructions: essentially CROSS APPLY is identical to CROSS JOIN with a join condition of 1=1. Similarly, OUTER APPLY is semantically equivalent to OUTER JOIN. As an example, the following query is equivalent to the previous example, but uses a derived table as the right-hand side of the OUTER APPLY.
SELECT D.dept_name, HS.name
FROM Department D OUTER APPLY (
SELECT E.emp_fname || ' ' || E.emp_lname
FROM Employee E
WHERE E.dept_id = D.dept_id AND E.salary > 80000
) HS( name )
Comparison with LATERAL
Like the APPLY operators, the LATERAL keyword is used to allow references between tables in the same FROM clause. APPLY, as with LATERAL, can be used with both derived tables and table expressions, including table functions (stored procedures). However, there are two subtle differences between the APPLY operators and the LATERAL keyword.
The LATERAL keyword cannot NULL-supply rows, whereas an OUTER APPLY will. Additionally, with a LATERAL derived table, the derived table and the outer reference must be separated by a comma. For an APPLY operator, the table expression on the right and the outer reference cannot be separated by a comma, but they can be separated by any other join operator. In other words, the APPLY operator allows references to any table within the left table expression, whereas the LATERAL keyword allows references to tables outside the current table expression.
It should be noted that APPLY operators are parsed such that the table-expression on the LHS contains all possible joins, whereas the table-expression on the RHS contains only a single table node. For example, a table expression of the form:
A JOIN B OUTER APPLY C JOIN D is parsed as ((A JOIN B) OUTER APPLY C) JOIN D.
This follows the same rules as for nested (outer) join table expressions without parentheses. Consequently, the table on the RHS of an APPLY can refer to tables on the LHS separated from the APPLY operator by joins, but a table separated from an APPLY by a join on the right cannot refer to tables on the left. For example:
SELECT * FROM A CROSS JOIN B OUTER APPLY some_procedure(A.x) CROSS JOIN D // No errorSELECT * FROM A CROSS JOIN B OUTER APPLY C CROSS JOIN some_procedure(B.x) // Error: B.x is not from the most immediate table expression on the LHS of the CROSS JOIN
Also, unlike for the LATERAL keyword, SQL Anywhere does not support a special query-expression syntax for derived tables with an APPLY.
SELECT * FROM A, ( B JOIN C ON B.x=C.x ) dt // Syntax Error: cannot give the latter table expression a correlation name without LATERAL SELECT * FROM A, LATERAL( B JOIN C ON B.x=A.x ) dt // No error - reference to A.x is permitted by LATERAL, as is the use of dt SELECT * FROM A OUTER APPLY ( B JOIN C ON B.x=A.x ) dt // Syntax Error: cannot specify a derived table name dt with OUTER APPLY
CROSS and OUTER APPLY are not part of the ANSI SQL standard; they are intended for compatibility with Microsoft SQL Server.
