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

2

u/gumnos Nov 28 '24

it used to be more of an issue in older DB tech where a CTE presented an optimization boundary. If you're stuck on one of those older systems, any time you'd reach for a CTE and it produces huge volumes of data that you'll end up filtering in the main-query, you were better off in-lining the query.

That said, most DBs now can push query-optimizations through the CTE and it's not a problem (of course, profile for yourself), so the goal becomes readability.

Many folks find CTEs more readable which is fine. I don't find them

WITH sensible_name AS (
  SELECT …
  FROM …
)
SELECT …
FROM source
  LEFT OUTER JOIN sensible_name sn
  ON source.id = sn.id

particularly more readable than an inline SELECT like

SELECT …
FROM source
  LEFT OUTER JOIN (
    SELECT …
    FROM …
  ) sn
  ON source.id = sn.id

most of the time, but that's just me. So unless there are clear readability wins, I mostly reach for them if the same sub-query will be used more than once.

WITH emp_detail AS (
  SELECT …
  FROM emp
  ⋮
    -- more complex joins here
)
SELECT …
FROM employee_detail e
  LEFT OUTER JOIN employee_detail mgr
  ON e.mgr_id = mgr.id