INNER JOIN: combining rows from two tables

INNER JOIN combines two tables into one result. It considers pairs of rows, one row from each table, and keeps every pair for which the condition after ON is true; each pair it keeps becomes one output row that has the columns of both tables. Rows that find no partner are left out. That is how a query turns orders, which stores only a customer_id, into a list of orders with their customers’ names: orders JOIN customers ON customers.customer_id = orders.customer_id. The word INNER is optional, so a plain JOIN means the same thing.

You can run every query of this lesson on the page: the Run button uses sql.js 1.14.2, which is SQLite 3.49.1 compiled for the web, and the data is the mart shop of the previous lesson. PostgreSQL 18 and MySQL 8.4 run the same queries; the end of the lesson lists where they differ.

Orders with their customers’ names

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  city        TEXT    NOT NULL
);
CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  status      TEXT    NOT NULL
);
INSERT INTO customers (customer_id, name, city) VALUES
  (1, 'Asha Kulkarni', 'Pune'),
  (2, 'Vikram Nair',   'Kochi'),
  (3, 'Meera Rathore', 'Jaipur'),
  (4, 'Tenzin Dorjee', 'Gangtok');
INSERT INTO orders (order_id, customer_id, status) VALUES
  (101, 1, 'delivered'), (102, 2, 'delivered'), (103, 1, 'placed'), (104, 3, 'cancelled');

-- Each order with the name and city of the customer who placed it.
SELECT o.order_id, c.name, c.city, o.status
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

┌──────────┬───────────────┬────────┬───────────┐
│ order_id │     name      │  city  │  status   │
├──────────┼───────────────┼────────┼───────────┤
│ 101      │ Asha Kulkarni │ Pune   │ delivered │
│ 102      │ Vikram Nair   │ Kochi  │ delivered │
│ 103      │ Asha Kulkarni │ Pune   │ placed    │
│ 104      │ Meera Rathore │ Jaipur │ cancelled │
└──────────┴───────────────┴────────┴───────────┘

Read the result against the two tables:

• Every order found exactly one customer, because customer_id is the primary key of customers. The join therefore returns one row per order: four.
• Asha placed two orders, so her name and city appear twice. A join repeats the row on the “one” side for every match on the “many” side.
• Tenzin placed no order. No pair with his row makes the condition true, so he is not in the result at all. Keeping rows like his takes an outer join, the subject of the next lesson.

The SQLite manual defines every join as starting from all possible pairs of rows, then keeping the pairs that pass ON. That describes the result, not the work: an engine may find the same pairs any faster way it likes, such as looking up each order’s customer through the index of the primary key.

Aliases and qualified column names

FROM orders AS o gives the table a short second name, an alias, and o.order_id then means the order_id column of orders. The word AS is optional. Two rules come with aliases and joins:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL);
INSERT INTO customers (customer_id, name) VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair');
INSERT INTO orders (order_id, customer_id, status) VALUES (101, 1, 'delivered'), (102, 2, 'delivered');

-- customer_id is a column of both tables, so the first query cannot tell which one you mean.
SELECT order_id, customer_id, name
FROM orders
JOIN customers ON customers.customer_id = orders.customer_id;

-- Once a table has an alias, the alias is its only name in the query.
SELECT orders.order_id
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id;

-- Qualified with the aliases, the same query runs.
SELECT o.order_id, o.customer_id, c.name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

┌──────────┬─────────────┬───────────────┐
│ order_id │ customer_id │     name      │
├──────────┼─────────────┼───────────────┤
│ 101      │ 1           │ Asha Kulkarni │
│ 102      │ 2           │ Vikram Nair   │
└──────────┴─────────────┴───────────────┘

Parse error near line 7: ambiguous column name: customer_id
Parse error near line 12: no such column: orders.order_id

• A column name that two tables in the query share must be qualified with its table: customer_id exists in both orders and customers, so SQLite cannot tell which one the first query means. PostgreSQL says the column reference is ambiguous, and MySQL raises error 1052.
• Once a table has an alias, the alias is its only name in that query. The second query names orders.order_id after calling the table o, and fails. PostgreSQL reports an invalid reference to the table, MySQL error 1054, “Unknown column”.

