Left Join: Difference between ON and WHERE

Below is one example to show the difference using ON and WHERE to filter rows in LEFT JOIN:

 

CREATE TABLE LEFTT1
(C1 INT,
C2 NVARCHAR(20));

CREATE TABLE LEFTT2
(C1 INT,
C2 NVARCHAR(30));

INSERT INTO LEFTT1
VALUES (1,’1ABCDD’),
(2,’1BBBB’),
(3,’1CCCC’),
(4,’1DDDD’)

INSERT INTO LEFTT2
VALUES (1,’2ABBB’),
(3,’2CBDB’)

SELECT * FROM LEFTT1 T1 LEFT JOIN LEFTT2 T2
ON T1.C1 = T2.C1 AND T1.C1 <4

SELECT * FROM LEFTT1 T1 LEFT JOIN LEFTT2 T2
ON T1.C1 = T2.C1
WHERE
    T1.C1 < 4

 

Result:

c1          c2                   c1          c2
———– ——————– ———– ——————————
1           1abcdd               1           2abbb
2           1bbbb                NULL        NULL
3           1cccc                3           2cbdb
4           1dddd                NULL        NULL

(4 row(s) affected)

c1          c2                   c1          c2
———– ——————– ———– ——————————
1           1abcdd               1           2abbb
2           1bbbb                NULL        NULL
3           1cccc                3           2cbdb

 

First SELECT query return 4 rows when using ON and second query returns 3.

 

Now let’s try add one row in LeftT2:

INSERT INTO LEFTT2
VALUES (4,’2BBBB’);

 

Rerun first SELECT query and check results:

c1          c2                   c1          c2
———– ——————– ———– ——————————
1           1abcdd               1           2abbb
2           1bbbb                NULL        NULL
3           1cccc                3           2cbdb
4           1dddd                NULL        NULL

Last row is still showing NULLs for LeftT2.

The reason for this is Row 4 is LEFT join includes 2 steps: INNER JOIN and compensation

Row 4 is filterred out in INNER JOIN step and added back (with NULLs for right table – LeftT2) in compensation step. Therefore, we saw NULLs for LeftT2 columns

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s