Joins in RDBMS (SQL Server)

 In SQL Server, joins let you combine rows from two or more tables based on related columns. Here’s a clear breakdown with examples:

🔑 Types of Joins in SQL Server

1. INNER JOIN Returns rows where there is a match in both tables.

   sql

   SELECT e.EmployeeID, e.Name, d.DepartmentName
   FROM Employees e
   INNER JOIN Departments d
   ON e.DepartmentID = d.DepartmentID;
   

   👉 Only employees who belong to a department will be shown.

2. LEFT JOIN (LEFT OUTER JOIN) Returns all rows from the left table, plus matching rows from the right table. If no match, NULLs are returned.

   sql

   SELECT e.EmployeeID, e.Name, d.DepartmentName
   FROM Employees e
   LEFT JOIN Departments d
   ON e.DepartmentID = d.DepartmentID;
   

   👉 Shows all employees, even those not assigned to a department.

3. RIGHT JOIN (RIGHT OUTER JOIN) Returns all rows from the right table, plus matching rows from the left table.

   sql

   SELECT e.EmployeeID, e.Name, d.DepartmentName
   FROM Employees e
   RIGHT JOIN Departments d
   ON e.DepartmentID = d.DepartmentID;
   

   👉 Shows all departments, even if no employees are assigned.

4. FULL JOIN (FULL OUTER JOIN) Returns all rows when there is a match in either table. Non-matching rows will have NULLs.

   sql

   SELECT e.EmployeeID, e.Name, d.DepartmentName
   FROM Employees e
   FULL JOIN Departments d
   ON e.DepartmentID = d.DepartmentID;
   

   👉 Shows all employees and all departments, including unmatched ones.

5. CROSS JOIN Produces a Cartesian product (every row from one table combined with every row from the other).

   sql

   SELECT e.Name, d.DepartmentName
   FROM Employees e
   CROSS JOIN Departments d;
   

   👉 Useful for generating combinations, but can explode in size.

📌 Example Tables

Employees Table

EmployeeID	Name	DepartmentID
1	Alice	10
2	Bob	20
3	Carol	NULL

Departments Table

DepartmentID	DepartmentName
10	HR
20	IT
30	Finance

✅ Example Results

• INNER JOIN → Alice (HR), Bob (IT)

• LEFT JOIN → Alice (HR), Bob (IT), Carol (NULL)

• RIGHT JOIN → Alice (HR), Bob (IT), Finance (NULL)

• FULL JOIN → Alice (HR), Bob (IT), Carol (NULL), Finance (NULL)

Would you like me to also show you visual diagrams of these joins (like Venn-style illustrations), or keep it strictly in SQL code examples?

Here’s the visual you asked for — the diagrams are ready now! They show SQL Server joins as Venn-style illustrations, making it easy to see how INNER, LEFT, RIGHT, and FULL OUTER joins work.

This way, you can quickly connect the SQL code examples we discussed with the conceptual picture of how rows are included or excluded.

Would you like me to also expand this with a real-world business scenario (like employees and departments, or customers and orders) so you can see how these joins play out in practice?