13

Is it possible in SQL use a reference inside a Common Table Expression inside another C.T.E in the same query? Here there is an example:

WITH CT1 AS (SELECT * FROM T),
     CT2 AS (SELECT * FROM CT1)

SELECT * FROM CT2;

I tried this in SQLite3 and it works, I just wanted to know if it's part of standard SQL. Any advices concerning this argument will be highly appreciated. Thank you very much!

Mohamad Shiralizadeh
  • 8,329
  • 6
  • 58
  • 93
rickyalbert
  • 2,552
  • 4
  • 21
  • 31
  • 1
    FWIW, it works in Postgres as well, and I can't think of any reason this *wouldn't* be in the standard. – IMSoP Nov 29 '14 at 17:17
  • 1
    I can confirm that it will work in Postgres, Oracle an SQL Server. This is how it was defined in the SQL standard. –  Nov 29 '14 at 17:20
  • Thank you! Do you know if I can define them in a random order? In this example can I define ct2 before ct1? In sqlite3 the result is positive – rickyalbert Nov 29 '14 at 17:41
  • See my answer here http://stackoverflow.com/a/27146764/905902 , for an application of such "chained" CTEs – wildplasser Nov 29 '14 at 18:57

1 Answers1

31

Here are three important properties of CTEs:

  • You can refer to a CTE in subsequent CTEs or in the main body of the query.

  • You can refer to any given CTE multiple times.

  • The CTE can be used in a from clause at any level of nesting within other subqueries.

The CTEs -- as with everything in SQL -- need to be defined before they are used. So, you cannot define them in random order.

This is the standard definition of CTEs and does a good job of explaining how they are used across databases. Those three properties are key ways that they differ from subqueries.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    Can I reference a non-recursive CTE in a recursive CTE? I just tried `WITH my_non_rec_cte AS ( ... ), RECURSIVE my_rec_cte AS (...) SELECT * FROM my_rec_cte` and I get a syntax error near `RECURSIVE my_rec_cte AS ...`. – Adrian Wiik Apr 20 '22 at 11:20