Outer Joins: ON vs WHERE Conditions

ON vs WHERE in an OUTER JOIN

The main difference is when the condition is applied:

• ON filters rows while joining the tables.

• WHERE filters rows after the join result is created.

This difference is especially important for LEFT, RIGHT, and FULL OUTER JOIN.

Assume:

Employees

Departments

Condition in the WHERE clause

SELECT
    E.EmployeeName,
    D.DepartmentName
FROM Employees E
LEFT JOIN Departments D
    ON E.DepartmentId = D.DepartmentId
WHERE D.DepartmentName = 'IT';

Result

What happened?

The LEFT JOIN initially returns all employees. But after the join, the WHERE condition removes:

• Banu because her department is HR.

• Charles because his department value is NULL.

Therefore, the query effectively behaves like an INNER JOIN.

Condition in the ON clause

SELECT
    E.EmployeeName,
    D.DepartmentName
FROM Employees E
LEFT JOIN Departments D
    ON E.DepartmentId = D.DepartmentId
   AND D.DepartmentName = 'IT';

Result

What happened?

The condition controls which department rows can match, but the LEFT JOIN still preserves every employee.

• Arun matches IT.

• Banu’s HR department does not satisfy the condition, so department data becomes NULL.

• Charles has no department, so department data is NULL.

Quick comparison

Finding employees without departments

A WHERE condition is intentionally used here:

SELECT
    E.EmployeeName
FROM Employees E
LEFT JOIN Departments D
    ON E.DepartmentId = D.DepartmentId
WHERE D.DepartmentId IS NULL;

This returns employees who have no matching department.

Important key point

For a LEFT JOIN:

• Put right-table filtering conditions in ON when you want to keep all left-table rows.

• Put conditions in WHERE when you want to filter the final result.

A condition on the preserved table usually behaves similarly in ON and WHERE, but the result can still differ:

-- Keeps all employees, but only DepartmentId 10 can match
LEFT JOIN Departments D
    ON E.DepartmentId = D.DepartmentId
   AND E.DepartmentId = 10

-- Returns only employees whose DepartmentId is 10
LEFT JOIN Departments D
    ON E.DepartmentId = D.DepartmentId
WHERE E.DepartmentId = 10

Interview answer:
“The ON clause controls whether rows match during the join, while the WHERE clause filters the completed result. In an outer join, filtering the non-preserved table in WHERE may remove NULL-extended rows and effectively convert the outer join into an inner join.”