A good habit is to qualify every column in a query that joins tables, even the names only one table has today. If someone later adds a column with the same name to the other table, the query stops with an error instead of quietly reading the wrong column, and a reader can always see where each value comes from.

Joining more tables

Each JOIN adds one table to the rows built so far. Order lines name their product by product_id, so joining products turns the numbers into names:

CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  price_paise INTEGER NOT NULL
);
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
INSERT INTO products (product_id, name, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 14999), (2, 'Toor dal 500 g', 8950), (3, 'Masala chai 250 g', 12000),
  (4, 'Coconut oil 1 L', 21500), (5, 'Ragi flour 1 kg', 6800);
INSERT INTO order_items (order_id, product_id, quantity) VALUES
  (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3);

-- Every order line with its product's name and the line's value in paise.
SELECT i.order_id, p.name AS product, i.quantity,
       i.quantity * p.price_paise AS line_paise
FROM order_items AS i
JOIN products AS p ON p.product_id = i.product_id
ORDER BY i.order_id, product;

┌──────────┬───────────────────┬──────────┬────────────┐
│ order_id │      product      │ quantity │ line_paise │
├──────────┼───────────────────┼──────────┼────────────┤
│ 101      │ Basmati rice 1 kg │ 2        │ 29998      │
│ 101      │ Toor dal 500 g    │ 1        │ 8950       │
│ 102      │ Coconut oil 1 L   │ 1        │ 21500      │
│ 103      │ Masala chai 250 g │ 2        │ 24000      │
│ 103      │ Ragi flour 1 kg   │ 1        │ 6800       │
│ 104      │ Toor dal 500 g    │ 3        │ 26850      │
└──────────┴───────────────────┴──────────┴────────────┘

Six lines in, six rows out, because each line has exactly one product. Toor dal appears twice, once for order 101 and once for order 104. The expression i.quantity * p.price_paise uses a column from each table, which a single table could not provide.

The ON of a later join can use any table joined before it, and WHERE then filters the combined rows:

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL);
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL);
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
INSERT INTO orders (order_id, customer_id, status) VALUES
  (101, 1, 'delivered'), (102, 2, 'delivered'), (103, 1, 'placed'), (104, 3, 'cancelled');
INSERT INTO products (product_id, name, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 14999), (2, 'Toor dal 500 g', 8950), (3, 'Masala chai 250 g', 12000),
  (4, 'Coconut oil 1 L', 21500), (5, 'Ragi flour 1 kg', 6800);
INSERT INTO order_items (order_id, product_id, quantity) VALUES
  (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3);

-- What was delivered: orders, then their lines, then the products on those lines.
SELECT o.order_id, p.name AS product, i.quantity
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
WHERE o.status = 'delivered'
ORDER BY o.order_id, product;

┌──────────┬───────────────────┬──────────┐
│ order_id │      product      │ quantity │
├──────────┼───────────────────┼──────────┤
│ 101      │ Basmati rice 1 kg │ 2        │
│ 101      │ Toor dal 500 g    │ 1        │
│ 102      │ Coconut oil 1 L   │ 1        │
└──────────┴───────────────────┴──────────┘

order_items is the bridge: orders join to their lines on order_id, and lines join to products on product_id. The two delivered orders have three lines between them. For inner joins the order in which you list the tables does not change the result, and the engine may join them in whatever order it finds fastest.

Predicting how many rows a join returns

A join’s row count follows from the values it matches. For each value of the join column, the result holds (rows on the left with that value) × (rows on the right with that value), and the total is the sum over all values. Two cases cover most joins:

