Condition in ON cluase of OUETR JOIN Effects

Introduction

Where we are making JOIN, a lot of developer uses extra condition within the ON clause of the JOIN. In my review I find that some developer added a lot of condition in the ON clause (they are including WHERE condition within the ON clues of the JOIN).

Please remind it that the extra condition of ON clause of JOIN can affect the OUTER JOIN. This article is related to it.

What MS says

First we look at what MS Says about it.

"Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join."

Example of Extra Condition

To understand it properly am taking an example of that.

Step-1 [ Create the Base Table ]

IF OBJECT_ID('my_TABLE1') IS NOT NULL

   BEGIN

     DROP TABLE my_TABLE1;

   END

GO

CREATE TABLE my_TABLE1

       (ID     INT         NOT NULL IDENTITY PRIMARY KEY,

        SNAME  VARCHAR(50) NOT NULL);

GO       

IF OBJECT_ID('my_TABLE2') IS NOT NULL

   BEGIN

     DROP TABLE my_TABLE2;

   END

GO 

CREATE TABLE my_TABLE2

       (ID      INT         NOT NULL IDENTITY PRIMARY KEY,

        SECTION VARCHAR(1)  NOT NULL);

GO            

    

Step-2 [ Insert Records ]

INSERT INTO  my_TABLE1

       (SNAME)

VALUES ('JOYDEEP'),

       ('SUKAMAL'),

       ('TUHIN'),

       ('SANGRAM'),

       ('ROOHUL');

GO      

INSERT INTO  my_TABLE2

       (SECTION)

VALUES ('A'),

       ('B'),

       ('A');   

Step-3 [ Make the INNER JOIN ]

SELECT  a.ID, a.SNAME, b.SECTION

FROM    my_TABLE1 a

        INNER JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A'  

       

SELECT  a.ID, a.SNAME, b.SECTION

FROM    my_TABLE1 a

        INNER JOIN my_TABLE2 b ON a.ID = b.ID

WHERE    b.SECTION = 'A' 

Both of the Queries display the same output

ID         SNAME                        SECTION

1          JOYDEEP                    A

3          TUHIN                           A

Step-4 [ Make the LEFT JOIN ]

SELECT  a.ID, a.SNAME, b.SECTION

FROM    my_TABLE1 a

        LEFT JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A' 

Output:

ID         SNAME                        SECTION

1          JOYDEEP                    A

2          SUKAMAL                    NULL

3          TUHIN                           A

4          SANGRAM                    NULL

5          ROOHUL                       NULL

SELECT  a.ID, a.SNAME, b.SECTION

FROM    my_TABLE1 a

        LEFT JOIN my_TABLE2 b ON a.ID = b.ID

WHERE    b.SECTION = 'A' 

Output:

ID         SNAME                        SECTION

1          JOYDEEP                    A

3          TUHIN                          A

Observation -1

In case of INNER JOIN both the query is fine and work perfectly.

But in case of LEFT JOIN both the query react differently.

In case of LEFT OUTER JOIN first query the SECTION = 'A' condition works on before JOIN. It retrieves the result from my_TABLE2 and LEFT OUTER JOIN with my_TABLE1 and it does not affect the my_TABLE1.

When we add the WHERE clause it effects of the complete result set after JOIN and filters the records.

Observation -2

Now we see the actual execution plan of both query (INNER JOIN and LEFT JOIN).

SELECT  a.ID, a.SNAME, b.SECTION

FROM    my_TABLE1 a

        INNER JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A';

        

SELECT  a.ID, a.SNAME, b.SECTION

FROM    my_TABLE1 a

        LEFT JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A'; 

In case of Query-2 the hash match operator is used as join. In Query-2 by optimizer will be switched to inner join instead left outer join.

Hope you like it.

Posted by: MR. JOYDEEP DAS