Multiple Cte in Single Query

Keeping it simple and how to do multiple CTE in a query

You can have multiple CTEs in one query, as well as reuse a CTE:

WITH    cte1 AS
        (
        SELECT  1 AS id
        ),
        cte2 AS
        (
        SELECT  2 AS id
        )
SELECT  *
FROM    cte1
UNION ALL
SELECT  *
FROM    cte2
UNION ALL
SELECT  *
FROM    cte1

Note, however, that SQL Server may reevaluate the CTE each time it is accessed, so if you are using values like RAND(), NEWID() etc., they may change between the CTE calls.

Include multiple inline functions and multiple CTEs in a WITH clause in a single query

As ever for a "what is the syntax for ..." questions, you should refer to the official documentation.

The SELECT syntax is

query_block ::=

with_clause ::=

plsql_declarations ::=

subquery_factoring_clause ::=

The PL/SQL function syntax

function_definition ::=

body ::=

Therefore:

• A PL/SQL function's body must be terminated with a ;.

  (Note: this is a PLSQL statement terminator and not a separator in the WITH clause between PL/SQL function declarations as there is no separator character following PL/SQL function declarations.)

• There is a , character between successive sub-query factoring clauses.

• The SELECT statement does not need a ; or / statement terminator but it may be allowed/required/forbidden by the client application you are using to denote the termination of the statement.

  For example:

  • You can only pass a single statement via an OJDBC statement and, for this client, the statement terminator is forbidden.
  • In SQL Developer, when you are running a single statement then the trailing statement terminator is allowed but is optional.
  • In SQL Developer, when you are running a script then statement terminators are required between statements.

multiple cte in single select statement where ctes can refer to each other

Yes, you can reference previously declared CTEs in subsequent CTEs:

WITH cte1 as (
  SELECT t.* 
    FROM cdr.Location t),
     cte2 as (
  SELECT t.* 
    FROM cdr.Location t
    JOIN cte1 c1 ON c1.somefield = t.someField)
SELECT * 
  FROM cte1 
UNION 
SELECT * 
  FROM cte2

NOTES

1. Joining onto cte2 in the cte1 declaration wouldn't work, because the statement is executed from top down.
2. You reference a CTE like any other inline view (which it is) or table/temp table/etc by JOINing on whatever you need.

BTW: Try to formulate a better example in the future - it's good for you and the rest of the SO community who are trying to help you.

SQL export multiple CTE tables to a new table

Why can't I query all of my cte tables at once? They don't depend on each others.

Consider these things:

• While a single query can have multiple CTEs, a single CTE cannot be shared by multiple queries.
  
  • I agree this is annoying.
• Each top-level SELECT statement represents a completely separate query.
  
  • (Confusingly, CTEs (WITH x AS ( ... )) are placed before the SELECT keyword, however it is still a single query.
  • Therefore your SELECT * FROM level1 SELECT * FROM level2 statements are two separate queries.
    
    • Only the first query (SELECT * FROM level1) has access to the level1 and level2 CTEs defined before it.
  • This is why you should always use a terminating semicolon so you can not-only visually see the syntactical bounds of each statement and query, but also prevent you from making this kind of mistake again in future.
    
    • So it should have been formatted as:
      

      ------------------
      -- Query 1:
      ------------------
      WITH level1 AS (
           SELECT [order] FROM test
      ),
      level2 AS AS (
           SELECT [route], [tag] FROM test
      )
      SELECT * FROM level1;
      
      ------------------
      -- Query 2 (which is *entirely separate* from Query 1 above):
      ------------------
      SELECT * FROM level2; /* <-- Error: The `level2` CTE isn't in-scope! */
      

3. You can combine two or more SELECT queries into one by concatenating their results using UNION ALL, however you can only do this for queries with the same column-design, but your two SELECT statements have different columns ([order]) vs ([route] and [tag]) so it's nonsensical to concatenate them as-is.

But if you do want to concatenate them, using NULL for their missing columns' data, then do this:

WITH level1 AS (
    SELECT
        [order]
    FROM
        test
),
level2 AS AS (
    SELECT
        [route],
        [tag]
    FROM
        test
)
SELECT
    NULL AS [route],
    NULL AS [tag],
    [order]
FROM
    level1

UNION ALL

SELECT
    [route],
    [tag]
    NULL AS [order]
FROM
    level2;    /* <-- Note the semicolon. */

Use Multiple CTE

The first one fails because a CTE or set of CTEs can only be followed by a single statement.

You could rewrite it as

; with [cteOne] as (
  select 1 as col
)
select 'yesA' where exists (select * from [cteOne])

; with [cteTwo]  as (
  select 2 as col
)
select 'yexB' where exists (select * from [cteTwo])

Use one CTE many times

A CTE is basically a disposable view. It only persists for a single statement, and then automatically disappears.

Your options include:

• Redefine the CTE a second time. This is as simple as copy-paste from WITH... through the end of the definition to before your SET.

• Put your results into a #temp table or a @table variable

• Materialize the results into a real table and reference that

• Alter slightly to just SELECT COUNT from your CTE:

.

SELECT @total = COUNT(*)
FROM Players p 
INNER JOIN Teams t 
    ON p.IdTeam=t.Id 
INNER JOIN Leagues l 
    ON l.Id=t.IdLeague
WHERE l.Id=@idleague

---

Related Topics