About Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

The SQL Language’s Most Missing Feature

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.
 

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

2 Responses to "The SQL Language’s Most Missing Feature"

  1. Are you aware that you can replace

    WHERE first_name || ‘ ‘ || last_name LIKE ‘L% E%’

    with

    HAVING name LIKE ‘L% E%’

    This means that you can use “(expression) AS alias” in your SELECT clause, and everywhere else you can simply refer to “alias” instead of repeating “(expression)”.

    • Lukas Eder says:

      Again, referring to “name” from HAVING is something that works quirkily in some SQL dialects, but is not really part of the SQL language. Besides, very often you don’t want to project an expression in the SELECT clause just to reuse it amongst other SELECT clauses, which is the point of the article.

Leave a Reply


9 − = six



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close