INNER JOIN | Engineering Interview Kit

Concept

An INNER JOIN returns only the rows that have matching values in both tables. If a row exists in Table A but has no corresponding match in Table B, it is completely discarded from the final result.

This is the most common type of join, and if you simply type JOIN in SQL without specifying a type, the database defaults to an INNER JOIN.

Mental Model

Imagine two tables:

Users: Alice (ID: 1), Bob (ID: 2), Charlie (ID: 3)
Orders: Order A (User 1), Order B (User 1), Order C (User 99)

If you INNER JOIN these tables on the User ID:

Alice has matching orders. She is Included (twice, once for each order).
Bob has no orders. He is Discarded.
Order C belongs to a deleted user (99). It is Discarded.

Result: Only Alice’s two orders are returned.

Syntax & Examples

SELECT users.name, orders.product, orders.amount
FROM users
INNER JOIN orders 
  ON users.id = orders.user_id;

Joining Multiple Tables

You can chain multiple INNER JOINs together to traverse deep relationships.

-- Find which Customer bought which Product
SELECT customers.name, products.product_name
FROM customers
INNER JOIN orders 
  ON customers.id = orders.customer_id
INNER JOIN order_items 
  ON orders.id = order_items.order_id
INNER JOIN products 
  ON order_items.product_id = products.id;

The Implicit Join (Anti-Pattern)

In the 1990s, before the explicit JOIN keyword was standardized in SQL-92, developers wrote joins by listing tables separated by commas in the FROM clause, and doing the matching in the WHERE clause.

Old Style (Do not write this today):

SELECT users.name, orders.amount 
FROM users, orders 
WHERE users.id = orders.user_id;

This performs the exact same INNER JOIN, but it is heavily discouraged today. It mixes the join logic (relationships) with the filtering logic (WHERE), making complex queries unreadable and prone to Cartesian explosion errors if the WHERE clause is accidentally omitted. Always use the explicit INNER JOIN ... ON syntax.

Interview Questions

Q: You INNER JOIN a departments table (5 rows) with an employees table (1,000 rows). How many rows will the final result set contain?
A: It is impossible to know for certain without looking at the data, but it will be somewhere between 0 and 1,000.
If every single employee belongs to exactly one of those 5 departments, the result is 1,000 rows. If 200 employees have department_id = NULL (they are contractors), those 200 will be discarded, resulting in 800 rows. If the departments are brand new and have 0 employees, the result will be 0 rows. (Unlike a LEFT JOIN which would guarantee at least 5 rows).

Q: Can you INNER JOIN a table to itself?
A: Yes, absolutely. This is called a Self Join. It is frequently used for hierarchical data stored in the same table, such as finding an employee’s manager.

SELECT employee.name, manager.name
FROM employees AS employee
INNER JOIN employees AS manager 
  ON employee.manager_id = manager.id;

Because both tables are physically the same, you must use aliases (AS employee, AS manager) to differentiate them in the query, otherwise the database will throw an ambiguity error.