lateral clauses in SQL

What alternatives offered for lateral clauses in SQL server, MySQL and Oracle:

select name, salary, avg_salary
from instructor I1,
lateral (select avg(salary) as avg_salary
from instructor I2
where I2.dept_name= I1.dept_name);

 

SQL;2003 allows a subquery in the from clause that is prefixed by lateral key word to access the attributes of preceding tables or sub queries in the from clause. Example SQL is given in the question.  Without the lateral clause, the sub query cannot access the correlation variable I1 from the outer query. Currently only few SQL implementations such as IBM DB2 support the lateral clause.

The Apply operator invokes a table-valued function for every row in the outer record set. The table valued function is evaluated for each row of the outer record set. Rows returned from table valued function are joined to the row of the outer record set using UNION ALL operation. There are two types – Cross Apply or Outer Apply.
Syntax: 

SELECT <column list> FROM
left_table_source { CROSS | OUTER } APPLY right_table_source

CROSS APPLY returns only rows from the outer table (left_table_source) that produce a result set from the table-valued function (right_table_source). It is like the Left Outer
join. OUTER APPLY returns both rows that produce a result set, and rows that do not match, with NULL values in the columns produced by the table-valued function. It is like
the Full outer join operation.

MySQL – We can achieve the lateral functionality (eg: using correlated sub queries in the FROM clause) by using ON clause of a JOIN operation. But it would then fetch the
entire inner query row set to join against the outer row which may not very efficient. Correlated subquery is a subquery that uses the information from the outer query. In
where clause, the correlated sub query can be used which references columns of outer query and the inner query executes for every row of outer query.

Oracle – The LATERAL key word is supported and is used to designate sub query as a lateral inline view. In the lateral inline view, we can denote tables that appear on left of
lateral inline view in the FROM clause of a query. This kind of left correlation can be specified anywhere within the sub query (like SELECT, FROM, and WHERE clauses)
and at any nesting level.

Example:

SELECT * FROM employees e, LATERAL (SELECT * FROM departments d
WHERE e.department_id = d.department_id);

Also equivalent of the SQL server’s APPLY clause in Oracle is a lateral JOIN. A lateral join in Oracle is when you join a table A with a function F that outputs rows and this
function has columns of A as parameters.

 

Leave a Reply