PostgreSQL JOIN Explained for Beginners (Examples)

If you searched for "postgresql join explained for beginners," here is the short answer: a JOIN lets you combine rows from two or more tables into one result, by matching a column in one table to a related column in another. This guide walks through it slowly, with two tiny example tables you can copy into your own database, so you can see exactly what each JOIN type does and why.

What is a JOIN in PostgreSQL?

In a real database, you rarely store everything in one giant table. Instead, you split related information into separate tables. For example, a shop might keep a customers table and a separate orders table, because one customer can place many orders. A JOIN is the SQL command that puts those two tables back together for a query, matching rows where a shared value (like a customer ID) lines up.

This guide assumes you already know the basics of SELECT and WHERE. If you don't yet, read our PostgreSQL SELECT guide first, then come back here.

The Sample Tables We'll Use

We'll build two small tables: customers and orders. Every order belongs to one customer, so the orders table stores a customer_id that points back to a row in customers. Run this in psql or any PostgreSQL client to follow along.

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

Line by line: CREATE TABLE customers starts a new table named customers. customer_id SERIAL PRIMARY KEY makes an auto-incrementing whole number that uniquely identifies each customer (1, 2, 3, and so on). name TEXT NOT NULL and email TEXT NOT NULL store text and require a value — NOT NULL means "this field can't be left empty."

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(customer_id),
  product TEXT NOT NULL,
  amount NUMERIC(10,2)
);

The orders table has its own order_id primary key. The important line is customer_id INTEGER REFERENCES customers(customer_id) — this is a foreign key. It says "this column stores a number that must match a customer_id that already exists in the customers table." That link is what makes a JOIN possible. amount NUMERIC(10,2) stores a price with up to 10 digits total and 2 digits after the decimal point, which is the right type for money.

INSERT INTO customers (name, email) VALUES
  ('Maria Lopez', 'maria@example.com'),
  ('Sam Chen', 'sam@example.com'),
  ('Priya Rao', 'priya@example.com');

INSERT INTO orders (customer_id, product, amount) VALUES
  (1, 'Keyboard', 45.00),
  (1, 'Mouse', 20.00),
  (2, 'Monitor', 199.99);

This adds three customers (their customer_id values will be 1, 2, and 3, assigned automatically) and three orders. Notice customer_id 1 (Maria) has two orders, customer_id 2 (Sam) has one order, and customer_id 3 (Priya) has none yet. Keep that in mind — it matters when we compare JOIN types below.

INNER JOIN: Matching Rows Only

INNER JOIN — usually just written JOIN — returns only the rows where the match actually exists in both tables. Any customer with zero orders, like Priya, will not appear at all.

SELECT customers.name, orders.product, orders.amount
FROM customers
INNER JOIN orders
  ON customers.customer_id = orders.customer_id;

SELECT customers.name, orders.product, orders.amount picks the columns we want to see, one from each table. FROM customers starts with the customers table. INNER JOIN orders adds the orders table. ON customers.customer_id = orders.customer_id is the matching rule: for every row, PostgreSQL checks whether a customer's customer_id equals an order's customer_id, and only keeps the pairs that match.

Maria appears twice because she has two orders — a JOIN returns one row per match, not one row per customer. Priya doesn't appear at all, because INNER JOIN drops anything without a match.

LEFT JOIN: Keeping Every Row From the First Table

Sometimes you want every customer to show up, even ones with no orders yet — useful for finding customers who haven't bought anything. LEFT JOIN keeps every row from the "left" table (the one listed first, customers) whether or not it has a match.

SELECT customers.name, orders.product
FROM customers
LEFT JOIN orders
  ON customers.customer_id = orders.customer_id;

This looks almost identical to the INNER JOIN query, just with LEFT JOIN instead of INNER JOIN. The result now includes Priya Rao, with product showing as NULL — PostgreSQL's way of saying "no matching value was found." Maria and Sam's rows look the same as before.

RIGHT JOIN and FULL JOIN, Briefly

RIGHT JOIN is the mirror image of LEFT JOIN: it keeps every row from the second ("right") table, filling in NULL where the first table has no match. FULL JOIN keeps every row from both tables, matched where possible and NULL where not. In practice, most beginners use INNER JOIN and LEFT JOIN far more often than RIGHT JOIN or FULL JOIN — you can usually rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order.

A Shorter Syntax: JOIN ... USING

When the column you're matching on has the exact same name in both tables — here, customer_id in both customers and orders — you can use USING instead of ON, which is a little shorter.

SELECT customers.name, orders.product
FROM customers
JOIN orders USING (customer_id);

JOIN orders USING (customer_id) tells PostgreSQL to match rows where both tables' customer_id column has the same value, without repeating customers.customer_id = orders.customer_id. It only works because we named the column customer_id in both tables — if the names were different (like id and customer_id), you'd need ON instead.

Common Mistakes Beginners Make with JOINs

• Forgetting the ON (or USING) condition — this silently creates a cross join, pairing every row with every other row and producing far too many results.
• Mixing up LEFT and RIGHT. If you're unsure, use LEFT JOIN and put the table you care most about keeping first, after FROM.
• Writing a plain column name, like name, when both tables have a column with that name — PostgreSQL will raise an "ambiguous column" error. Prefix it with the table name, like customers.name.
• Assuming a JOIN removes duplicate rows automatically. It doesn't — a customer with three orders will appear three times unless you group or filter the results.

Practice Exercise

Using the same customers and orders tables from earlier, try writing a query that lists every customer's name along with the total amount they've spent, including customers who've spent nothing. That needs a LEFT JOIN plus SUM() and GROUP BY — both covered in our SQL aggregate functions guide.

SELECT customers.name, COALESCE(SUM(orders.amount), 0) AS total_spent
FROM customers
LEFT JOIN orders USING (customer_id)
GROUP BY customers.name;

COALESCE(SUM(orders.amount), 0) adds up each customer's order amounts, and swaps in 0 if SUM() would otherwise return NULL (which happens for customers with no orders). GROUP BY customers.name collapses each customer's rows into a single summary row.