Introduction

When working with PostgreSQL in real backend projects, you will rarely query just a single table. Data is usually normalized across multiple tables linked together by foreign keys, and you need to combine them to return meaningful information. This lesson focuses on three important techniques that help you write complex queries in a clear and efficient way: JOIN to connect tables, subquery for nested queries, and CTE to structure query logic.

We will use a sample schema simulating a sales system consisting of four main tables: customers, orders, products, and order_items. The customers table stores customer information, orders records customer purchases, products contains the product catalog, and order_items is an intermediate table storing details of each product in every order. This is a nested one-to-many relationship you will encounter frequently.

JOINs across multiple tables

JOIN is the most common way to combine data from multiple tables in a single query. PostgreSQL supports INNER JOIN (returns only matching records in both tables), LEFT JOIN (returns all records from the left side even when there is no match on the right), and other variants like RIGHT JOIN, FULL OUTER JOIN. In real backend work, you will use INNER JOIN and LEFT JOIN the most.

The example below retrieves the list of orders along with the customer name and total amount:

SELECT o.id AS order_id, c.name AS customer_name, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 10;

When you need to join multiple tables at once, simply chain additional JOIN clauses. For example, to retrieve details of each product in an order including the product name and quantity:

SELECT o.id AS order_id, p.name AS product_name, oi.quantity, oi.unit_price
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 42;

A common mistake is joining tables without an ON condition, resulting in a Cartesian product and very slow query performance. Always make sure every JOIN has a clear key-matching condition.

Subqueries and correlated subqueries

A subquery is a SELECT query nested inside another query. You can use a subquery in the WHERE clause to filter, in SELECT to compute values, or in FROM to create a temporary table. A special form is the correlated subquery, where the inner query references a column from the outer query, meaning it is re-executed for each result row.

Example: finding customers who have placed at least one order using a simple subquery:

SELECT name, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total_amount > 500);

Example correlated subquery that counts the number of orders for each customer displayed in the list:

SELECT c.name, c.email,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c
WHERE c.created_at > '2024-01-01';

Since the inner query references c.id from the outer query, PostgreSQL must re-run it for each customer. With large tables, correlated subqueries can be slow, so consider switching to JOIN or CTE if performance is a concern.

Common Table Expressions with WITH

CTE (Common Table Expression) lets you define named temporary queries within the same statement, making complex logic easier to read. A CTE starts with the WITH keyword, followed by a list of named queries, and then the main query that uses them.

Example: calculating total revenue per customer in 2024, then filtering to those with revenue above 1000:

WITH customer_revenue AS (
  SELECT o.customer_id, SUM(o.total_amount) AS revenue
  FROM orders o
  WHERE o.created_at >= '2024-01-01' AND o.created_at < '2025-01-01'
  GROUP BY o.customer_id
)
SELECT c.name, cr.revenue
FROM customer_revenue cr
INNER JOIN customers c ON c.id = cr.customer_id
WHERE cr.revenue > 1000
ORDER BY cr.revenue DESC;

CTEs also support a recursive form (recursive CTE) using the WITH RECURSIVE keyword, which is very useful for traversing hierarchical trees such as a company's organizational structure or multi-level product categories. Additionally, PostgreSQL 12 and later allow materializing with WITH ... AS MATERIALIZED to speed up queries in certain cases.

Common mistakes and practical tips

A common mistake is confusing JOIN with a subquery in the WHERE clause using IN or EXISTS. Both give equivalent results in many situations, but EXISTS is usually faster when you only need to check existence rather than retrieve values. Example: finding customers who have placed an order:

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

When writing CTEs, use meaningful names like customer_revenue or top_products so readers can easily understand the purpose of each step. Avoid overusing CTEs when a simple JOIN is sufficient, because CTEs can add extra optimization overhead in some PostgreSQL versions. Finally, always check the execution plan using EXPLAIN ANALYZE when a complex query runs slowly, because sometimes a correlated subquery can be replaced with a JOIN for a significant speedup.

Summary

In this lesson, you learned how to combine multiple tables using JOIN, write subqueries and correlated subqueries to filter and calculate, and use CTEs with WITH to structure complex queries into readable blocks. These three techniques form the foundation for handling most query requirements in real-world backend applications, from retrieving order lists with details to computing aggregate reports. In the next lesson, you will learn more about indexing and how to optimize the performance of the complex queries you just wrote.