SQL Joins Explained with Visuals

Team24 September 2024 Blog, Databases, Tutorials

INNER, LEFT, RIGHT, FULL, and CROSS joins — where each is useful, example queries, and performance notes with indexes.

Sample Tables

customers(id, name)
orders(id, customer_id, amount)

INNER JOIN

SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

LEFT JOIN

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

RIGHT / FULL JOIN

-- RIGHT shows all rows from RIGHT table
SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

-- FULL OUTER JOIN shows unmatched from both
SELECT c.name, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

Performance Notes

Index the join keys (e.g., orders.customer_id).
Filter early with WHERE to reduce rows before joining.
Prefer INNER join when appropriate for better optimizer plans.

Back to Tutorials