[RESOLVED] SQL - Filter in ON-Clause vs. in WHERE-Clause

[Zvoni]

On a german forum i had a discussion with a very experienced user

Let's take this SQL-Statement

SELECT T1.ID, T1.Field2, T2.ID, T2.OtherField2 
FROM Table1 T1, Table2 T2 
WHERE T1.ID=T2.ID AND T2.OtherField2=SomeValue

Pretty straight forward: We're looking for records with equal ID's between the two tables, but we only want those records with a specific value in T2.OtherField2
Above SQL can be rewritten

SELECT T1.ID, T1.Field2, T2.ID, T2.OtherField2 
FROM Table1 T1
INNER JOIN Table2 T2 
ON T1.ID=T2.ID
WHERE T2.OtherField2=SomeValue

BUT

SELECT T1.ID, T1.Field2, T2.ID, T2.OtherField2 
FROM Table1 T1
INNER JOIN Table2 T2 
ON T1.ID=T2.ID AND T2.OtherField2=SomeValue

returns the same result as above.

The point of discussion on that german Forum was: A "Filter" belongs into a WHERE-Clause

My question to you: Why? Is it true? does it have any influence on performance?
And lastly:
Can i use parameters on a ON-Clause like

SELECT T1.ID, T1.Field2, T2.ID, T2.OtherField2 
FROM Table1 T1
INNER JOIN Table2 T2 
ON T1.ID=T2.ID AND T2.OtherField2=@SomeParameterName

i know, i could just mock up something and try it, but currently i don't have a lot of time

Thoughts? Opinions?

[wqweto]

> A "Filter" belongs into a WHERE-Clause

My first reaction to "overloaded" ON clause. It's by convention, all 3 queries are the same for the query planner.

There is a difference only with OUTER JOINs where you might want to bring a "filtering" predicate into the ON clause, for instance your

FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.ID=T2.ID AND T2.OtherField2=@SomeParameterName

is not equivalent to

FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.ID=T2.ID
WHERE T2.OtherField2=@SomeParameterName

. . . anymore because in the second query the WHERE clause (the equality operator precisely) will discard all NULLs in T2.OtherField2 and these NULLs might get there because of the OUTER JOIN, not because they are data in Table2.

So bringing the filtering to the ON clause for OUTER JOINs prevents filtering out the non-matching rows.

It's like "first filter Table2 for @SomeParameterName and then with the result of this do OUTER JOIN with Table1 i.e. keep the the non-matching rows from Table1 and return NULL for T2.OtherField2 for these in the resultset"

cheers,
</wqw>

[techgnome]

It's definately not something that should be reached for first hand. I have run into a couple of situations where I did do some filtering on the join clause - with hard-coded values, not parameters - to tweak a query, but it was only done after hours and hours of testing and banging my head against the desk as to why something was giving me crap results. It turned out to be for that reason that wqweto pointed out. Once I added "and t2.someField =1" to parts of the join (one join was where somefield =1, another was where somefield = 2 and so on...) things started clicking and the query not only started returning the right results, but also started performing better (it was part of a larger massive query). And it's not something I've thought about much or given a lot of thought about since until now.

-tg

[szlamany]

In my opinion, maintainability is very important in all my queries - client support goes on for decades and enhancements always come down the pike.

If you want to limit the JOIN'd rows, the criteria goes with the ON clause.

If you want to filter the working resultset after the JOINS are done, it goes with the WHERE clause. Of course, thinking the "working resultset" is going through such simple and primitive passes belongs back in the pre-SQL 2000 world most likely!

But you do want to make things clear to the EXECUTION PLAN logic...

And you want to make things clear to future moments when you walk back into that query!

[Zvoni]

OK, thx everybody for the Feedback.
After doing some simple tests, i can see where the pitfalls are, as correctly pointed out: For Outer Joins (LEFT/RIGHT)

[FunkyDexter]

For me the strongest argument for not putting filters in the ON clause is when you consider Outer Joins.

Select *
From A
Left Join B
   On A.ID = B.ID
   And A.Filter = true

is not equivalent to

Select *
From A
Left Join B
   On A.ID = B.ID
Where A.Filter = true

The first will return all records for A but limit the matches found in B. The second will only return records for A where filter is true.

Other than that I think the maintainability arguments that have already been offered are equally important.

[techgnome]

If memory serves, the couple of times when I did do it, it's when I needed to join to the same table twice under different circumstances....

Select *
From A
Inner Join B
   On A.ID = B.ID
   And B.Filter = true
Left Join B B2
   On A.ID = B2.ID
   And B2.Filter = false

-tg

[Zvoni]

FD,
First code line 5
shouldn‘t that be „…and B.Filter=True“

i had to use filters in a Left join in the On-clause for the right hand table/expression because i used Aliases for Window-Functions,
and Aliases are always „fun“ in a WHERE or HAVING-clause

but i agree in general: you should know what you expect back

[Zvoni]

5556723[/URL]]If memory serves, the couple of times when I did do it, it's when I needed to join to the same table twice under different circumstances....

Select *
From A
Inner Join B
   On A.ID = B.ID
   And B.Filter = true
Left Join B B2
   On A.ID = B2.ID
   And B2.Filter = false

-tg

tg,
exactly.
and the argument on the german forum was to rewrite your query as a SELECT-Statement for the right hand side of the join (or even as a CTE)
kinda like

Select *
From A
Inner Join (Select * From B Where Filter=True) B
On A.ID = B.ID
Left Join (Select * From B Where Filter=False) B2
On A.ID = B2.ID

i‘ve come to see a Filter in an ON-Clause as a kinda shortcut/equivalent to the above with the advantage i can use Field-Aliases

[FunkyDexter]

shouldn‘t that be „…and B.Filter=True“

No, I definitely meant A.Filter. The point is that if the clause applies to the left hand table in a left join, it behaves as a filter on that table when it's in the where clause. If it's in the join it merely limits the matches in finds in the right hand table:-

Create table #A (ID int, Filter bit)
Create Table #B (ID int)
 
Insert into #A Values (1, 1)
Insert into #B Values (1)
 
Select *
From #A A
Left Join #B B
    on A.ID = B.ID
    And A.Filter = 0
 
 
Select *
From #A A
Left Join #B B
    on A.ID = B.ID
Where A.Filter = 0
 
Drop Table #A
Drop Table #B

You do get differences with B.Filter as well but it mostly comes down to having to check for nulls.