A JOIN is used to combine rows from two or more tables based on a related column between them.

  • JOINs help retrieve related data from multiple tables in a single query.
  • SQL Server supports several types of JOINs:
JOIN Type Description
INNER JOIN Returns rows that have matching values in both tables.
LEFT JOIN Returns all rows from the left table and matching rows from the right table. Non-matching rows from right table are NULL.
RIGHT JOIN Returns all rows from the right table and matching rows from the left table. Non-matching rows from left table are NULL.
FULL OUTER JOIN Returns all rows when there is a match in either left or right table.
CROSS JOIN Returns the Cartesian product of both tables (all combinations).

Step 1: Example tables

Employees Table

EmployeeID FirstName LastName DepartmentID
1 John Doe 101
2 Jane Smith 102
3 Mike Brown 101
4 Sara White NULL

Departments Table

DepartmentID DepartmentName
101 IT
102 HR
103 Finance

Step 2: INNER JOIN

Returns only rows with matching values in both tables.

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

Result:

FirstName LastName DepartmentName
John Doe IT
Jane Smith HR
Mike Brown IT

Sara is excluded because she has NULL DepartmentID.

Step 3: LEFT JOIN

Returns all rows from left table, with NULL for non-matching right table rows.

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

Result:

FirstName LastName DepartmentName
John Doe IT
Jane Smith HR
Mike Brown IT
Sara White NULL

Step 4: RIGHT JOIN

Returns all rows from right table, with NULL for non-matching left table rows.

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

Result:

FirstName LastName DepartmentName
John Doe IT
Jane Smith HR
Mike Brown IT
NULL NULL Finance

Step 5: FULL OUTER JOIN

Returns all rows from both tables, with NULL where there is no match.

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

Result:

FirstName LastName DepartmentName
John Doe IT
Jane Smith HR
Mike Brown IT
Sara White NULL
NULL NULL Finance

Step 6: CROSS JOIN

Returns all combinations of rows from both tables.

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

Result: 4 employees × 3 departments = 12 rows

FirstName DepartmentName
John IT
John HR
John Finance
Jane IT
Jane HR
Jane Finance
Mike IT
Mike HR
Mike Finance
Sara IT
Sara HR
Sara Finance

Categorized in:

SQL Server,