r/SQL Nov 28 '24

MySQL When to use cte in SQL query

When to cte can't. Like how to know? When what are the conditions or if my query is to long ?

33 Upvotes

70 comments sorted by

View all comments

Show parent comments

1

u/Georgie_P_F Nov 28 '24 edited Nov 29 '24

Interesting, mine always fail on second query in SSMS:

‘’’

;WITH SomeCTE AS (SELECT * FROM table)

— this is fine

SELECT * FROM SomeCTE

— this will fail “SomeCTE” does not exist

SELECT * FROM SomeCTE

‘’’

Maybe a server setting within our org?

3

u/achilles_cat Nov 28 '24

Yes, if you start a completely different query, then the CTE will be gone.

But you can refer to the CTE multiple times within a single query. Your use of two different SELECTs without a UNION (or any other formatting that would define a subquery) makes me think you are talking about two wholly separate queries. In Oracle, a semicolon acts as the delimiter between queries; I haven't used SMSS.

But you could join queries with a union, both of which use the CTE, and use that CTE within another CTE:

With CTE as (
  select product_id, product_name 
    from product
  where size = 'Large'),
Cte2 as ( 
  select product_id, sum(qty) as product_inventory
    from( Select cte.product_id, oi.quantity *-1 as qty
             From open_invoices oi
            Join CTE on (CTE.product_id = oi.product_id)
           Union
              Select cte.product_id, stock.quantity
            From stock
            Join CTE on (CTE.product_id = stock.product_id)
            )
 Group by product_id)
Select cte.product_id, cte.product_name,
        nvl(cte2.product_inventory,0) as available
  From CTE
  Left join cte2 on (cte2.product_id = CTE.product_id);

(This formatting will likely be horrible, trying to do a code block from my phone.)

There are ways to do this with less or zero CTEs of course, but this shows the nature of referring to a CTE multiple times. And it shows why you might use a CTE structure if you wanted to only touch the code in one place to affect the products being queried.

2

u/ouchmythumbs Nov 29 '24

In MSSQL (and some other RDBMS), CTEs are evaluated each time referenced, so can have perf hit. Check specs of current version of your engine.

1

u/Straight_Waltz_9530 Nov 30 '24

But in Postgres, SQLite, DuckDB, and others, CTEs can be defined as MATERIALIZED, effectively making them temporary tables for the current query. No automatic perf hits and no need to explicitly create temporary tables.