In-line CTE in SQL

Until today I thought that the CTE had to go before the main SELECT clause, but it appears that a CTE can be dropped into any subselect. As an example:

-- tested on BigQuery, Redshift, MySQL, Postgres
SELECT CONCAT(Odd.num, Even.num) FROM
  (WITH Numbers AS (SELECT 1 AS num UNION ALL SELECT 3 UNION ALL SELECT 5) SELECT * FROM Numbers) Odd,
  (WITH Numbers AS (SELECT 2 AS num UNION ALL SELECT 4 UNION ALL SELECT 6) SELECT * FROM Numbers) Even

Outside of just playing around with things though, is there ever a case for this sort of "embedded" CTE, or is there no reason to ever have it outside of the top, as if it's a variable declaration?

Solution

Not only that, you can have CTEs inside of CTEs.

WITH CTE1 AS (
  WITH CTE2 AS (
    …
  )
)
SELECT …

I had to work on someone’s query that did this 13 levels deep. What a pain. The main reason this isn’t good is that it makes a mess of readability. I think what you are describing isn’t quite as bad as it keeps subsections of the query together. Doing oddball things like this generally leads to confusion when others try to understand your query.