LATERAL is Your Pal to Create Native Column Variables in SQL – Java, SQL and jOOQ.

The usual SQL WITH clause has been tremendously useful in structuring SQL queries. As an alternative of nesting every part in unreadable derived tables like this:

SELECT actor_id, title, COUNT(*)
FROM (
  SELECT actor_id, first_name || ' ' || last_name AS title
  FROM actor
) AS a
JOIN film_actor AS fa USING (actor_id)
GROUP BY actor_id, title
ORDER BY COUNT(*) DESC
LIMIT 5

Individuals have began transferring the logic up entrance, identical to in another programming language, the place we declare issues first, lexically, then use them:

WITH a AS (
  SELECT actor_id, first_name || ' ' || last_name AS title
  FROM actor
)
SELECT actor_id, title, COUNT(*)
FROM a
JOIN film_actor AS fa USING (actor_id)
GROUP BY actor_id, title
ORDER BY COUNT(*) DESC
LIMIT 5;

Each queries will produce the 5 actors with essentially the most movies from the Sakila database:

|actor_id|title          |rely|
|--------|--------------|-----|
|107     |GINA DEGENERES|42   |
|102     |WALTER TORN   |41   |
|198     |MARY KEITEL   |40   |
|181     |MATTHEW CARREY|39   |
|23      |SANDRA KILMER |37   |

However one thing about that is off. We’ve to create a brand new desk a, and use that desk as an alternative of the unique actor desk. It looks like we’re utilizing the mistaken abstraction right here to get the results of merely aliasing a column expression.

The examples are nonetheless quite simple, however we’ve all seen the five hundred line SQL monsters the place derived tables cascade to a number of ranges deep.

LATERAL to the rescue

The SQL:1999 normal specifies the , which is SQL’s means of permitting for a derived desk (a subquery within the FROM clause) to entry all of the lexically previous objects within the FROM clause. It’s a bit bizarre when it comes to syntax, I personally suppose that Microsoft SQL Server has a a lot nicer answer for this idea by way of APPLY. Oracle helps each syntaxes (normal and T-SQL’s). Db2, Firebird, MySQL, PostgreSQL solely have LATERAL.

The principle use-case of utilizing LATERAL is to do fancy queries like top-n-per-category queries. However utilizing LATERAL, we are able to now additionally transfer the column alias proper the place it belongs, conceptually, subsequent to (“laterally”) the JOIN tables within the FROM clause. There are primarily two methods to do that:

As a desk record aspect

Desk lists have gone a bit out of vogue besides amongst hard-core Oracle oldschool followers who prefer to proceed utilizing (+) for outer joins, partying prefer it’s 1989. However with LATERAL, maybe you would possibly recognize desk lists once more? Test this out:

SELECT actor_id, title, COUNT(*)
FROM 
  actor JOIN film_actor AS fa USING (actor_id),
  LATERAL (SELECT first_name || ' ' || last_name AS title) AS t
GROUP BY actor_id, title
ORDER BY COUNT(*) DESC
LIMIT 5;

Isn’t that great? The final aspect within the FROM clause is a set of native variables derived from the earlier FROM clause components. We will do that cascadingly, too!

SELECT actor_id, title, name_length, COUNT(*)
FROM 
  actor JOIN film_actor AS fa USING (actor_id),
  LATERAL (SELECT first_name || ' ' || last_name AS title) AS t1,
  LATERAL (SELECT size(title) AS name_length) AS t2
GROUP BY actor_id, title, name_length
ORDER BY COUNT(*) DESC
LIMIT 5;

This produces:

|actor_id|title          |name_length|rely|
|--------|--------------|-----------|-----|
|107     |GINA DEGENERES|14         |42   |
|102     |WALTER TORN   |11         |41   |
|198     |MARY KEITEL   |11         |40   |
|181     |MATTHEW CARREY|14         |39   |
|23      |SANDRA KILMER |13         |37   |

A be part of tree aspect

In case you’re not about to resurrect the outdated desk record syntax for this use-case, you may all the time simply CROSS JOIN any proper the place it belongs. For instance:

SELECT actor_id, title, COUNT(*)
FROM actor
CROSS JOIN LATERAL (SELECT first_name || ' ' || last_name AS title) AS t
JOIN film_actor AS fa USING (actor_id)
GROUP BY actor_id, title
ORDER BY COUNT(*) DESC
LIMIT 5;

Once more, should you require a number of cascading steps of native variables, simply CROSS JOIN extra such objects:

SELECT actor_id, title, name_length, COUNT(*)
FROM actor
CROSS JOIN LATERAL (SELECT first_name || ' ' || last_name AS title) AS t1
CROSS JOIN LATERAL (SELECT size(title) AS name_length) AS t2
JOIN film_actor AS fa USING (actor_id)
GROUP BY actor_id, title, name_length
ORDER BY COUNT(*) DESC
LIMIT 5;

The tradeoff is straightforward:

WITH helps declare every part up entrance, earlier than utilizing issues. However like unusual derived tables, they require you to suppose extra about the way to nest issues.
LATERAL helps declare variables proper subsequent to the unique tables that include the variable contents, with out having to derive these authentic tables. The remainder of the question can nonetheless work with the unmodified, underived authentic desk, which makes refactoring issues and reasoning about issues a lot simpler, no less than in my view.

I can’t stress sufficient how helpful that is, as you may see within the instance. As a result of the FROM clause is the primary clause of your question, within the logical order of operations. Meaning, something you declare in FROM can be utilized in every single place else within the question! The examples are utilizing the column expressions in GROUP BY, for instance.

Utilizing T-SQL APPLY

Oracle and SQL Server have a syntax that I personally discover extra intuitive: APPLY. As a result of what we’re doing right here is we’re making use of a perform (or subquery, which is a form of perform) to a desk, making a cross product between the desk and the perform (or subquery) consequence.

Have a look at this Oracle instance:

SELECT actor_id, title, name_length, COUNT(*)
FROM actor
CROSS APPLY (SELECT first_name || ' ' || last_name AS title FROM twin)
CROSS APPLY (SELECT size(title) AS name_length FROM twin)
JOIN film_actor USING (actor_id)
GROUP BY actor_id, title, name_length
ORDER BY COUNT(*) DESC
FETCH FIRST 5 ROWS ONLY;

It does the very same factor because the earlier CROSS JOIN LATERAL instance.