Subqueries, CTEs, and views
Break queries into understandable steps and reuse named query interfaces.
Subqueries
A subquery is a query nested inside another SQL statement. It can provide a value or a set of rows to the outer query:
SELECT product_id, name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
Use EXISTS when the question is whether at least one related row exists:
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);
Common table expressions
A common table expression (CTE) names a query result for one statement. The WITH clause can make a multi-step query easier to read:
WITH customer_totals AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
)
SELECT c.name, totals.order_count
FROM customer_totals AS totals
JOIN customers AS c USING (customer_id)
WHERE totals.order_count > 1;
A CTE is not automatically a performance optimization; its planning and materialization behavior depends on the DBMS and query. Use it when the named step makes the logic clearer, then inspect performance if needed.
Views
A view gives a reusable name to a query:
CREATE VIEW order_summary AS
SELECT o.order_id, c.name AS customer_name, o.ordered_at, o.status
FROM orders AS o
JOIN customers AS c USING (customer_id);
Applications can query the view much like a table. A normal view stores the query definition rather than a separate snapshot of its result. Materialized views, where supported, store results and need refresh management.
Exercise: Create a query or view that shows each customer's total number of orders.
Sources: PostgreSQL: WITH Queries, PostgreSQL: Views