PostgreSQL INNER JOIN | PostgreSQL Tutorial

Introduction to PostgreSQL INNER JOIN

In a relational database, data is typically distributed across multiple related tables. INNER JOIN is the most common way to retrieve data from multiple tables based on a matching condition.

Syntax:

SELECT select_list
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

The INNER keyword is optional — JOIN alone means INNER JOIN.

How INNER JOIN works

For each row in table1, PostgreSQL compares the join column with every row in table2. When the values match, it creates a result row combining columns from both tables. Rows with no match in either table are excluded.

Example: joining two tables

Using the classic DVD rental schema — every payment belongs to one customer, identified by customer_id:

SELECT
  customer.customer_id,
  customer.first_name,
  customer.last_name,
  payment.amount,
  payment.payment_date
FROM customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
ORDER BY payment.payment_date;

This returns only customers who have at least one payment (rows with no payments are excluded).

Using table aliases

Table aliases make multi-table queries more readable:

SELECT c.customer_id, c.first_name, p.amount, p.payment_date
FROM customer c
INNER JOIN payment p ON p.customer_id = c.customer_id
ORDER BY p.payment_date DESC
LIMIT 20;

The USING shorthand

When both tables share the same column name for the join key, use USING:

SELECT c.first_name, p.amount
FROM customer c
INNER JOIN payment p USING (customer_id);

PostgreSQL outputs customer_id only once (not duplicated from both tables).

Joining three tables

SELECT
  c.first_name || ' ' || c.last_name AS customer,
  f.title AS film,
  p.amount
FROM customer c
INNER JOIN payment p ON p.customer_id = c.customer_id
INNER JOIN rental r ON r.rental_id = p.rental_id
INNER JOIN inventory i ON i.inventory_id = r.inventory_id
INNER JOIN film f ON f.film_id = i.film_id
ORDER BY c.last_name, f.title;

Production tips

Index all foreign key columns used in JOIN conditions — unindexed joins on large tables trigger sequential scans.
Avoid SELECT * in joins — column name ambiguity and unnecessary data transfer are common issues.
Use EXPLAIN ANALYZE to verify the join strategy (hash join, nested loop, merge join).
For very selective filters, put the most selective condition in the WHERE clause so the planner can use it to limit rows before the join.

Reference: PostgreSQL documentation — JOIN types.

Frequently Asked Questions

What does INNER JOIN do in PostgreSQL?

INNER JOIN combines rows from two tables where the ON condition is true. If a row in the left table has no matching row in the right table (or vice versa), that row is excluded from the result. It is equivalent to JOIN without a qualifier — the INNER keyword is optional.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only matched rows. LEFT JOIN (LEFT OUTER JOIN) returns all rows from the left table plus matched rows from the right; where no match exists, right-side columns are NULL. Use INNER JOIN when you only want rows that have a counterpart in both tables. Use LEFT JOIN when you need to keep all records from the primary table regardless of whether a related record exists.

What is the USING clause in an INNER JOIN?

The USING clause is a shorthand when the join columns have the same name in both tables. Instead of ON t1.customer_id = t2.customer_id, write USING (customer_id). PostgreSQL outputs the joined column only once (not duplicated). This is equivalent but shorter and avoids column name ambiguity.

How do I join more than two tables in PostgreSQL?

Chain multiple JOIN clauses. Each JOIN adds another table to the query. PostgreSQL joins tables left to right unless the query planner reorders them. Always index the join columns — unindexed foreign keys cause sequential scans that become very slow on large tables.