Skip to Main Content

Ability to define column variables just like common table expressions

Lukas EderJul 22 2016 — edited Jul 22 2016

Common table expressions (a.k.a. subquery factoring) is a very useful tool to compose complex SQL queries. They're the only real way to declare variables in the SQL language. I often wish that something similar were possible for column expressions as well, and in fact many users do so. For instance, a lot of people struggle when they have to repeat complex expressions in SELECT and GROUP BY. These things can be solved, of course, using common table expressions or derived tables, but what if there was a more convenient syntax? I have proposed this in the past on the jOOQ blog:

The idea is that there would need to be a WITH clause (or some other keyword) right after the table reference, i.e. after the FROM clause. So, when today, we need to write:

SELECT   first_name || ' ' || last_name

WHERE    first_name || ' ' || last_name LIKE 'L% E%'

GROUP BY first_name || ' ' || last_name

ORDER BY first_name || ' ' || last_name

... with the improved syntax, we could write instead:

SELECT   name

FROM     customers

WITH     name AS first_name || ' ' || last_name

WHERE    name LIKE 'L% E%'



The whole feature would just be syntax sugar. The expression could be inlined and expanded immediately into the surrounding SQL statement, without any impact on performance. It just helps users re-use complex expressions across SELECT, WHERE, GROUP BY, HAVING, ORDER BY and other clauses.

I think this would be a real killer addition, also to the SQL standard, not just to Oracle SQL.

Post Details
Added on Jul 22 2016