INNER JOIN returns matching rows only; LEFT JOIN returns all left rows; use multiple JOINs freely.
JOIN Types
-- INNER JOIN — only matching rows
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;
-- LEFT JOIN — all users, NULL if no posts
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;
-- RIGHT JOIN — all posts including orphaned
SELECT p.title, u.name
FROM posts p
RIGHT JOIN users u ON p.user_id = u.id;
-- Self JOIN
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Multiple JOINs
SELECT u.name, p.title, c.content
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id
WHERE p.is_published = 1;