SQLErudition.com

A filter condition can be syntactically put in the WHERE clause of the query or the ON clause too. But does it matter?

No, it does not matter for an INNER join. I found this one example of an exception in Martin Smith’s comment on Stack Overflow where using the GROUP BY ALL makes a difference in results. GROUP BY ALL is now a deprecated syntax (Feature ID 169) so probably we can ignore this exception. But it is always good to know.

On the other hand, Yes, it could affect the output in any of the OUTER joins.

This happens because when a filter criteria is made a part of the JOIN condition, it is applied at the time of reading the tables to identify the rows for the JOIN. The rows that do not satisfy the filter criteria are not included for the purpose of the JOIN and that causes NULLs in the OUTER JOINs.

However, with the filter criteria a part of the WHERE clause, it is applied after the query has been processed and the adhoc dataset has been created internally. The final dataset is reduced to just those rows which meet the filter criteria.

I will use a criteria of c.isactiveYN = ‘Y’ for demonstration below. Rows with a value of ‘Y’ are used for the join. The rows with ‘N’ or NULLs don’t match the criteria so they are left out.

As the examples show below, this seemingly minor difference can cause substantial difference in the query output of OUTER JOINs. This effect could cascade on to other subsequent tables in bigger queries that are joined to the original tables.

-- Create demo tables and insert some data
declare @customer table 
(id int, 
cname varchar(20), 
isactiveYN char(1))

declare @order table 
(id int, 
customerid int, 
orderdate date)

insert into @customer 
values
(1, 'Bob',   'Y'),
(2, 'Harry', 'N'),
(3, 'Max',   'Y')

insert into @order
values
(1, 1, '2014-1-1'),
(2, 1, '2014-2-2'),
(3, 2, '2014-3-3'),
(4, 2, '2014-4-4'),
(5, 3, '2014-5-5'),
(6, 3, '2014-6-6')

select * from @customer
select * from @order

Results :

 

Demonstration: The location of criteria has no effect on the results in INNER JOIN.

-- A simple INNER JOIN without any 
-- conditions to display all data:

-- INNER JOIN with out any conditions
select * 
from 
  @customer as c
inner join 
  @order as o
  on c.id = o.customerid

-- The following two queries differ
-- in the location of the criteria
-- but still show similar results

-- INNER JOIN with filter in WHERE clause
select * 
from 
  @customer as c
inner join 
  @order as o
  on c.id = o.customerid
where
  c.isactiveYN = 'Y'

-- INNER JOIN with filter in ON clause
select * 
from
  @customer as c
inner join 
  @order as o
  on c.id = o.customerid
  and c.isactiveYN = 'Y'

Results : The second and third results are similar.

 

Demonstration: LEFT OUTER joins with the condition in the WHERE clause and the ON clause

-- LEFT JOIN: Filter in WHERE clause
select * 
from 
  @customer as c
left outer join 
  @order as o
  on c.id = o.customerid
where 
  c.isactiveYN = 'Y'

-- LEFT JOIN: Filter in ON clause
select * 
from 
  @customer as c
left outer join
  @order as o
  on c.id = o.customerid
  and c.isactiveYN = 'Y'

Results : “Harry” is not in the first result-set because the row was filtered out by the WHERE clause. “Harry” is in the second result-set because it comes from the LEFT OUTER table but there is no matching row because it was not used for the JOIN.

 

Demonstration: RIGHT OUTER joins with the condition in the WHERE clause and the ON clause

-- RIGHT JOIN: Filter in WHERE clause
select * 
from 
  @customer as c
right outer join 
  @order as o
  on c.id = o.customerid
where 
  c.isactiveYN = 'Y'

-- RIGHT JOIN: Filter in ON clause
select * 
from 
  @customer as c
right outer join 
  @order as o
  on c.id = o.customerid
  and c.isactiveYN = 'Y'

Results : “Harry” is not in the first result-set because the row was filtered out by the WHERE clause. “Harry” is not in the second result-set because it has been filtered out and it is also not in the OUTER table.

 

Summary: When the criteria is in the WHERE clause, The results of the INNER, LEFT OUTER and the RIGHT OUTER queries are the same. Differences show up in the results of LEFT and RIGHT OUTER queries when the criteria is in the ON clause.