ON vs WHERE in SQL

8
0

Introduction

SQL queries often combine related tables and apply conditions to select useful records. Although ON and WHERE can contain similar conditions, they serve different purposes.

The distinction becomes especially important with outer joins, where unmatched records may need to remain visible. Placing a condition in the correct clause helps preserve the required records and avoid unexpectedly removing them.

What Is the Difference Between ON and WHERE?

The ON clause defines which pairs of rows qualify as matches in a join. The WHERE clause filters the rows produced by the FROM clause, including any joins.

Aspect

ON

WHERE

Main purpose

Define matching conditions for a join

Filter the resulting rows

With an inner join

Determines which row pairs match

Can further restrict those matches

With an outer join

Determines matches while unmatched rows from the preserved side remain

Can remove rows, including unmatched rows

Without a join

Not used as a standalone row filter

Can filter a single table

An ON condition can include both a comparison between tables and additional requirements involving one table. It is not limited to comparing IDs.

Example Tables

Consider a college database containing two tables.

The Department table stores the departments offered by the college. A department can exist even when no students are assigned to it.

Department

DeptID

DeptName

10

Computer Science

20

Electronics

30

Mathematics

The Student table stores each student’s identifier, name, assigned department, and semester.

Student

StudentID

Name

DeptID

Semester

101

Asha

10

2

102

Ravi

20

4

103

Meera

10

2

104

Neha

NULL

2

Department.DeptID and Student.StudentID are primary keys. A non-NULL value in Student.DeptID refers to an existing department.

Asha and Meera belong to Computer Science and are in semester 2. Ravi belongs to Electronics and is in semester 4. Mathematics has no students, while Neha has no assigned department.

ON vs. WHERE with INNER JOIN

An INNER JOIN returns only matching row pairs. For a condition such as checking a student’s semester, placing it in ON or WHERE produces the same result when the rest of the query remains unchanged.

Objective: Display students in semester 2 who belong to an existing department.

Consider the following queries to apply the semester condition in either ON or WHERE.

-- Semester condition in ON
SELECT d.DeptID, d.DeptName, s.Name, s.Semester
FROM Department AS d
INNER JOIN Student AS s
ON d.DeptID = s.DeptID
AND s.Semester = 2
ORDER BY d.DeptID, s.StudentID;
-- Semester condition in WHERE
SELECT d.DeptID, d.DeptName, s.Name, s.Semester
FROM Department AS d
INNER JOIN Student AS s
ON d.DeptID = s.DeptID
WHERE s.Semester = 2
ORDER BY d.DeptID, s.StudentID;

AS d and AS s assign temporary aliases, or shorter names, to Department and Student. For example, s.Semester refers to the student’s semester. AND requires both conditions to be true.

ORDER BY d.DeptID, s.StudentID sorts by department ID first and student ID within each department, both in ascending order.

Expected result is same for both placements:

DeptID

DeptName

Name

Semester

10

Computer Science

Asha

2

10

Computer Science

Meera

2

Both versions require matching department IDs and semester 2. Ravi fails the semester condition. Neha has no matching department, and Mathematics has no matching student, so neither contributes a row to this inner join.

For readability, an inner join commonly keeps the relationship between tables in ON and places additional result filters in WHERE.

INNER JOIN : ON VS WHERE

INNER JOIN : ON VS WHERE

ON vs. WHERE with LEFT JOIN

A LEFT JOIN preserves every row from the left table during the join. When no right-table row satisfies the complete ON condition, the right-table columns receive NULL.

In the following examples, Department is the left table and Student is the right table. The placement of the semester condition changes whether departments without qualifying students remain visible.

Condition in ON: Preserve Every Department

Objective: Display every department, showing student details only for students in semester 2.

Consider the following queries to restrict student matches while keeping all departments.

SELECT d.DeptID, d.DeptName, s.Name, s.Semester
FROM Department AS d
LEFT JOIN Student AS s
ON d.DeptID = s.DeptID
AND s.Semester = 2
ORDER BY d.DeptID, s.StudentID;

Expected result

DeptID

DeptName

Name

Semester

10

Computer Science

Asha

2

10

Computer Science

Meera

2

20

Electronics

NULL

NULL

30

Mathematics

NULL

NULL

Asha and Meera satisfy both parts of the ON condition. Ravi does not qualify because he is in semester 4, but Electronics remains because the left join preserves every department.

The NULL values for Electronics mean no student matched the complete condition. They do not mean Electronics has no students. Mathematics also remains, although it has no students at all.

Condition in WHERE: Filter the Joined Rows

Objective: Display only rows containing a matched student in semester 2.

Consider the following queries to apply the semester requirement to the joined result.

