SQL
Enhancing SQL Readability with Common Table Expressions (CTEs)
Discover how SQL CTEs (WITH clause) improve query readability and modularity by breaking down complex queries into logical, named sub-queries for better organization.
WITH
HighValueCustomers AS (
SELECT
customer_id,
SUM(order_total) as total_spent
FROM
orders
GROUP BY
customer_id
HAVING
SUM(order_total) > 1000
),
LoyalCustomers AS (
SELECT
customer_id
FROM
orders
GROUP BY
customer_id
HAVING
COUNT(order_id) >= 5
)
SELECT
c.customer_name,
hvc.total_spent
FROM
customers c
JOIN
HighValueCustomers hvc ON c.customer_id = hvc.customer_id
JOIN
LoyalCustomers lc ON c.customer_id = lc.customer_id
ORDER BY
hvc.total_spent DESC;
How it works: Common Table Expressions (CTEs), defined using the WITH clause, allow you to create temporary, named result sets that you can reference within a single SQL statement. This snippet shows how two CTEs, `HighValueCustomers` and `LoyalCustomers`, are defined to first identify customers who spent over $1000 and those with 5 or more orders. These CTEs are then joined with the `customers` table, greatly improving the readability and organization of what would otherwise be a complex subquery or nested query.