SQL INNER JOIN: Match Rows from Two Tables (Examples)

SQL Tutorial · Chapter 20 of 32

The SQL INNER JOIN returns only the rows that have a matching value in both tables. If a customer has no orders, or an order points to a customer that does not exist, that row is left out. INNER JOIN is the default and most common join: writing just JOIN means INNER JOIN in every major database.

SQL INNER JOIN Syntax

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

The ON condition usually compares a primary key in one table with a foreign key in the other. Use table aliases (c, o) to keep the query short.

Sample Tables

Example 1: Basic INNER JOIN

SELECT c.Name, o.OrderID, o.Product, o.Amount
FROM Customers c
INNER JOIN Orders o ON o.CustomerID = c.CustomerID
ORDER BY o.OrderID;

Five rows. Emma Brown (no orders) and order 105 (customer 6 does not exist) are excluded. Anita Sharma appears twice because she has two matching orders.

Example 2: INNER JOIN with WHERE

SELECT c.Name, c.Country, o.Product
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE o.Amount > 100;

Example 3: INNER JOIN with GROUP BY

Joining then grouping gives you totals per customer with their names, something neither table can produce alone.

SELECT c.Name, COUNT(o.OrderID) AS Orders, SUM(o.Amount) AS Total
FROM Customers c
INNER JOIN Orders o ON o.CustomerID = c.CustomerID
GROUP BY c.Name
ORDER BY Total DESC;

Example 4: Extra Conditions in ON

ON may contain more than one condition. Here only January orders are joined.

SELECT c.Name, o.OrderID, o.OrderDate
FROM Customers c
INNER JOIN Orders o
    ON o.CustomerID = c.CustomerID
   AND o.OrderDate < '2026-02-01';

For an INNER JOIN this is equivalent to putting the date test in WHERE; for outer joins the two placements behave differently.

Example 5: Self Join

A table can be joined to itself to compare rows. This finds pairs of customers in the same country; with our data none share a country, so the result is empty, but the pattern is common for employee and manager tables.

SELECT a.Name AS Customer1, b.Name AS Customer2, a.Country
FROM Customers a
INNER JOIN Customers b
    ON a.Country = b.Country AND a.CustomerID < b.CustomerID;

Works in MySQL, SQL Server, PostgreSQL, SQLite

• INNER JOIN syntax is identical in all four. JOIN, INNER JOIN and the comma-style FROM a, b WHERE a.id = b.id give the same result, but explicit INNER JOIN is clearest.
• MySQL, PostgreSQL and SQLite support USING (CustomerID) as shorthand when the key column has the same name in both tables; SQL Server does not.
• MySQL and SQLite also support NATURAL JOIN; avoid it, because it silently joins on every shared column name.

Common Mistakes

• Omitting ON, which gives every combination of rows (a cross join).
• Selecting CustomerID without a prefix when both tables contain it: “ambiguous column name”.
• Expecting unmatched rows to appear; that needs LEFT, RIGHT or FULL JOIN.
• Duplicated rows after joining to a table that has several matches per key; aggregate or use DISTINCT deliberately.

Practice Exercise

1. List each order’s OrderID with the customer’s City.
2. Show the total Amount ordered by customers from India.

-- 1
SELECT o.OrderID, c.City
FROM Orders o INNER JOIN Customers c ON c.CustomerID = o.CustomerID;
-- 101 Mumbai, 102 London, 103 Mumbai, 104 Madrid, 106 Singapore

-- 2
SELECT SUM(o.Amount) AS IndiaTotal
FROM Orders o INNER JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE c.Country = 'India';
-- 1500

Related Chapters

• SQL Joins Introduction – all four join types side by side
• SQL LEFT JOIN – keep customers with no orders
• SQL Aliases – the c and o shorthand
• SQL from Scratch: full course overview

FAQ

What is the difference between JOIN and INNER JOIN?

None. JOIN on its own is shorthand for INNER JOIN in MySQL, SQL Server, PostgreSQL, SQLite and Oracle.

Does INNER JOIN return duplicate rows?

It returns one row for every matching pair. If a customer has three orders, that customer appears three times. That is correct behaviour, not duplication.

What happens to rows without a match in an INNER JOIN?

They are excluded from the result. To keep them, use LEFT JOIN, RIGHT JOIN or FULL JOIN.

Chapter 20 of 32 · SQL from Scratch: all 32 chapters