What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in SQL?
A JOIN clause is used to combine rows from two or more tables based on a related column between them.
• INNER JOIN returns only records where there is a match in both tables. • LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table, and the matched records from the right table. If no match is found, NULL values are returned for right table columns. • RIGHT JOIN returns all records from the right table, and matched records from the left table (with NULLs for unmatched left records). • FULL OUTER JOIN returns all records when there is a match in either left or right table, filling with NULL wherever a side is missing.
-- Sample Tables: Users (id, name) and Orders (id, user_id, amount)
SELECT
u.id AS user_id,
u.name,
o.id AS order_id,
COALESCE(o.amount, 0) AS total_spent
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id
WHERE u.active = 1
ORDER BY total_spent DESC;Using a LEFT JOIN guarantees that all active users appear in the result set, even if they have not placed any orders yet. For users without orders, `o.id` will be NULL and `COALESCE` gracefully falls back to 0.
Ready for Indexing & Query Optimization?
Take a quick test to evaluate yourself, or move straight to the next module.