Syntax error: WITH is not valid input in this position

Question

So I have this similar request

    WITH customers_in_usa AS (
        SELECT 
           customerName, state
        FROM
           customers
        WHERE
           country = 'USA'
    ) SELECT 
        customerName
    FROM
        customers_in_usa
    WHERE
        state = 'CA'
    ORDER BY customerName;

But when wrote it I caught an error: 'WITH is not valid input in this position' error_picture. Can you help me to understand what's wrong in this code?

Answer 1

WITH customers_in_usa AS is for now invalid MySQL code. MySQL will support CTE's in the future in MySQL version 8..

You could rewrite your SQL code, that should give the same results.

SELECT 
    customerName
  , state
FROM 
   customers 
WHERE
   country = 'USA'
 AND
   state = 'CA'
ORDER BY
   customerName

Answer 2

MySQL doesn't support common table expressions and WITH syntax until version 8.0.1.

Version 8.0 is still not production-ready as I write this (but it's in Release Candidate status, so it'll be GA pretty soon).

---

Update: MySQL 8.0 released its first GA version 8.0.11 on 2018-04-19.