Difference Between INNER JOIN and LEFT JOIN in SQL
💡 Concept Name
INNER JOIN vs LEFT JOIN – Two fundamental SQL join types used to combine rows from two tables based on related columns.
📘 Quick Intro
INNER JOIN returns only rows with matching keys in both tables, while LEFT JOIN returns all rows from the left table and matches rows from the right, with NULLs where no match exists.
🧠 Analogy / Short Story
INNER JOIN is like showing only couples in a dance—both must have a partner.
LEFT JOIN is like showing everyone on the left, even if their partner didn’t show up.
🔧 Technical Explanation
INNER JOINoutputs rows where keys match in both tables.LEFT JOINoutputs all rows from the left table, and matching rows from the right table, filling with NULLs if no right-side match.- Used to control how much data from joined tables should be included.
- Important in querying relational data where presence or absence of related data matters.
💻 Real Code Example
-- INNER JOIN: Returns only matching customers with orders
SELECT Customers.Name, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
-- LEFT JOIN: Returns all customers, with orders if any
SELECT Customers.Name, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
❓ Interview Q&A
Q1: What does INNER JOIN return?
A: Rows with matching values in both tables.
Q2: What happens to unmatched rows in LEFT JOIN?
A: They appear with NULLs for right table columns.
Q3: Can LEFT JOIN return fewer rows than INNER JOIN?
A: No, LEFT JOIN returns at least as many rows as INNER JOIN.
Q4: Which join is better for filtering matched data only?
A: INNER JOIN.
Q5: Is it possible to write the same result using different join types?
A: Sometimes, but joins have semantic differences affecting results.
Q6: What is a right join and how does it relate?
A: Right JOIN returns all rows from the right table and matched rows from the left, opposite of LEFT JOIN.
Q7: How does join condition affect INNER JOIN results?
A: Only rows meeting the condition appear in the result.
Q8: Can you join more than two tables?
A: Yes, by chaining multiple JOINs.
Q9: Does LEFT JOIN affect performance compared to INNER JOIN?
A: Sometimes, due to larger result sets and NULL handling.
Q10: How do NULLs affect join comparisons?
A: NULLs never equal each other in join conditions, affecting matched rows.
📝 MCQs
Q1. What does INNER JOIN return?
- All rows from left table
- Rows with matching values in both tables
- All rows from right table
- Rows with no match
Q2. What does LEFT JOIN return?
- Only matching rows
- All rows from right table
- All rows from left table and matched right table rows
- No rows
Q3. What value is shown when LEFT JOIN has no matching right table row?
- 0
- Empty string
- NULL
- Error
Q4. Which join returns fewer rows?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
Q5. Can you chain multiple JOINs?
- No
- Yes
- Only two tables
- Depends on DBMS
Q6. What is a right join?
- Same as INNER JOIN
- Opposite of LEFT JOIN
- Union of tables
- Cross product
Q7. Do NULL values match in joins?
- Yes
- No
- Sometimes
- Depends on collation
Q8. Which join filters to only matched rows?
- LEFT JOIN
- RIGHT JOIN
- INNER JOIN
- FULL JOIN
Q9. Does LEFT JOIN always return as many or more rows than INNER JOIN?
- Yes
- No
- Sometimes
- Only with NULLs
Q10. What happens if join condition is omitted?
- Syntax error
- Cartesian product
- Empty result
- Only left table rows
💡 Bonus Insight
Understanding join types is essential for writing accurate SQL queries. Be cautious with LEFT JOIN as it can produce unexpected NULLs that require careful handling in subsequent logic.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!