• Matching a key. When the column on one side is a primary key or unique, each row on the other side matches at most one row. The result has at most as many rows as the other side, and exactly as many when every value exists in the keyed table, which a NOT NULL foreign key guarantees.
• Matching a value that repeats on both sides. The counts multiply. Pune has two customers and three pickup points, so a join on city gives Pune 2 × 3 = 6 rows:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE pickup_points (point_id INTEGER PRIMARY KEY, city TEXT NOT NULL, address TEXT NOT NULL);
INSERT INTO customers (customer_id, name, city) VALUES
  (1, 'Asha Kulkarni', 'Pune'), (2, 'Vikram Nair', 'Kochi'), (3, 'Meera Rathore', 'Jaipur'),
  (4, 'Tenzin Dorjee', 'Gangtok'), (5, 'Rohan Joshi', 'Pune');
INSERT INTO pickup_points (point_id, city, address) VALUES
  (1, 'Pune', 'Kothrud'), (2, 'Pune', 'Baner'), (3, 'Pune', 'Hadapsar'),
  (4, 'Kochi', 'Edappally'), (5, 'Mumbai', 'Andheri'), (6, 'Mumbai', 'Thane');

-- city is a key of neither table: every customer meets every pickup point of the same city.
SELECT c.city, count(*) AS pairs
FROM customers AS c
JOIN pickup_points AS p ON p.city = c.city
GROUP BY c.city
ORDER BY c.city;

SELECT count(*) AS all_pairs
FROM customers AS c
JOIN pickup_points AS p ON p.city = c.city;

┌───────┬───────┐
│ city  │ pairs │
├───────┼───────┤
│ Kochi │ 1     │
│ Pune  │ 6     │
└───────┴───────┘
┌───────────┐
│ all_pairs │
├───────────┤
│ 7         │
└───────────┘

Here are the joins of this lesson, predicted first and then counted by the queries above:

Venn diagrams of two overlapping circles are often used to explain joins, and they mislead. Circles describe set operations on rows of the same shape, such as UNION and INTERSECT, which the module on set operations covers. They suggest that a join cannot return more rows than its tables hold, while the pickup-point join returns 7 rows from tables of 5 and 6, and a careless join can return far more. Think in pairs and count the matches instead.

When the condition is wrong

A join with the wrong condition rarely fails. It returns rows that look plausible:

-- A new shop numbers its orders from 1, as it numbers its customers.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL);
INSERT INTO customers (customer_id, name) VALUES
  (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO orders (order_id, customer_id, status) VALUES
  (1, 1, 'delivered'), (2, 2, 'delivered'), (3, 1, 'placed'), (4, 3, 'cancelled');

-- Right: each order with the customer it names.
SELECT o.order_id, c.name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

-- Wrong columns: still four rows and no error, but orders 3 and 4 get the wrong customer.
SELECT o.order_id, c.name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.order_id
ORDER BY o.order_id;

-- No condition at all: every order paired with every customer.
SELECT count(*) AS pairs
FROM orders AS o
JOIN customers AS c;

┌──────────┬───────────────┐
│ order_id │     name      │
├──────────┼───────────────┤
│ 1        │ Asha Kulkarni │
│ 2        │ Vikram Nair   │
│ 3        │ Asha Kulkarni │
│ 4        │ Meera Rathore │
└──────────┴───────────────┘
┌──────────┬───────────────┐
│ order_id │     name      │
├──────────┼───────────────┤
│ 1        │ Asha Kulkarni │
│ 2        │ Vikram Nair   │
│ 3        │ Meera Rathore │
│ 4        │ Tenzin Dorjee │
└──────────┴───────────────┘
┌───────┐
│ pairs │
├───────┤
│ 16    │
└───────┘

• The wrong columns. This shop numbers its orders from 1, like its customers, so c.customer_id = o.order_id finds a partner for every order. Order 3 belongs to Asha, yet the second query gives it to Meera, and nothing warns you.
• No condition. Without ON, every order is paired with every customer: 4 × 4 = 16 rows. SQLite and MySQL run such a join; PostgreSQL refuses JOIN without ON or USING and wants CROSS JOIN when every combination is really what you need.

The cheapest test is the one from the previous section: predict the row count, then compare it with count(*) of the query you wrote.

ON, USING and NATURAL JOIN

When the two columns have the same name, USING (customer_id) is a short form of ON orders.customer_id = customers.customer_id. NATURAL JOIN goes further and builds the USING list itself, from every column name the two tables share:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  city        TEXT    NOT NULL      -- where the customer lives
);
CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  city        TEXT    NOT NULL      -- where the order was delivered
);
INSERT INTO customers (customer_id, name, city) VALUES (1, 'Asha Kulkarni', 'Pune'), (2, 'Vikram Nair', 'Kochi');
INSERT INTO orders (order_id, customer_id, city) VALUES (101, 1, 'Pune'), (102, 2, 'Kochi'), (103, 1, 'Mumbai');

