SQL INNER JOIN

An INNER JOIN combines rows from two (or more) tables by matching values in a related column, and it only keeps rows where a match is found in both tables. It is the most commonly used join in SQL because most real-world questions — “which customers placed orders?”, “which employees belong to a department?” — are really questions about matching, related data spread across multiple tables. Understanding INNER JOIN deeply is the foundation for every other join type you’ll learn (LEFT, RIGHT, FULL).

Overview: How INNER JOIN Works

Relational databases store data in separate, normalized tables to avoid duplication. A customers table stores each customer once; an orders table stores each order once, with a customer_id column pointing back to the customer who placed it. On their own, these tables answer different questions. To answer a question that spans both — “show me each order along with the customer’s name” — you need to join them.

INNER JOIN works by pairing rows from the left table with rows from the right table wherever the condition in the ON clause evaluates to true. If a row in either table has no matching row in the other table, it is dropped from the result entirely. This is the key distinguishing feature of an inner join versus an outer join (LEFT JOIN, RIGHT JOIN, FULL JOIN), which keep unmatched rows and fill in NULLs instead of discarding them.

Under the hood, the query engine doesn’t necessarily loop through every possible pair of rows (a naive “nested loop”), although it can when tables are small or no index exists. For larger tables, the query planner chooses a join strategy based on statistics and available indexes: a nested loop join (good when one side is small or well-indexed), a hash join (builds an in-memory hash table on the smaller side’s join key, then probes it with the larger side), or a merge join (when both inputs are already sorted on the join key). Whichever strategy is chosen, the logical result is identical — only matching pairs survive. This is why creating an index on the columns used in your ON clause (typically foreign keys) is one of the single best things you can do for join performance: it lets the engine avoid scanning the entire table for every comparison.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
  ON table1.matching_column = table2.matching_column;

Part	Meaning
SELECT columns	Which columns to return; usually qualified with a table name or alias to avoid ambiguity.
FROM table1	The “left” table (or alias) that the join starts from.
INNER JOIN table2	The “right” table to combine with. JOIN alone (without LEFT/RIGHT/FULL) means INNER JOIN by default in every major SQL dialect.
ON table1.col = table2.col	The matching condition. Only row pairs where this is true are kept. Can use any comparison, not just equality, and can combine multiple conditions with AND.

You can chain multiple INNER JOIN clauses to combine three or more tables, and it’s idiomatic to give each table a short alias (c for customers, o for orders) to keep the query readable.

Examples

Example 1: A basic two-table INNER JOIN

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  country TEXT
);
INSERT INTO customers VALUES
  (1, 'Alice Johnson', 'USA'),
  (2, 'Brian Smith', 'UK'),
  (3, 'Chiara Rossi', 'Canada');

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  amount REAL
);
INSERT INTO orders VALUES
  (101, 1, '2026-01-05', 250.00),
  (102, 2, '2026-01-07', 125.50),
  (103, 1, '2026-02-01', 75.25);

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Result:

customer_name	order_id	amount
Alice Johnson	101	250.0
Brian Smith	102	125.5
Alice Johnson	103	75.25

Notice that Chiara Rossi never appears. She has no rows in orders, so there is nothing for her customer_id to match — INNER JOIN silently drops her from the result. This is the defining behavior of an inner join.

Example 2: NULL foreign keys never match

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name TEXT
);
INSERT INTO departments VALUES
  (1, 'Engineering'), (2, 'Sales'), (3, 'Marketing');

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  name TEXT,
  department_id INTEGER,
  salary REAL
);
INSERT INTO employees VALUES
  (1, 'Dana Lee', 1, 95000),
  (2, 'Omar Haddad', 1, 88000),
  (3, 'Priya Nair', 2, 72000),
  (4, 'Sam Ortiz', NULL, 65000);

SELECT e.name, d.department_name, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
ORDER BY e.employee_id;

Result:

name	department_name	salary
Dana Lee	Engineering	95000.0
Omar Haddad	Engineering	88000.0
Priya Nair	Sales	72000.0

Sam Ortiz has a NULL department_id (perhaps he’s newly hired and not yet assigned). In SQL, NULL never equals anything — not even another NULL — so he can never satisfy the ON condition and is excluded. If you needed to see unassigned employees too, you’d use a LEFT JOIN instead.

Example 3: Chaining INNER JOIN across three tables

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT
);
INSERT INTO customers VALUES (1, 'Alice Johnson'), (2, 'Brian Smith'), (3, 'Chiara Rossi');

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER
);
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1);

CREATE TABLE shipments (
  shipment_id INTEGER PRIMARY KEY,
  order_id INTEGER,
  carrier TEXT,
  shipped_date TEXT
);
INSERT INTO shipments VALUES
  (301, 101, 'FedEx', '2026-01-06'),
  (302, 102, 'UPS', '2026-01-08');

SELECT c.customer_name, o.order_id, s.carrier, s.shipped_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN shipments s ON o.order_id = s.order_id
ORDER BY o.order_id;

Result:

