I used to overuse subqueries in SQL. It worked… but it made my queries harder to read and sometimes slower. Then I started using CTEs (Common Table Expressions). And everything became much cleaner. Instead of this: SELECT * FROM ( SELECT CustomerID, COUNT(*) AS TotalOrders FROM Orders GROUP BY CustomerID ) t WHERE TotalOrders > 5 You can write: WITH OrderSummary AS ( SELECT CustomerID, COUNT(*) AS TotalOrders FROM Orders GROUP BY CustomerID ) SELECT * FROM OrderSummary WHERE TotalOrders > 5 Same result — but much easier to read and maintain. Lesson I learned: Readable queries are easier to debug, optimize, and scale. Do you prefer subqueries or CTEs in your work? #SQL #SQLServer #DataEngineering #DatabaseDeveloper #TechTips
Anshu Sharaf
3w
I completely agree with this. I also prefer using CTEs in most cases because they make queries much more readable and structured. From my experience, CTEs really help when dealing with complex transformations or multi-step logic — it becomes easier to debug, maintain, and even explain to others. That said, I’ve noticed performance can sometimes vary depending on the engine and use case, so I usually balance readability with execution plans when needed. Overall, for clean and scalable SQL, CTEs are definitely my go-to
to me Subquery is much easier than CTE
Great explanation, Hafizur Rahman, I completely agree. CTEs are excellent for making complex queries more readable and easier to manage, especially when breaking logic into clear, step-by-step transformations. Subqueries, on the other hand, are well-suited for simple and concise operations such as filters or existence checks. From a performance perspective, both generally produce similar execution plans. However, CTEs can become less efficient on large datasets, particularly when they are referenced multiple times. While CTEs do not have their own indexes, they still leverage indexes on the underlying base tables. In summary, use CTEs for clarity and structured logic, subqueries for simplicity and quick operations, and consider temporary tables when working with large volumes of data where performance optimization is critical.
Another even simpler option would be to use HAVING directly. CTEs become especially useful when the logic is more complex. Do you see a performance advantage (in your example) with a CTE depending on the SQL engine?
Thanks for the share. This really shows how important readability is especially when the query gets complicated.
Sanat Kamali читай и вникай
CTEs are powerful, but these examples feel like overkill. Using a CTE (or a subquery) where a simple HAVING clause would work adds redundant complexity rather than reducing it. To truly show the value of CTEs, it’s better to use an example that requires them—like filtering on a Window Function, which can't be done in a standard WHERE or HAVING clause.https://sqlize.online/s/bb