SQL Joins Explained With Simple Examples: INNER, LEFT, RIGHT

Real applications split data into related tables: customers in one, bookings in another, drivers in a third. SQL joins combine rows from these tables using a shared column. Once you understand joins, reports and admin panels become much easier to build.

Example tables

customers

bookings

INNER JOIN: only matching rows

SELECT c.name, b.route, b.fare
FROM customers c
INNER JOIN bookings b ON b.customer_id = c.id;

Result: Aman (2 rows), Simran (1 row). Rohit is missing because he has no bookings.

LEFT JOIN: all rows from the left table

SELECT c.name, b.route
FROM customers c
LEFT JOIN bookings b ON b.customer_id = c.id;

Rohit appears with NULL route. Use this to find customers who never booked:

SELECT c.name
FROM customers c
LEFT JOIN bookings b ON b.customer_id = c.id
WHERE b.id IS NULL;

RIGHT JOIN

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

Joining three tables

SELECT b.id, c.name AS customer, d.name AS driver, b.route
FROM bookings b
JOIN customers c ON c.id = b.customer_id
LEFT JOIN drivers d ON d.id = b.driver_id
ORDER BY b.id DESC;

LEFT JOIN on drivers keeps bookings with no driver assigned yet.

Joins with GROUP BY

Total spent per customer:

SELECT c.name, COUNT(b.id) AS trips, COALESCE(SUM(b.fare), 0) AS total
FROM customers c
LEFT JOIN bookings b ON b.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total DESC;

Common mistakes

• Forgetting the ON condition, which creates every combination of rows (a huge result)
• Filtering a LEFT JOINed table in WHERE (e.g. WHERE d.city = 'Ludhiana') which turns it into an inner join; put that condition in ON instead if you want to keep unmatched rows
• No indexes on join columns like customer_id, which makes joins slow. See database optimisation
• Ambiguous column names; always use table aliases

Start with MySQL basics if tables and queries are new to you.

FAQs

Is JOIN the same as INNER JOIN?

Yes. JOIN alone means INNER JOIN in MySQL.

Does MySQL support FULL OUTER JOIN?

Not directly. You can combine a LEFT JOIN and RIGHT JOIN with UNION.