Understanding Outer Join in SQL

Estimated study time: 12 minutes. LEFT, RIGHT, and FULL — the three outer joins compared.

An outer join returns matched rows plus some unmatched rows too, unlike an INNER JOIN which only keeps matches. SQL Server has three outer join variants: LEFT, RIGHT, and FULL.

LEFT OUTER JOIN

Keeps every row from the left table, filling in NULLs for the right table where there's no match.

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

RIGHT OUTER JOIN

Keeps every row from the right table, filling in NULLs for the left table where there's no match.

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

FULL OUTER JOIN

Keeps every row from both tables, filling in NULLs on whichever side lacks a match.

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

Outer Join vs Inner Join

An INNER JOIN only ever returns matched pairs. An outer join guarantees rows from one side (or both) show up regardless of whether a match exists — the trade-off is a result set that can include NULLs you'll need to handle.