What is the difference between INNER JOIN and LEFT JOIN?
Why Interviewers Ask This
Interviewers use this question to quickly assess whether a candidate has the foundational knowledge required for MySQL / SQL development. It reveals whether you understand the building blocks that more complex concepts rely on.
Answer
INNER JOIN returns only the rows where there is a match in BOTH tables. Non-matching rows from either table are excluded from the result. Use INNER JOIN when you only want records that have corresponding entries in both tables. Example: customers who have placed at least one order. LEFT JOIN (LEFT OUTER JOIN) returns ALL rows from the left (first) table, plus the matching rows from the right table. If there is no match in the right table, the right table columns are filled with NULL. Use LEFT JOIN when you want all records from the left table regardless of whether they have matches. Example: all customers, showing their orders if they have any (NULLs for customers with no orders). Practical difference: SELECT c.name, o.id FROM customers c INNER JOIN orders o ON c.id = o.customer_id — shows only customers with orders. SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON c.id = o.customer_id — shows all customers; those without orders have NULL for o.id. To find customers with NO orders: LEFT JOIN ... WHERE o.id IS NULL.
Common Mistake
Many candidates answer correctly but can't explain the 'why'. Always be prepared to justify your answer with a concrete example or use case from your MySQL / SQL experience.