CTE vs subquery vs view decision in SQL

Introduction

We use CTEs, subqueries, and views to organize and reuse parts of our database queries. Choosing the right one helps keep queries clear and efficient.

When you want to break a complex query into smaller, readable parts.

When you need to reuse a query multiple times in one main query.

When you want to save a query for repeated use without rewriting it.

When you want to improve query readability for yourself or others.

When you want to simplify maintenance by separating logic.

Syntax

SQL

-- CTE syntax
WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT * FROM cte_name;

-- Subquery syntax
SELECT column1
FROM (
  SELECT column1, column2
  FROM table_name
  WHERE condition
) AS subquery_alias;

-- View creation syntax
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

CTEs start with WITH and are temporary for one query.

Subqueries are nested SELECT statements inside another query.

Views are saved queries stored in the database for reuse.

Examples

This CTE selects recent orders and then uses them in the main query.

SQL

WITH recent_orders AS (
  SELECT order_id, order_date
  FROM orders
  WHERE order_date > '2024-01-01'
)
SELECT * FROM recent_orders;

This subquery counts orders per customer and filters those with more than 5 orders.

SQL

SELECT customer_id
FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) AS order_summary
WHERE order_count > 5;

This view saves a query for active customers to use anytime without rewriting.

SQL

CREATE VIEW active_customers AS
SELECT customer_id, customer_name
FROM customers
WHERE status = 'active';

Sample Program

This query uses a CTE to find orders with total amounts over 1000 and then selects their IDs.

SQL

WITH high_value_orders AS (
  SELECT order_id, total_amount
  FROM orders
  WHERE total_amount > 1000
)
SELECT order_id
FROM high_value_orders;

Important Notes

CTEs improve readability but exist only during query execution.

Subqueries can sometimes be less readable if nested deeply.

Views store queries permanently and can simplify repeated tasks.

Summary

CTEs are best for breaking down complex queries temporarily.

Subqueries nest queries inside others for filtering or calculation.

Views save queries in the database for easy reuse.