-- USING (customer_id): the join column appears once; both city columns stay.
SELECT * FROM orders JOIN customers USING (customer_id) ORDER BY order_id;

-- NATURAL JOIN matches every column name the tables share: customer_id and city.
SELECT * FROM orders NATURAL JOIN customers ORDER BY order_id;

┌──────────┬─────────────┬────────┬───────────────┬───────┐
│ order_id │ customer_id │  city  │     name      │ city  │
├──────────┼─────────────┼────────┼───────────────┼───────┤
│ 101      │ 1           │ Pune   │ Asha Kulkarni │ Pune  │
│ 102      │ 2           │ Kochi  │ Vikram Nair   │ Kochi │
│ 103      │ 1           │ Mumbai │ Asha Kulkarni │ Pune  │
└──────────┴─────────────┴────────┴───────────────┴───────┘
┌──────────┬─────────────┬───────┬───────────────┐
│ order_id │ customer_id │ city  │     name      │
├──────────┼─────────────┼───────┼───────────────┤
│ 101      │ 1           │ Pune  │ Asha Kulkarni │
│ 102      │ 2           │ Kochi │ Vikram Nair   │
└──────────┴─────────────┴───────┴───────────────┘

• With USING, customer_id appears once in SELECT * and can be named without a table, since both columns hold the same value. Only the listed column is merged: both city columns stay, the delivery city and the home city. SQLite keeps the merged column where the left table has it; PostgreSQL and MySQL put the USING columns first.
• NATURAL JOIN matched on customer_id and city, so it kept only the orders delivered to the customer’s home city. Order 103, delivered to Mumbai, vanished without an error.

NATURAL JOIN ties a query to the column names of both tables. If a release adds updated_at to both, every NATURAL JOIN between them starts comparing timestamps, and the PostgreSQL manual calls it “considerably more risky” than USING for that reason. Write ON, or USING when the names match by design.

Older code puts the tables in a list and the condition in WHERE: FROM orders, customers WHERE customers.customer_id = orders.customer_id. For an inner join it returns the same rows. The JOIN … ON form keeps each condition next to its table, and outer joins can only be written that way.

How the engines differ

Interview questions

Fresher · Is JOIN the same as INNER JOIN, and is a comma join the same thing?

Yes for the first: in PostgreSQL, MySQL and SQLite a plain JOIN is an inner join, and the word INNER is optional. Listing the tables with a comma and putting the condition in WHERE returns the same rows as JOIN … ON for an inner join. The differences are elsewhere. Without any condition, a comma or CROSS JOIN pairs every row with every row; SQLite and MySQL also accept JOIN without ON and do the same, while PostgreSQL insists on ON or USING after JOIN. In PostgreSQL and MySQL a comma binds more loosely than JOIN, so mixing the two forms can leave an ON clause unable to see a table. Outer joins exist only in the JOIN form. Prefer JOIN … ON: each condition then sits next to the table it belongs to.

Key takeaways

• JOIN … ON keeps the pairs of rows that satisfy the condition; rows without a partner are left out, and INNER is optional.
• Give tables aliases, qualify every column with them, and use only the alias once a table has one.
• On a key, a join returns at most one row per row of the other side; on a value that repeats on both sides, the counts multiply.
• Predict the row count before you trust a join: a wrong condition returns plausible rows without any error.
• Prefer ON, or USING when the names match; NATURAL JOIN silently changes meaning when a shared column is added.