SQL Joins Explained: INNER, LEFT, RIGHT, FULL
💡 Concept Name
SQL Joins are used to retrieve data from multiple tables based on a related column between them. The main types are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
📘 Quick Intro
SQL joins allow combining records from two or more tables using a common field. Each type of join dictates which records appear based on matching or non-matching keys in the tables.
🧠 Analogy / Short Story
Think of two circles in a Venn diagram. Each circle is a table, and the overlapping region is where data matches. An INNER JOIN is just the overlap. LEFT JOIN includes everything from the left circle plus the overlap. RIGHT JOIN does the same but for the right circle. FULL JOIN gives you the entire picture – both circles with and without overlap.
🔧 Technical Explanation
- 🔗 INNER JOIN: Returns only the rows with matching values in both tables.
- ⬅️ LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and the 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.
- 🌐 FULL JOIN (FULL OUTER JOIN): Returns rows when there is a match in one of the tables; unmatched rows are filled with NULLs.
🎯 Purpose & Use Case
- ✅ Combine data from related tables (e.g., customers and orders).
- ✅ Handle optional relationships (e.g., not all employees have departments).
- ✅ Generate comprehensive reports by comparing datasets.
- ✅ Manage NULLs and missing information gracefully using LEFT or FULL joins.
💻 Real Code Example
-- INNER JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DeptId = d.Id;
-- LEFT JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DeptId = d.Id;
-- RIGHT JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DeptId = d.Id;
-- FULL JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DeptId = d.Id;
❓ Interview Q&A
Q1: What is the difference between INNER JOIN and LEFT JOIN?
A: INNER JOIN returns only matched rows, while LEFT JOIN returns all rows from the left table and matched rows from the right.
Q2: When would you use FULL JOIN?
A: When you need to see all records from both tables, regardless of whether there’s a match.
Q3: Can JOINs be used on more than two tables?
A: Yes, SQL supports joining multiple tables in one query.
Q4: What happens when there’s no match in a LEFT JOIN?
A: The result shows NULLs for the unmatched columns of the right table.
Q5: Is there a performance impact of using JOINs?
A: Yes, especially with large datasets. Proper indexing is important.
Q6: What is a Cartesian join?
A: It returns all possible combinations of rows from both tables, typically by mistake when no JOIN condition is given.
Q7: What’s the default JOIN type if not specified?
A: By default, JOIN means INNER JOIN.
Q8: What keyword is used for combining tables in JOINs?
A: The keyword JOIN or specific types like INNER JOIN, LEFT JOIN, etc.
Q9: Can JOINs filter data?
A: Yes, using WHERE conditions after the JOIN clause.
Q10: Can we join on multiple columns?
A: Yes, just specify all matching conditions using AND in the ON clause.
📝 MCQs
Q1. Which join returns only matched rows from both tables?
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- INNER JOIN
Q2. Which join includes all rows from the left table?
- INNER JOIN
- RIGHT JOIN
- LEFT JOIN
- FULL JOIN
Q3. Which JOIN includes unmatched rows from both sides?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
Q4. What is returned when there’s no match in RIGHT JOIN?
- Error
- NULLs for left table
- All NULL
- Nothing
Q5. Which join returns all rows when no ON condition is used?
- FULL JOIN
- LEFT JOIN
- RIGHT JOIN
- Cartesian Join
Q6. Which SQL clause specifies the join condition?
- WHERE
- JOIN
- USING
- ON
Q7. Can you JOIN more than two tables?
- No
- Yes
- Only with FULL JOIN
- Only with INNER JOIN
Q8. Which join returns unmatched left rows as NULLs?
- LEFT JOIN
- INNER JOIN
- FULL JOIN
- RIGHT JOIN
Q9. What is the result of INNER JOIN with no matching keys?
- NULLs
- No rows
- All rows
- Error
Q10. Which join provides the most complete set of data?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
💡 Bonus Insight
Always validate JOIN logic with test data to avoid silent NULLs. Use EXPLAIN or execution plans to evaluate performance. Aliasing table names makes complex JOINs more readable and maintainable.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!