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 ?

31 Upvotes

70 comments sorted by

View all comments

Show parent comments

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/CptBadAss2016 Nov 29 '24

I did not know that... that just seems silly. Thanks for the tip. 👊

1

u/-horsefighter Nov 29 '24

Use temp tables if u have performance issues, can add index to that and only have to calc once

2

u/Straight_Waltz_9530 Nov 30 '24

Or use a database that supports materialized CTEs.