Martin Joo
@mmartin_joo
Ever seen a SQL query with 3 nested subqueries and cried? 🧵 Let's clean them with CTEs.
2/ Original query: employees in departments where avg salary > $75k. Subquery-in-subquery chaos:
3/ With Common Table Expression (CTE):
4/ CTEs act as temporary tables that can be used in one query. Break complex logic into named blocks. For example, this CTE:
5/ Returns the following "temp table":
6/ Why use CTEs? ✅ Readable ✅ Reusable in same query ✅ Debuggable (test each CTE separately)
7/ CTEs can reference earlier CTEs. Chain them like functions! `dept_avg_salary` is a CTE from the earlier query. It can be used like a table:
8/ Thanks for reading! I'll release a book soon. It's called Building a database engine. Yes. We're going to build a DB engine. From scratch. Check it out here: https://buff.ly/4kcvgcv