customer_name	order_id	carrier	shipped_date
Alice Johnson	101	FedEx	2026-01-06
Brian Smith	102	UPS	2026-01-08

Each additional INNER JOIN narrows the result further. Order 103 has no shipment yet, so it disappears at the second join, and Chiara Rossi disappeared even earlier because she has no orders at all. Chaining inner joins is effectively a chain of “AND this must also match” filters.

How It Works Step by Step (Logical Processing Order)

SQL is declarative, but the engine still evaluates a query in a defined logical order, regardless of how it’s written or physically executed. For a query with a join, the order is:

1. FROM / JOIN — the tables are combined first. For each candidate pair of rows, the ON condition is checked; only matching pairs form the intermediate “joined” row set.
2. WHERE — the joined rows are then filtered further by any WHERE condition.
3. GROUP BY / HAVING — if present, rows are grouped and aggregate filters applied.
4. SELECT — the requested columns/expressions are computed.
5. ORDER BY — finally, the result set is sorted.

This example shows WHERE and ORDER BY layered on top of a join:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT
);
INSERT INTO customers VALUES (1, 'Alice Johnson'), (2, 'Brian Smith'), (3, 'Chiara Rossi');

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount REAL
);
INSERT INTO orders VALUES (101, 1, 250.00), (102, 2, 125.50), (103, 1, 75.25);

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100
ORDER BY o.amount DESC;

Result:

customer_name	order_id	amount
Alice Johnson	101	250.0
Brian Smith	102	125.5

The join happens first (producing all three matched order/customer pairs), then WHERE o.amount > 100 removes order 103 (75.25), and finally ORDER BY o.amount DESC sorts what remains.

Common Mistakes

Mistake 1: Ambiguous column names

When both tables share a column name (like customer_id), referring to it without qualifying which table it comes from causes an error:

-- Wrong: 'customer_id' exists in both tables
SELECT customer_id, order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

This fails with an “ambiguous column name” error, because the database has no way to know whether you mean customers.customer_id or orders.customer_id. Always qualify columns that could exist in more than one joined table:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT);
INSERT INTO customers VALUES (1, 'Alice Johnson'), (2, 'Brian Smith');

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO orders VALUES (101, 1), (102, 2);

SELECT customers.customer_id, order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY order_id;

Mistake 2: Typo in the join column name

A join column that doesn’t actually exist produces a clear “no such column” error rather than silently doing the wrong thing:

-- Wrong: orders has no column named 'cust_id'
SELECT c.customer_name, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.cust_id;

The fix is to use the column’s real name, which you can confirm by checking the table’s schema before writing the join:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT);
INSERT INTO customers VALUES (1, 'Alice Johnson'), (2, 'Brian Smith');

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL);
INSERT INTO orders VALUES (101, 1, 250.00), (102, 2, 125.50);

SELECT c.customer_name, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_id;

A subtler version of this mistake is forgetting the ON clause entirely (or using a comma between tables with no WHERE filter). That doesn’t error — it silently produces a cartesian product, pairing every row of one table with every row of the other. With small sample tables this can look almost right and go unnoticed until it multiplies your row counts in production.

Best Practices

• Always qualify column names with a table name or alias once more than one table is involved, even if a name isn’t currently ambiguous — a later schema change can silently make it so.
• Give tables short, meaningful aliases (c, o, d) to keep multi-join queries readable.
• Index the columns used in your ON conditions (typically foreign keys) — this is usually the single biggest join performance win.
• Remember INNER JOIN and JOIN are the same thing; write INNER JOIN explicitly when you want to signal intent clearly to future readers.
• Never rely on a comma-separated FROM list as a substitute for a real join — always use explicit JOIN ... ON syntax so the matching condition is impossible to miss.
• If rows are unexpectedly missing from your results, check first whether the missing rows have NULL or unmatched foreign key values — that’s the most common reason an inner join drops data.
• Add ORDER BY whenever result order matters to your application; joins do not guarantee any particular row order on their own.

Practice Exercises

• Exercise 1: Using the customers and orders tables from Example 1, write an INNER JOIN query that returns each order’s order_date alongside the customer’s country, ordered by order_date.
• Exercise 2: Using the employees and departments tables from Example 2, write a query that returns only employees in the Engineering department along with their salary, using INNER JOIN plus a WHERE filter. (Hint: the result should have exactly 2 rows — Dana Lee and Omar Haddad.)
• Exercise 3: Extend Example 3’s three-table join to also filter for only shipments handled by FedEx. What single row do you expect back, and why does Brian Smith’s order not appear?

Summary

• INNER JOIN returns only the rows where the join condition matches in both tables — unmatched rows on either side are excluded.
• JOIN alone means INNER JOIN in standard SQL.
• NULL values in a foreign key column never match anything, so those rows are dropped from an inner join’s result.
• You can chain multiple INNER JOIN clauses to combine three or more tables; each join further narrows the result.
• Logical processing order is FROM/JOIN → WHERE → GROUP BY/HAVING → SELECT → ORDER BY, regardless of how the query is physically executed.
• Always qualify ambiguous column names, and index your join columns for performance.