Today, we will discuss an interesting question raised by one of our readers, Yoel from Israel. Yoel asked about the difference between using the WHERE clause and the ON clause when filtering records in SQL Server joins. This is a common question that many SQL Server users have, so let’s dive into the topic and explore the nuances of these clauses.

First, let’s clarify the difference between INNER joins and OUTER joins. INNER joins return only the matching records from both tables, while OUTER joins return all records from one table and the matching records from the other table.

When using INNER joins, the placement of the filtering condition in the WHERE clause or the ON clause does not make a difference. The result will be the same. However, when using OUTER joins, the placement of the filtering condition can affect the result.

Let’s take a look at an example to better understand this concept:

USE AdventureWorks

-- Create Table1
CREATE TABLE Table1 (
    ID INT,
    Value VARCHAR(10),
    Flag INT
)

-- Populate Table1
INSERT INTO Table1 (ID, Value, Flag)
VALUES
    (1, 'First', 1),
    (2, 'Second', 1),
    (3, 'Third', 2),
    (4, 'Fourth', 1),
    (5, 'Fifth', 2),
    (6, 'Sixth', 1),
    (7, 'Seventh', 2)

-- Create Table2
CREATE TABLE Table2 (
    ID INT,
    Value VARCHAR(10),
    Flag INT
)

-- Populate Table2
INSERT INTO Table2 (ID, Value, Flag)
VALUES
    (1, 'First', 1),
    (2, 'Second', 1),
    (3, 'Third', 2),
    (8, 'Eightth', 1),
    (9, 'Nineth', 2)

-- INNER JOIN with WHERE Condition
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Flag = 1

-- INNER JOIN with Additional Condition on ON clause
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1

-- LEFT JOIN with WHERE Condition
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1

-- LEFT JOIN with Additional Condition on ON clause
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Flag = 1

-- Clean up tables
DROP TABLE Table1
DROP TABLE Table2

In this example, we have two tables, Table1 and Table2, with some sample data. We perform both INNER and LEFT joins on these tables, using both the WHERE clause and the ON clause to filter the results.

When using INNER joins, whether we use the WHERE clause or the ON clause to filter the results, the outcome is the same. The filtering condition is applied to the result of the join, so it does not affect the join itself.

However, when using LEFT joins, the placement of the filtering condition can make a difference. If we use the ON clause to filter the results, the condition is applied before the join, affecting only the table being joined. But if we use the WHERE clause to filter the results, the condition is applied to the complete result, affecting both tables.

So, in the case of LEFT joins, using the ON clause to filter the results can give different results compared to using the WHERE clause. The ON clause filters the joined table, while the WHERE clause filters the complete result.

I hope this explanation clarifies the difference between using the WHERE clause and the ON clause in SQL Server joins. If you have any further questions or feedback, please leave a comment below. And if you found this article helpful, please share it with your network.