PostgreSQL Joins

PostgreSQL Join Types Overview

A join combines rows from two or more tables based on a related column. PostgreSQL supports inner join, left join, right join, full outer join, cross join, natural join, and self-join.

The join type determines how non-matching rows are handled:

• INNER JOIN — only matching rows from both tables
• LEFT JOIN — all rows from the left table; NULLs where no right-table match
• RIGHT JOIN — all rows from the right table; NULLs where no left-table match
• FULL OUTER JOIN — all rows from both tables; NULLs on either side where no match
• CROSS JOIN — every combination of rows (Cartesian product)

Sample tables for examples

The following examples use two fruit-basket tables to visualize overlapping and non-overlapping rows:

CREATE TABLE basket_a (id INT PRIMARY KEY, fruit VARCHAR(100));
CREATE TABLE basket_b (id INT PRIMARY KEY, fruit VARCHAR(100));

INSERT INTO basket_a VALUES (1,'Apple'),(2,'Orange'),(3,'Banana'),(4,'Cucumber');
INSERT INTO basket_b VALUES (1,'Orange'),(2,'Apple'),(3,'Watermelon'),(4,'Pear');

INNER JOIN

INNER JOIN returns rows where the join condition matches in both tables. Non-matching rows are excluded.

SELECT a.fruit AS fruit_a, b.fruit AS fruit_b
FROM basket_a a
INNER JOIN basket_b b ON a.fruit = b.fruit;

Result: only fruits that exist in both baskets (Apple, Orange).

LEFT JOIN

LEFT JOIN returns all rows from the left table. For rows with no match in the right table, right-side columns are NULL.

SELECT a.fruit AS fruit_a, b.fruit AS fruit_b
FROM basket_a a
LEFT JOIN basket_b b ON a.fruit = b.fruit;

Result: all 4 rows from basket_a; Banana and Cucumber have NULL in fruit_b.

To find rows that exist only in the left table (no match on the right):

SELECT a.fruit
FROM basket_a a
LEFT JOIN basket_b b ON a.fruit = b.fruit
WHERE b.fruit IS NULL;

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN — all rows from the right table are included; left-side columns are NULL for non-matching rows.

SELECT a.fruit AS fruit_a, b.fruit AS fruit_b
FROM basket_a a
RIGHT JOIN basket_b b ON a.fruit = b.fruit;

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables. Non-matching rows have NULLs on the non-matching side.

SELECT a.fruit AS fruit_a, b.fruit AS fruit_b
FROM basket_a a
FULL OUTER JOIN basket_b b ON a.fruit = b.fruit;

To find rows unique to each table (not in both):

SELECT a.fruit AS fruit_a, b.fruit AS fruit_b
FROM basket_a a
FULL OUTER JOIN basket_b b ON a.fruit = b.fruit
WHERE a.fruit IS NULL OR b.fruit IS NULL;

Production tips

• Index the join columns — unindexed foreign keys cause sequential scans on large tables.
• Use EXPLAIN ANALYZE to check join strategy (hash join, nested loop, merge join). The planner chooses based on table sizes and statistics.
• Avoid SELECT * in joins — duplicated column names cause confusion and transfer unnecessary data.
• LEFT JOIN with a WHERE b.col IS NULL filter (anti-join) is a common pattern for finding orphaned rows — but a NOT EXISTS subquery often has the same or better performance.

Reference: PostgreSQL documentation — Table Expressions.