PostgreSQL INNER JOIN

INNER JOIN is the most fundamental join type in PostgreSQL. It returns only the rows where the join condition is satisfied on both sides — any row from either table that has no matching counterpart is silently excluded from the result. The INNER keyword is optional; JOIN alone defaults to INNER JOIN.

Syntax

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

When the join column has the same name in both tables, use the USING shorthand:

SELECT select_list
FROM table1
INNER JOIN table2 USING (shared_column_name);

Chain additional tables with more JOIN clauses:

SELECT select_list
FROM table1
INNER JOIN table2 ON table2.fk = table1.pk
INNER JOIN table3 ON table3.fk = table2.pk;

Practical Example

The following schema tracks events, venues, and ticket sales:

CREATE TABLE venues (
  venue_id   SERIAL PRIMARY KEY,
  venue_name VARCHAR(120) NOT NULL,
  city       VARCHAR(80)  NOT NULL
);

CREATE TABLE events (
  event_id   SERIAL PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  venue_id   INT REFERENCES venues(venue_id),
  event_date DATE NOT NULL
);

CREATE TABLE ticket_sales (
  sale_id    SERIAL PRIMARY KEY,
  event_id   INT REFERENCES events(event_id),
  quantity   INT NOT NULL DEFAULT 1,
  sale_price NUMERIC(8, 2) NOT NULL
);

INSERT INTO venues (venue_name, city) VALUES
  ('Grand Amphitheatre', 'Austin'),
  ('Harbor Stage',       'Seattle'),
  ('Rooftop Arena',      'Denver');

INSERT INTO events (title, venue_id, event_date) VALUES
  ('Jazz Night',        1, '2026-06-10'),
  ('Indie Film Fest',   2, '2026-07-04'),
  ('Electronic Summit', 1, '2026-08-15'),
  ('Workshop: SQL Perf',3, '2026-09-01');

INSERT INTO ticket_sales (event_id, quantity, sale_price) VALUES
  (1, 2, 45.00),
  (1, 1, 45.00),
  (2, 4, 25.00),
  (3, 1, 60.00);
-- event 4 (Workshop) has no ticket sales yet

Join two tables

Retrieve every ticket sale with its event title:

SELECT
  ts.sale_id,
  e.title,
  ts.quantity,
  ts.sale_price
FROM ticket_sales ts
INNER JOIN events e ON e.event_id = ts.event_id
ORDER BY ts.sale_id;

Event 4 (Workshop) is absent because it has no rows in ticket_sales.

Join three tables

Add the venue to each sale:

SELECT
  ts.sale_id,
  e.title        AS event_title,
  v.venue_name,
  v.city,
  ts.quantity,
  ts.sale_price
FROM ticket_sales ts
INNER JOIN events  e ON e.event_id  = ts.event_id
INNER JOIN venues  v ON v.venue_id  = e.venue_id
ORDER BY v.city, e.event_date;

Aggregate across the join

Total revenue per venue:

SELECT
  v.venue_name,
  SUM(ts.quantity * ts.sale_price) AS total_revenue
FROM ticket_sales ts
INNER JOIN events  e ON e.event_id = ts.event_id
INNER JOIN venues  v ON v.venue_id = e.venue_id
GROUP BY v.venue_name
ORDER BY total_revenue DESC;

Inspect the query plan

EXPLAIN ANALYZE
SELECT e.title, v.venue_name
FROM ticket_sales ts
INNER JOIN events  e ON e.event_id = ts.event_id
INNER JOIN venues  v ON v.venue_id = e.venue_id;

Look for Hash Join or Index Scan nodes rather than Seq Scan on large tables.

USING vs ON

Prefer ON when column names differ between tables or when you need to express range conditions. Use USING for clean, symmetric foreign key joins where both sides share the column name.

Testing with Vela

Multi-table joins often change behavior when underlying data volumes grow. Vela’s database branching lets you clone a production database in seconds, add or drop an index on a foreign key column, and run EXPLAIN ANALYZE against real row counts to see whether the planner switches from a nested loop to a hash join — without any risk to production data.

Production Tips

• Index every foreign key column used in JOIN conditions — PostgreSQL does not create these indexes automatically.
• Avoid SELECT * in joins; specify only the columns you need to prevent ambiguity and reduce data transfer.
• Use EXPLAIN ANALYZE to verify join strategies and actual row counts against estimates — large discrepancies indicate stale statistics.
• Run ANALYZE table_name to refresh statistics before benchmarking join performance on tables that have seen heavy inserts or deletes.
• For highly selective filters, apply the WHERE clause on the most restrictive table first — the planner will use it to prune rows before the join, lowering the total work.
• On very large tables, consider materializing intermediate results into a temporary table if the same join appears multiple times in a transaction.