Introduction
An INNER JOIN returns only the rows that have matching values in both tables based on a specified join condition. It is the most commonly used join because it naturally filters out orphaned or unrelated rows, giving you a clean intersection of related data. If a customer has no orders, or an order references a customer that does not exist, those rows are simply excluded from the result.
Cricket analogy: An INNER JOIN between a players table and a centuries table returns only players who actually scored a century, naturally excluding anyone who never crossed three figures, giving a clean list of century-makers.
Syntax
SELECT c.customer_id, c.customer_name, o.order_id, o.order_amount
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;Explanation
The ON clause defines the join condition, typically matching a primary key in one table to a foreign key in the other. The database engine conceptually forms the Cartesian product of both tables and keeps only the row pairs that satisfy the ON predicate. You can also write INNER JOIN simply as JOIN, since INNER is the default join type in ANSI SQL. Column ambiguity between tables (for example, both tables having an id column) is resolved with table aliases like c and o.
Cricket analogy: The ON clause matches a bowler's ID in the bowlers table to the bowler_id foreign key in the wickets table, conceptually pairing every bowler with every wicket record and keeping only true matches, with aliases like b and w resolving duplicate 'name' columns.
Example
-- customers
-- customer_id | customer_name
-- 1 | Alice
-- 2 | Bob
-- 3 | Carla
-- orders
-- order_id | customer_id | order_amount
-- 100 | 1 | 250
-- 101 | 1 | 75
-- 102 | 2 | 400
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;Output
Only customers with at least one matching order appear: Alice/100/250, Alice/101/75, Bob/102/400. Carla, who has no rows in orders, is excluded entirely because there is no matching customer_id on the orders side. No NULLs are introduced by an INNER JOIN; every returned row has real values from both tables.
Cricket analogy: Only bowlers with at least one recorded wicket appear: Bumrah/wicket1/2wkts, Bumrah/wicket2/1wkt, Shami/wicket3/3wkts; a net-bowler with zero official wickets is excluded entirely since there's no matching row on the wickets side.
Key Takeaways
- INNER JOIN returns only rows with matches in both tables; unmatched rows from either side are dropped.
- JOIN and INNER JOIN are equivalent in standard SQL.
- The join condition is evaluated conceptually over the Cartesian product, then filtered.
- No NULL-padding occurs; all columns in the result come from actual matched rows.
- Use table aliases to disambiguate columns with the same name across joined tables.
Practice what you learned
1. What does an INNER JOIN return?
2. If a customer has no orders, what happens in an INNER JOIN between customers and orders?
3. Which keyword is functionally equivalent to INNER JOIN in ANSI SQL?
4. Does INNER JOIN ever produce NULL values for columns that come from the joined tables themselves?
Was this page helpful?
You May Also Like
LEFT and RIGHT JOIN
Preserve all rows from one table, NULL-padding columns from the other table when there is no match.
FULL OUTER JOIN
Combine LEFT and RIGHT JOIN behavior, keeping all rows from both tables and NULL-padding unmatched sides.
Self Join
Join a table to itself using aliases to compare rows within the same table, such as employees to their managers.