none
SQL inner join clause and the where clause

    Question

  • Hi everyone,
    I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?

    Thanks
    Monday, January 21, 2008 5:32 PM

Answers

  •  

    ON, is widely agreed to use the JOIN condition (On t1.Column  =  t2.Column)

    WHERE, is helpful to have additional filters..

     

    But, you can move all your filters from WHERE  to ON clause & vice-versa. But, ON clause requires minimum one logical expression, followed by ON.

     

    All the quires will give same output & same performance. But, when you use OUTER JOIN the WHERE clause filter & ON clause might give different result.

     

     

    select * from sysobjects as so

                inner join syscolumns as sc on so.id=sc.id

    where

                so.name='sysobjects'

     

     

    select * from sysobjects as so

                inner join syscolumns as sc on so.id=sc.id and so.name='sysobjects'

     

     

    select * from sysobjects as so

                inner join syscolumns as sc on 1=1

    where

                so.id=sc.id and so.name='sysobjects'

               

     

    Monday, January 21, 2008 5:54 PM
  • JavaBoy,

     

    For INNER JOIN, it does not matter where do you put the criteria. You can check both execution plans.

     

    For OUTER JOIN it is different, using the criteria in the WHERE clause could yield not expected result, because the meaning or logic change.

     

    For readability, I prefer to use in the JOIN just the criteria used to join the tables (columns involved in the relationship), and the filter (criteria used to filter the rows you want to be part of the result) in the WHERE clause.

     

     

    AMB

    Monday, January 21, 2008 5:55 PM
  • Hi,

    Few more points on WHERE & ON.

    [CODE]

    SELECT E.ENAME, D.DNAME

    FROM EMP E
    INNER JOIN DEPT D
    WHERE E.DEPTNO = D.DEPTNO

    SELECT E.ENAME, D.DNAME

    FROM EMP E
    INNER JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO

    [/CODE]

    The above two queries produces the same results set, but can have vastly different performance hits depending on the parser. 

     

    Consider the below points:
    1.  Which version produces a query plan that is better optimized.
    2.  Which is more maintainable should the code need to be retrofitted later to incorporate more joined (INNER or OUTER) tables.

    Thanks -- Vj

    Monday, January 21, 2008 6:19 PM

All replies

  •  

    ON, is widely agreed to use the JOIN condition (On t1.Column  =  t2.Column)

    WHERE, is helpful to have additional filters..

     

    But, you can move all your filters from WHERE  to ON clause & vice-versa. But, ON clause requires minimum one logical expression, followed by ON.

     

    All the quires will give same output & same performance. But, when you use OUTER JOIN the WHERE clause filter & ON clause might give different result.

     

     

    select * from sysobjects as so

                inner join syscolumns as sc on so.id=sc.id

    where

                so.name='sysobjects'

     

     

    select * from sysobjects as so

                inner join syscolumns as sc on so.id=sc.id and so.name='sysobjects'

     

     

    select * from sysobjects as so

                inner join syscolumns as sc on 1=1

    where

                so.id=sc.id and so.name='sysobjects'

               

     

    Monday, January 21, 2008 5:54 PM
  • JavaBoy,

     

    For INNER JOIN, it does not matter where do you put the criteria. You can check both execution plans.

     

    For OUTER JOIN it is different, using the criteria in the WHERE clause could yield not expected result, because the meaning or logic change.

     

    For readability, I prefer to use in the JOIN just the criteria used to join the tables (columns involved in the relationship), and the filter (criteria used to filter the rows you want to be part of the result) in the WHERE clause.

     

     

    AMB

    Monday, January 21, 2008 5:55 PM
  • Hi,

    Few more points on WHERE & ON.

    [CODE]

    SELECT E.ENAME, D.DNAME

    FROM EMP E
    INNER JOIN DEPT D
    WHERE E.DEPTNO = D.DEPTNO

    SELECT E.ENAME, D.DNAME

    FROM EMP E
    INNER JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO

    [/CODE]

    The above two queries produces the same results set, but can have vastly different performance hits depending on the parser. 

     

    Consider the below points:
    1.  Which version produces a query plan that is better optimized.
    2.  Which is more maintainable should the code need to be retrofitted later to incorporate more joined (INNER or OUTER) tables.

    Thanks -- Vj

    Monday, January 21, 2008 6:19 PM