SELECT d.DeptID, d.DeptName, s.Name, s.Semester
FROM Department AS d
LEFT JOIN Student AS s
ON d.DeptID = s.DeptID
WHERE s.Semester = 2
ORDER BY d.DeptID, s.StudentID;

Expected result

DeptID

DeptName

Name

Semester

10

Computer Science

Asha

2

10

Computer Science

Meera

2

The join initially matches Ravi with Electronics and preserves Mathematics with NULL student columns. The WHERE condition then removes Ravi’s row because his semester is 4 and removes Mathematics because its student-side semester is NULL.

WHERE keeps only rows for which its condition is true. A comparison such as NULL = 2 is unknown, so it does not pass the filter.

For this particular condition, the left join produces the same final result as an inner join with the semester filter. This does not mean every WHERE condition removes unmatched rows: an IS NULL condition can deliberately select them.

LEFT JOIN : ON VS WHERE

LEFT JOIN : ON VS WHERE

ON vs. WHERE with RIGHT JOIN

A RIGHT JOIN preserves every row from the right table during the join. When no left-table row satisfies the complete ON condition, the left-table columns receive NULL.

In the following examples, Student is the left table and Department is the right table. The join therefore preserves departments, while the placement of the semester condition determines which rows remain in the final result.

Condition in ON: Preserve Every Department

Objective: Display every department, showing student details only for students in semester 2.

Consider the following queries to restrict student matches while keeping all departments.

SELECT d.DeptID, d.DeptName, s.Name, s.Semester
FROM Student AS s
RIGHT JOIN Department AS d
ON s.DeptID = d.DeptID
AND s.Semester = 2
ORDER BY d.DeptID, s.StudentID;

AS s and AS d give shorter names to Student and Department. The AND operator requires both conditions to be true: the department IDs must match, and the student must be in semester 2.

ORDER BY sorts the result by department ID first and student ID within each department, both in ascending order.

Expected result

DeptID

DeptName

Name

Semester

10

Computer Science

Asha

2

10

Computer Science

Meera

2

20

Electronics

NULL

NULL

30

Mathematics

NULL

NULL

Asha and Meera satisfy both conditions, so their details appear with Computer Science. Ravi does not satisfy the semester condition because he is in semester 4. Electronics still appears because it belongs to the preserved right table.

Mathematics also remains, although it has no students. In both unmatched rows, NULL means no student satisfied the complete join condition. It does not necessarily mean the department has no students.

Neha does not appear because her department ID is NULL, so she has no matching department. A right join does not preserve unmatched rows from the left table.

Condition in WHERE: Filter the Joined Rows

Objective: Display only rows containing a matched student in semester 2.

Consider the following queries to apply the semester requirement to the joined result.

SELECT d.DeptID, d.DeptName, s.Name, s.Semester
FROM Student AS s
RIGHT JOIN Department AS d
ON s.DeptID = d.DeptID
WHERE s.Semester = 2
ORDER BY d.DeptID, s.StudentID;

Expected result

DeptID

DeptName

Name

Semester

10

Computer Science

Asha

2

10

Computer Science

Meera

2

The join matches students to departments using only their department IDs. The WHERE condition then filters those joined rows:

  • Computer Science: Asha and Meera remain because their semester is 2.

  • Electronics: Ravi’s row is removed because his semester is 4.

  • Mathematics: Its unmatched row is removed because the student-side semester is NULL.

WHERE keeps only rows whose condition is true. Comparing NULL with 2 produces an unknown result, so the Mathematics row does not pass the filter.

For this particular condition, the right join produces the same final result as an inner join with the semester filter. To retain every department while limiting which students match, place the semester condition in ON.

The same distinction applies to a FULL OUTER JOIN, which preserves unmatched rows from both tables during the join: a later WHERE condition can still remove those rows.

RIGHT JOIN : ON VS WHERE

RIGHT JOIN : ON VS WHERE

Filtering the Preserved Table

An ON condition does not remove a row merely because that row belongs to the preserved table and fails the condition. It prevents matches, but the outer join can still retain that row with NULL values from the other side.

For example, if the report should contain only Computer Science, the condition d.DeptID = 10 belongs in WHERE. Placing it only in the ON clause of the department-preserving left join would still retain Electronics and Mathematics, with NULL student details.

This separates two different requirements: deciding which records may match and deciding which departments should appear in the report.

Summary

ON defines which rows match in a join, while WHERE filters the resulting rows. A straightforward condition can produce the same result in either clause with an inner join, but moving it between clauses can change an outer join’s result.

For outer joins, identify the side whose unmatched records must remain. Use ON to control qualifying matches and WHERE to select the required result rows, including unmatched rows when an IS NULL check is appropriate.

CS Core

Read Similar Blogs

Comments0