SQL JOINs explained with simple examples

A JOIN combines rows from two tables based on a related column. Our example has two tables.

authors

id	name
1	Rima
2	Karim
3	Sumon

articles

id	title	author_id
10	Intro to SQL	1
11	Caching	1
12	Indexes	2
13	Draft (no author)	NULL

INNER JOIN

Only rows that match in both tables.

SELECT a.name, ar.title
FROM authors a
INNER JOIN articles ar ON ar.author_id = a.id;

Result: Rima with two articles, Karim with one. Sumon (no articles) and the draft (no author) are missing.

LEFT JOIN

All rows from the left table, plus matching rows from the right. Where there is no match, right columns are NULL.

SELECT a.name, ar.title
FROM authors a
LEFT JOIN articles ar ON ar.author_id = a.id;

Now Sumon appears, with title = NULL. Use it for "all authors, even those with no articles".

Find rows with no match:

SELECT a.name
FROM authors a
LEFT JOIN articles ar ON ar.author_id = a.id
WHERE ar.id IS NULL;   -- authors with no articles

RIGHT JOIN

The mirror of LEFT JOIN: all rows from the right table. Most people simply swap the table order and use LEFT JOIN.

FULL OUTER JOIN

All rows from both tables, matched where possible. Shows Sumon and the draft. Not available in every database (older MySQL lacks it).

CROSS JOIN

Every row of one table with every row of the other (a Cartesian product). 3 authors x 4 articles = 12 rows. Rare, useful for generating combinations. A missing ON condition can accidentally create one.

Common mistakes

1. Duplicate rows. Joining a "one" table to a "many" table repeats the "one" side. Rima appears twice. If you then SUM a column from authors, you double count. Aggregate carefully or use a subquery.
2. Filtering the right table in WHERE. LEFT JOIN ... WHERE ar.title = 'Caching' removes the NULL rows, so it silently behaves like an INNER JOIN. Put that condition in the ON clause instead: ON ar.author_id = a.id AND ar.title = 'Caching'.
3. Joining on the wrong column or a non-unique column, causing row explosions.
4. Forgetting NULLs: NULL = NULL is not true, so rows with NULL keys never match.

Performance

Joins are fast when the join columns are indexed (see the index part). Always index foreign keys such as articles.author_id.

Takeaway: INNER for matches only, LEFT to keep everything on the left, and watch for duplicates and WHERE filters on the right table.

---

Backend & Database series, Part 1 (SQL and transactions): 1 of 4.