What are the Different Types of Joins in SQL?
💡 Concept Name
SQL Joins – Operations to combine rows from two or more tables based on related columns.
📘 Quick Intro
Joins let you fetch related data by linking tables using common columns. Different join types control how rows are matched and which rows appear in the result.
🧠 Analogy / Short Story
Imagine you have two lists of people: one with employees, one with their projects. INNER JOIN shows only employees working on projects, LEFT JOIN shows all employees even if no project, and FULL JOIN shows everyone from both lists. CROSS JOIN pairs everyone with everyone, like a party where everyone meets everyone else.
🔧 Technical Explanation
- INNER JOIN: Returns rows with matching values in both tables.
- LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table; unmatched right rows are NULL.
- RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table; unmatched left rows are NULL.
- FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in either left or right table; unmatched rows get NULLs on the opposite side.
- CROSS JOIN: Returns Cartesian product; pairs every row from the left with every row from the right.
💻 Real Code Examples
-- INNER JOIN example
SELECT A.Name, B.OrderID
FROM Customers A
INNER JOIN Orders B ON A.CustomerID = B.CustomerID;
-- LEFT JOIN example
SELECT A.Name, B.OrderID
FROM Customers A
LEFT JOIN Orders B ON A.CustomerID = B.CustomerID;
-- RIGHT JOIN example
SELECT A.Name, B.OrderID
FROM Customers A
RIGHT JOIN Orders B ON A.CustomerID = B.CustomerID;
-- FULL JOIN example
SELECT A.Name, B.OrderID
FROM Customers A
FULL JOIN Orders B ON A.CustomerID = B.CustomerID;
-- CROSS JOIN example
SELECT A.Name, B.Product
FROM Customers A
CROSS JOIN Products B;
❓ Interview Q&A
Q1: What does INNER JOIN do?
A: Returns only rows with matching values in both tables.
Q2: How is LEFT JOIN different from INNER JOIN?
A: LEFT JOIN returns all left table rows plus matching right rows; unmatched right rows show NULL.
Q3: What is the purpose of FULL JOIN?
A: To return all rows from both tables, filling NULLs when no match exists.
Q4: When would you use CROSS JOIN?
A: When you want all combinations of rows from both tables.
Q5: Can RIGHT JOIN be replaced by LEFT JOIN?
A: Yes, by switching table order.
Q6: Does CROSS JOIN require ON condition?
A: No, it returns Cartesian product without join condition.
Q7: Are OUTER JOINs always slower than INNER JOIN?
A: Usually, because of handling unmatched rows and NULLs.
Q8: Can you join more than two tables?
A: Yes, by chaining multiple JOINs.
Q9: What is the difference between INNER JOIN and CROSS JOIN?
A: INNER JOIN filters matched rows; CROSS JOIN pairs every row from left with every row from right.
Q10: How do NULLs affect JOIN results?
A: NULLs do not match in join conditions, affecting matched rows.
📝 MCQs
Q1. Which join returns only matching rows?
- LEFT JOIN
- RIGHT JOIN
- INNER JOIN
- FULL JOIN
Q2. Which join returns all rows from the left table?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- CROSS JOIN
Q3. Which join returns the Cartesian product?
- FULL JOIN
- CROSS JOIN
- INNER JOIN
- LEFT JOIN
Q4. FULL JOIN returns?
- Only matching rows
- All rows from left
- All rows from right
- All rows from both tables with NULLs for no matches
Q5. RIGHT JOIN returns all rows from?
- Left table
- Right table
- Both tables
- No table
Q6. Do CROSS JOINs require ON conditions?
- Yes
- No
- Sometimes
- Depends on DBMS
Q7. Can you chain multiple JOINs?
- No
- Yes
- Only two tables
- Depends on DBMS
Q8. What does LEFT JOIN do with unmatched right rows?
- Ignores them
- Shows NULLs
- Throws error
- Deletes them
Q9. Is RIGHT JOIN just LEFT JOIN with tables swapped?
- No
- Yes
- Sometimes
- Depends on DBMS
Q10. Which join filters rows to only matched rows?
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- INNER JOIN
💡 Bonus Insight
Understanding the differences among join types helps you write efficient queries tailored to your data needs. Remember, LEFT and RIGHT joins are symmetrical—switch tables and join type to get the same result.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!