SQL INNER JOIN: combine rows from two tables

In SQL with INNER JOIN, you get only the rows where the join condition matches in both tables. Use it to combine related data — such as linking records to their lookup values — when unmatched rows should be excluded from the result. Because both sides must satisfy the join predicate, any row with a NULL join key or no corresponding match is automatically filtered out.

SQL Editor

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER);
INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'HR');
INSERT INTO employees VALUES (1,'Alma',1),(2,'Ben',1),(3,'Cara',2),(4,'Dan',NULL);

-- Return employees with their department name
SELECT e.name AS employee, d.name AS department
FROM employees e
INNER JOIN departments d ON d.id = e.dept_id
ORDER BY e.id;

Output:

Output will appear here...

Output:

+----------+-------------+
| employee | department  |
+----------+-------------+
| Alma     | Engineering |
| Ben      | Engineering |
| Cara     | Sales       |
+----------+-------------+
3 row(s)

How This Example Works

departments holds three departments; employees holds four people, each with a dept_id foreign key. Dan’s dept_id is NULL.
INNER JOIN departments d ON d.id = e.dept_id pairs each employee row with the department row whose id equals the employee’s dept_id. Only pairs where both sides match are kept.
Dan has dept_id = NULL. The = operator returns unknown when either operand is NULL, so Dan never matches any department and is excluded.
The HR department (id 3) has no employees pointing to it, so it also does not appear — INNER JOIN drops unmatched rows from both sides.

What Is SQL INNER JOIN?

INNER JOIN produces the intersection of two tables based on a join predicate. Logically, it keeps only pairs of rows where the ON condition evaluates to true. Physical execution varies by engine and data shape (nested loops, hash join, merge join), but the result set always contains only matching rows.

INNER JOIN vs LEFT JOIN

INNER JOIN	LEFT JOIN
Returns only matched rows from both tables	Returns all rows from the left table, with NULLs where no match exists
Unmatched rows are silently dropped	Unmatched left rows are preserved with NULL-filled right columns
Use when missing matches mean invalid data	Use when you need to see all left-side records regardless of matches

Rule: choose INNER JOIN when every result row must have valid data from both tables. Switch to LEFT JOIN when you need to retain rows even without a match on the right.

Common Mistakes With SQL INNER JOIN

Missing or always-true join predicate

Omitting the ON clause (or using a condition that is always true) produces a cartesian product: every employee pairs with every department.

-- Wrong: cartesian product (12 rows from 4 × 3)
SELECT * FROM employees, departments;

-- Right: explicit join predicate
SELECT * FROM employees e
INNER JOIN departments d ON d.id = e.dept_id;

Non-unique join keys causing row multiplication

INNER JOIN returns one output row per matching pair. If you join to a table where the key is not unique, one row on the left can match multiple rows on the right and multiply your results.

INNER JOIN vs EXISTS

Use INNER JOIN when you need columns from both tables in the output. Use EXISTS when you only need to filter rows based on whether a related row is present. EXISTS also avoids row multiplication if the subquery can match multiple rows, and the query planner can often stop after the first match.

-- EXISTS: one row per employee that has a matching department
SELECT e.name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

Performance Notes

Index the columns used in the join predicate (dept_id and departments.id in this case). Without an index, the engine may scan the inner table many times. For large tables, verify the plan with EXPLAIN QUERY PLAN — look for “USING INDEX” or “SEARCH” instead of “SCAN”.