Understanding JOINs in SQL Server

JOINs let you combine rows from two or more tables based on a related column. They are fundamental to relational databases because data is typically normalized across multiple tables. SQL Server supports several join types, each returning a different subset of matching rows.

INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables. If an employee has no matching department, that employee is excluded from the results.

SELECT
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

LEFT JOIN

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If there is no match, the right side columns contain NULL.

-- Show all customers, even those without orders
SELECT
    c.CustomerName,
    o.OrderID,
    o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerName;

RIGHT JOIN

A RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table and matched rows from the left table. In practice, most developers prefer LEFT JOIN and simply swap the table order.

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

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables. Where there is no match, the missing side fills with NULLs. This is useful for finding orphaned records.

SELECT
    e.FirstName,
    d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.EmployeeID IS NULL OR d.DepartmentID IS NULL;

Joining Multiple Tables

You can chain multiple JOINs in a single query. Each JOIN adds another table to the result set using its own ON condition.

SELECT
    o.OrderID,
    c.CustomerName,
    p.ProductName,
    oi.Quantity,
    oi.UnitPrice
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
WHERE o.OrderDate >= '2024-01-01'
ORDER BY o.OrderDate DESC;

Self JOIN

A self join joins a table to itself. This is common for hierarchical data like employee-manager relationships.

SELECT
    e.FirstName AS Employee,
    m.FirstName AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

Understanding when to use each join type is essential for writing correct queries. Use INNER JOIN when you only want matched rows, LEFT JOIN when the left table is the primary source, and FULL OUTER JOIN when you need to detect mismatches between two tables.