MySQL 5.7 easy alternative to WITH clause: Derived Tables

I have recently worked in a project that had a mixture of MySQL 8 and MySQL 5.7. Let’s not talk about it.

We can however, talk about the time when I naively used the WITH clause in my local MySQL 8 with no problems, only to run into a “You have an error in your SQL syntax;” during deployment into one Mysql 5.7 instance.

Considered Solutions

When considering the possible solutions to the issue, the two main things that came to mind were:

1. Replace the WITH clause with a View Table
2. Replace the WITH clause with a Derived Table

Using Derived Tables

All things considered, I decided to go with the second option as it was a better fit with this specific project’s structure.

Replacing WITH with a Derived Table is easy. Bellow is the (simplified) changes I made.

The Solution Used

Replace this:

WITH my_table AS (
    SELECT
      first_name,
      last_name,
      CONCAT(first_name, last_name) AS full_name
    FROM
      user
    )
SELECT
    *
FROM my_table
WHERE first_name = 'John'

With this:

SELECT
    *
FROM (
    SELECT
        first_name,
        last_name,
        CONCAT(first_name, last_name) AS full_name
    FROM
        user
    ) my_table
WHERE first_name = 'John'

Thoughts

Derived Tables and the WITH clause essentially do the same thing. Derived Tables will work on both MySQL 5.7 and MySQL 8, while the WITH clause is not supported in MySQL 5.7.

I prefer using WITH, but the slight loss in syntax is a small price to pay for backward compatibility and peace of mind when supporting older MySQL versions.