SQL Basics: JOINs Explained

datenbank
sql
English

SQL JOINs Explained

A JOIN combines rows from two tables based on a condition — usually a foreign key.

INNER JOIN

Only rows that match in both tables:

SELECT u.name, o.amount
FROM user u
INNER JOIN order o ON o.user_id = u.id;

LEFT JOIN

All rows from the left table, matching ones from the right — otherwise NULL:

SELECT u.name, o.amount
FROM user u
LEFT JOIN order o ON o.user_id = u.id;

RIGHT and FULL OUTER

RIGHT is the mirror image of LEFT. FULL OUTER returns all rows from both sides — MariaDB/MySQL do not support it directly; combine LEFT and RIGHT with UNION.

Practical tips

Always join on indexed columns (foreign keys).
Use aliases: FROM user u.
1:n relationships create duplicates — work with DISTINCT or GROUP BY.