Mastering PostgreSQL CTEs (Common Table Expressions) for Complex Queries

Mastering PostgreSQL CTEs (Common Table Expressions) for Complex Queries Common Table Expressions (CTEs) in PostgreSQL are a powerful feature that can help simplify complex queries, improve readability, and enable recursive operations. This article dives deep into CTEs, including recursive ones, to help you write cleaner and more maintainable SQL. What is a CTE? A CTE allows you to define a temporary named result set that you can reference within a larger query. It's declared using the WITH clause and can make deeply nested subqueries more readable and reusable. Basic Example WITH recent_orders AS ( SELECT id, customer_id, created_at FROM orders WHERE created_at > now() - interval '30 days' ) SELECT r.*, c.name FROM recent_orders r JOIN customers c ON r.customer_id = c.id; This example filters recent orders and joins them with customer data using a temporary result set named recent_orders. Recursive CTE Example: Hierarchical Data CTEs can be recursive, making them ideal for traversing hierarchical structures such as category trees or org charts. WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree; This recursive query starts at top-level categories and walks down the tree, joining each level with the one above. Multiple CTEs You can define multiple CTEs in a single query by separating them with commas: WITH top_customers AS ( SELECT customer_id, SUM(total) AS total_spent FROM orders GROUP BY customer_id ORDER BY total_spent DESC LIMIT 10 ), recent_feedback AS ( SELECT * FROM feedback WHERE created_at > now() - interval '7 days' ) SELECT tc.customer_id, f.message FROM top_customers tc LEFT JOIN recent_feedback f ON tc.customer_id = f.customer_id; CTEs vs Subqueries CTEs improve readability and maintainability, especially when reused multiple times. However, they are not always optimized for performance, so for large datasets, consider benchmarking against subqueries or materialized views. Conclusion CTEs are a must-have tool in your SQL arsenal. From simplifying complex logic to handling hierarchical data structures, mastering CTEs allows for elegant and maintainable queries in PostgreSQL. If this post helped you, consider supporting me: buymeacoffee.com/hexshift

WITH recent_orders AS (
  SELECT id, customer_id, created_at
  FROM orders
  WHERE created_at > now() - interval '30 days'
)
SELECT r.*, c.name
FROM recent_orders r
JOIN customers c ON r.customer_id = c.id;

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

WITH top_customers AS (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
  ORDER BY total_spent DESC
  LIMIT 10
),
recent_feedback AS (
  SELECT * FROM feedback WHERE created_at > now() - interval '7 days'
)
SELECT tc.customer_id, f.message
FROM top_customers tc
LEFT JOIN recent_feedback f ON tc.customer_id = f.customer_id;

Related Posts

Comparing Cross-Platform Mobile Development Tools

Feb 17, 2025  0

Build a RAG-Powered Research Paper Assistant

Mar 11, 2025  0

How to "EXPIRE" the "HSET" Child Key in Redis?

Feb 25, 2025  0