SQL Joins Deep Dive

JOINS are the backbone of relational databases. They allow you to reconstruct normalized data into meaningful results. However, many developers struggle with Physical Join Types (Merge, Hash, Nested Loop) and how they impact scalability.

1. Inner vs Outer Joins

• INNER JOIN: Returns only matching rows from both tables. (The "Must-Have" result).
• LEFT JOIN: Returns all rows from the left table + matching rows from the right. Unmatched rows get NULLs. (The "Optional-Data" join).
• CROSS JOIN: Cartesian product. Returns every row in Table A paired with every row in Table B. (Danger! Can return millions of rows accidentally).

2. The Self Join

A Self Join is when you join a table to itself. This is essential for Hierarchies. For example, an Employees table where every row has a ManagerID pointing to another row in the same table.

SELECT E.Name AS Employee, M.Name AS Manager
FROM Employees E
LEFT JOIN Employees M ON E.ManagerID = M.EmployeeID

4. Interview Mastery

Q: "What is the difference between a Nested Loop Join and a Hash Join?"

Architect Answer: "These are physical join operators. A **Nested Loop** is efficient for small datasets where one side has an index. It loops through one table and looks up rows in the other. A **Hash Join** is used for massive datasets without indices. It builds a temporary hash table in RAM to find matches. If you see a Hash Join when joining two small tables, it usually means you are missing an Index on the join column."