CTEs > Subqueries

Selected quotes from thought leaders on why CTEs are better than subqueries:

#1

"CTEs > Subqueries.

I spent a couple of hours trying to understand and improve the performance of a query with 5 subqueries and nested subqueries.

- moved the subqueries to CTEs
- renamed the CTEs to something meaningful (instead of single-letter aliases).

Performance tuning became 10x easier because you can logically understand what's happening.

Query performance went from 2:48 to 0:07 seconds with a couple of tweaks.

Clarity always wins."

https://www.linkedin.com/posts/sawyernyquist_ctes-subqueries-i-spent-a-couple-of-hours-activity-7118947120249741312-4BMG

——

#2

"CTEs are particularly useful when you need to reuse the same result set multiple times within the query or when you want to simplify complex queries by dividing them into logical parts."

https://g-dhasade16.medium.com/sql-subquery-vs-ctes-b312a64614f

——

// We'll continue adding more quotes to list!

Our thoughts?

Absolutely, yes! Because you can visualize any CTEs into lineage diagram using Datascale! 😄

Try this query at https://datascale.cloud/

WITH employee_sales AS (
    SELECT 
        employee_id, 
        SUM(sales_amount) AS total_sales
    FROM 
        sales
    GROUP BY 
        employee_id
),
total_sales AS (
    SELECT 
        employee_id, 
        total_sales
    FROM 
        employee_sales
    WHERE 
        total_sales > 10000
)
SELECT * FROM