SQL is also awesome in many ways. We can write out the most complex truths and facts and have the database tell us the answer in no time.
But the SQL language is arguably the most beautiful programming language out there. It has so many caveats that people like me get ridiculously rich selling consulting services just to
merely explain its semantics. One of the best examples of how twisted the SQL language is, is
Alex Bolenok’s article about SQL NULL on Tech.Pro.
Now, one of the biggest criticisms of SQL is its verbosity. Most SQL dialects have virtually no constructs to avoid repetition. It is a common use case in SQL to filter, group, select, and order by the same expression. An example:
SELECT first_name || ' ' || last_name
FROM customers
WHERE first_name || ' ' || last_name LIKE 'L% E%'
GROUP BY first_name || ' ' || last_name
ORDER BY first_name || ' ' || last_name
You might write a different query for the same, sure. But even if you didn’t doesn’t it bother you that there is hardly any way to re-use the
first_name || ' ' || last_name concatenation expression?
Well, you can at least reuse it in the
ORDER BY clause:
SELECT first_name || ' ' || last_name AS name
FROM customers
WHERE first_name || ' ' || last_name LIKE 'L% E%'
GROUP BY first_name || ' ' || last_name
ORDER BY name
And in MySQL, you can also reuse it in the
GROUP BY clause,
although we think that this is a bad idea:
SELECT first_name || ' ' || last_name AS name
FROM customers
WHERE first_name || ' ' || last_name LIKE 'L% E%'
GROUP BY name
ORDER BY name
Standard SQL solutions for column reuse
Sure, you could create a derived table:
SELECT name
FROM (
SELECT first_name || ' ' || last_name
FROM customers
) c(name)
WHERE name LIKE 'L% E%'
GROUP BY name
ORDER BY name
… or a common table expression, which is basically a reusable derived table, or a local view:
WITH c(name) AS (
SELECT first_name || ' ' || last_name
FROM customers
)
SELECT name
FROM c
WHERE name LIKE 'L% E%'
GROUP BY name
ORDER BY name
But why do I even have to create a table to reuse / rename a simple column expression? Why can’t I just simply use something like a
common column expression? Like this?
-- Common column expression that can be appended
-- to any table expression.
SELECT name
FROM customers
WITH name AS first_name || ' ' || last_name
WHERE name LIKE 'L% E%'
GROUP BY name
ORDER BY name
Note that the common column expression would be scoped to the table source. In other words, it only makes sense in the context of a
FROM clause and the tables specified in the
FROM clause. In a way, the following two expressions would be exactly equivalent:
-- Proposed syntax
FROM customers
WITH name AS first_name || ' ' || last_name
-- Existing syntax
FROM (
SELECT customers.*,
first_name || ' ' || last_name AS name
FROM customers
) AS customers
The proposed syntax could also be applied to joined tables:
-- Proposed syntax
FROM customers
AS c
WITH name AS c.first_name || ' ' || c.last_name
JOIN addresses
AS a
WITH address AS a.street || '\n' || a.city
ON c.address_id = a.address_id
WITH full_address AS c.name || '\n' || a.address
-- Or alternatively, if you don't need to tightly
-- scope these common column expressions to their
-- corresponding tables:
FROM customers AS c
JOIN addresses AS a
ON c.address_id = a.address_id
WITH name AS c.first_name || ' ' || c.last_name,
address AS a.street || '\n' || a.city,
full_address AS name || '\n' || address
So in plain English, the new
WITH clause could be appended to any type of table expression. With parentheses and comments, the first among the above examples would read:
FROM (
customers AS c
-- The "name" column is appended to "c"
WITH name AS c.first_name || ' ' || c.last_name
)
JOIN (
addresses AS a
-- The "address" column is appended to "a"
WITH address AS a.street || '\n' || a.city
) ON c.address_id = a.address_id
-- The "full_address" column is appended to the
-- above joined table expression
WITH full_address AS c.name || '\n' || a.address
The above syntax would then again be exactly equivalent to this:
FROM (
SELECT
c.*, a.*,
c.name || '\n' || a.address AS full_address
FROM (
SELECT c.*,
c.first_name || ' ' || c.last_name
FROM customers AS c
) c
JOIN (
SELECT a.*,
a.street || '\n' || a.city
FROM addresses AS a
) a
ON c.address_id = a.address_id
)
SQL’s most missing language feature
Since
SQL:1999, we luckily have common table expressions – the
WITH clause that can be prepended to any
SELECT statement (and to other DML statements in some dialects). Using common table expressions, we can avoid repeating commonly used derived tables.
But such a thing is not possible for columns. The one language feature SQL is in most dire need of are thus
common column expressions.
Like this:
Like Loading...
We could use
LATERAL JOIN/CROSS APPLYto achieve it(https://stackoverflow.com/questions/8840228/postgresql-using-a-calculated-column-in-the-same-query/36530228#36530228):PostgreSQL https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e28e4aaeea857cb300ab8e004a2c5f57:
SELECT s.full_name
FROM customers c
JOIN LATERAL (VALUES(c.first_name || ‘ ‘ || c.last_name)) s(full_name) ON TRUE
WHERE s.full_name LIKE ‘L% E%’
GROUP BY s.full_name
ORDER BY s.full_name;
or even shorter https://dbfiddle.uk/?rdbms=postgres_11&fiddle=347a00245aeb29ac95ea7146e8e6ce82:
SELECT s.full_name
FROM customers c
,LATERAL (VALUES(c.first_name || ‘ ‘ || c.last_name)) s(full_name)
WHERE s.full_name LIKE ‘L% E%’
GROUP BY s.full_name
ORDER BY s.full_name
SQL Server https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ab1ffe7b9e22c59b82d4069e5820d9e9:
SELECT s.full_name
FROM customers c
CROSS APPLY (VALUES(c.first_name + ‘ ‘ + c.last_name)) s(full_name)
WHERE s.full_name LIKE ‘L% E%’
GROUP BY s.full_name
ORDER BY s.full_name;
Same will work for Oracle (CROSS APPLY plus SELECT c.first_name || ‘ ‘ || c.last_name FROM dual).
To sum up RDBMS needs to only support CROSS APPLY/LATERAL construct.
That is a very elegant solution and clever idea, thanks for sharing this!
Thanks. I use that solution starting from SQL Server 2008. It allows me to avoid wrapping calculated columns with outer query/copy-paste expression multiple times.
I will share this idea on SO(https://stackoverflow.com/questions/19185043/how-to-use-a-calculated-column-to-calculate-another-column-in-the-same-view/52024045#52024045).