Results 1 to 10 of 10

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

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

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

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

    Let's take this SQL-Statement
    Code:
    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
    Code:
    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
    Code:
    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
    Code:
    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?
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  2. #2
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    4,321

    Re: SQL - Filter in ON-Clause vs. in WHERE-Clause

    > 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>

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,991

    Re: SQL - Filter in ON-Clause vs. in WHERE-Clause

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,169

    Re: SQL - Filter in ON-Clause vs. in WHERE-Clause

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: SQL - Filter in ON-Clause vs. in WHERE-Clause

    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)
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,806

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

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

    sql Code:
    1. Select *
    2. From A
    3. Left Join B
    4.    On A.ID = B.ID
    5.    And A.Filter = true
    is not equivalent to
    sql Code:
    1. Select *
    2. From A
    3. Left Join B
    4.    On A.ID = B.ID
    5. 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.
    Last edited by FunkyDexter; Feb 18th, 2022 at 10:47 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,991

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

    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....
    sql Code:
    1. Select *
    2. From A
    3. Inner Join B
    4.    On A.ID = B.ID
    5.    And B.Filter = true
    6. Left Join B B2
    7.    On A.ID = B2.ID
    8.    And B2.Filter = false

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

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

    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
    Last edited by Zvoni; Feb 18th, 2022 at 11:35 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

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

    Quote Originally Posted by techgnome;[URL="tel:5556723"
    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....
    sql Code:
    1. Select *
    2. From A
    3. Inner Join B
    4.    On A.ID = B.ID
    5.    And B.Filter = true
    6. Left Join B B2
    7.    On A.ID = B2.ID
    8.    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
    Code:
    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
    Last edited by Zvoni; Feb 18th, 2022 at 11:32 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,806

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

    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:-

    sql Code:
    1. Create table #A (ID int, Filter bit)
    2. Create Table #B (ID int)
    3.  
    4. Insert into #A Values (1, 1)
    5. Insert into #B Values (1)
    6.  
    7. Select *
    8. From #A A
    9. Left Join #B B
    10.     on A.ID = B.ID
    11.     And A.Filter = 0
    12.  
    13.  
    14. Select *
    15. From #A A
    16. Left Join #B B
    17.     on A.ID = B.ID
    18. Where A.Filter = 0
    19.  
    20. Drop Table #A
    21. Drop Table #B

    You do get differences with B.Filter as well but it mostly comes down to having to check for nulls.
    Last edited by FunkyDexter; Feb 18th, 2022 at 02:48 PM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width