Sql Server: Does adding the condition in WHERE clause or JOIN clause matter?
- Posted by Sqltimes
- On December 14, 2013
- 0 Comments
Does it matter where you add a condition? WHERE clause or JOIN clause. How does it change in “INNER JOIN” or “OUTER JOIN”.
Let’s take an example:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
CREATE TABLE dbo.LeftTable (ID INT)CREATE TABLE dbo.RightTable (ID INT)GOINSERT INTO dbo.LeftTable (ID) VALUES (1)INSERT INTO dbo.LeftTable (ID) VALUES (2)INSERT INTO dbo.LeftTable (ID) VALUES (3)INSERT INTO dbo.LeftTable (ID) VALUES (4)INSERT INTO dbo.LeftTable (ID) VALUES (5)INSERT INTO dbo.LeftTable (ID) VALUES (6)INSERT INTO dbo.LeftTable (ID) VALUES (7)INSERT INTO dbo.RightTable (ID) VALUES (8)INSERT INTO dbo.RightTable (ID) VALUES (2)INSERT INTO dbo.RightTable (ID) VALUES (3)INSERT INTO dbo.RightTable (ID) VALUES (9)INSERT INTO dbo.RightTable (ID) VALUES (5)INSERT INTO dbo.RightTable (ID) VALUES (6)GO |
Condition Comparison Figure 1
Now, let’s see how the results differ based on where you put the condition clause.
For INNER JOIN, the end result does not change much. This is because of the order in which Sql Server processes the query and filters the records at every step.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECT L.ID -- Left table , R.ID -- Right tableFROM dbo.LeftTable AS LINNER JOIN dbo.RightTable AS R on L.ID = R.IDWHERE L.ID = 2-- compare with below querySELECT L.ID -- Left table , R.ID -- Right tableFROM dbo.LeftTable AS LINNER JOIN dbo.RightTable AS R ON L.ID = R.ID AND L.ID = 2 |
Condition Compare Figure 2
For OUTER JOIN, the results changes depending on where you put the condition.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT L.ID -- Left table , R.ID -- Right tableFROM dbo.LeftTable AS LLEFT OUTER JOIN dbo.RightTable AS R on L.ID = R.IDWHERE R.Id IS NULL-- Compare the result with query belowSELECT L.ID -- Left table , R.ID -- Right tableFROM dbo.LeftTable AS LLEFT OUTER JOIN dbo.RightTable AS R on L.ID = R.ID AND R.Id IS NULL |
Condition Compare Figure 2
Again, the result is different because of the order in which each section of the query is executed. What is more important is ‘what are you trying to do?’.
This query says that you are looking for records in ‘LEFT’ table, that do not exist (or no matching record) in ‘RIGHT’ table. Hence the condition “WHERE R.Id IS NULL” is added in the WHERE clause. An intermediary virtual table is generated when ‘LEFT’ and ‘RIGHT’ tables are joined in a “LEFT OUTER JOIN” on condition “ID = ID”. This result has all the records from ‘LEFT’ table. And where ever there is a match with RIGHT table, the record is also displayed. But the important point is that all the records from LEFT are displayed. Now when you apply the condition in WHERE clause “WHERE R.Id IS NULL”, it filters the above intermediary virtual table to just those records with RIGHT column values are NULL. Hence a smaller subset.
|
1
2
3
4
5
6
|
SELECT L.ID -- Left table , R.ID -- Right tableFROM dbo.LeftTable AS LLEFT OUTER JOIN dbo.RightTable AS R on L.ID = R.ID AND R.Id IS NULL |
Where as this query is different. It says, I want all the records between LEFT and RIGHT table joined in a LEFT OUTER JOIN based on two conditions.
- ID matches “ID = ID”
- AND RIGHT ID is NULL
This does not make any business case sense, does it? It other words we are saying, we want all the matching records while ID on RIGHT must be NULL — all at the same time. So you just get all records from LEFT table with corrpsponding records from RIGHT table listed as NULL. Even when there are matching records (example: records 2, 3, etc), you do not see them because their ID is not NULL. So its self-conflicting, so all you get is just records from LEFT table.
|
1
2
3
4
5
6
|
SELECT L.ID -- Left table , R.ID -- Right tableFROM dbo.LeftTable AS LLEFT OUTER JOIN dbo.RightTable AS R on L.ID = R.IDWHERE R.Id IS NULL |
Sometime soon, I’ll add apost on the order in which Sql Server processes each section of a query. That would help analyse these situations.
Hope this helps,
_Sqltimes
0 Comments