Joins in SQL: Types, Syntax, Examples & Use Cases

Estimated study time: 12 minutes. Every join type in one place, with syntax and examples.

Joins combine rows from two or more tables based on a related column. SQL Server supports several join types, each suited to a different question about your data.

INNER JOIN

Returns only rows with a match in both tables.

SELECT e.EmpName, d.DeptName
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.DeptID;

LEFT JOIN

Returns all rows from the left table, matched or not.

SELECT e.EmpName, d.DeptName
FROM Employees e
LEFT JOIN Departments d ON e.DeptID = d.DeptID;

RIGHT JOIN

Returns all rows from the right table, matched or not.

SELECT e.EmpName, d.DeptName
FROM Employees e
RIGHT JOIN Departments d ON e.DeptID = d.DeptID;

FULL OUTER JOIN

Returns all rows from both tables, filling in NULLs where there's no match on either side.

SELECT e.EmpName, d.DeptName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DeptID = d.DeptID;

SELF JOIN

Joins a table to itself, useful for hierarchical or comparative data like manager-employee relationships.

SELECT a.EmpName AS Employee, b.EmpName AS Manager
FROM Employees a
INNER JOIN Employees b ON a.ManagerID = b.EmpID;

CROSS JOIN

Returns the Cartesian product — every row from the first table paired with every row from the second.

SELECT Colors.Color, Sizes.Size
FROM Colors
CROSS JOIN Sizes;