Escape the Nested Query Nightmare with CTEs

Learning Exchange
1

Are your SQL queries turning into a nested nightmare? Use Common Table Expressions (CTEs).

A CTE lets you define a temporary, named result set that you can reference within your main query.

If you’ve never used them before, they can seem intimidating, but once you put them into practice, they can make life a lot more simple.

Example:

WITH RegionalSales AS (
    SELECT
        Region,
        SUM(SaleAmount) as TotalSales
    FROM Sales
    GROUP BY Region
)
SELECT
    Region,
    TotalSales
FROM RegionalSales
WHERE TotalSales > 500000;

Readability is crucial for ‘future you’ but also your team.

Want to go the extra mile? Add comments.

Single line comments can be done with two hyphens ‘–’.

Block (multi-line) comments can be started with /* and terminated with */ (‘and terminated with’ would be the comment here and can span multiple lines).

Clean, logical queries are easier to debug, maintain, and hand off to other analysts. It reduces errors and speeds up the delivery of critical reports.

Want to read more on CTEs? Check out the wiki.

2 Likes