February 4, 2021 at 9:28 am
Hello
Can you tell me please what really happens in the following situations:
1) FROM TABLE1 JOIN TABLE2 ON TABLE1.COL1=TABLE2.COL1 AND TABLE1.COL2='Y'
2) FROM TABLE1 JOIN TABLE2 ON TABLE1.COL1=TABLE2.COL1 AND TABLE2.COL2='Y'
I am asking how the AND statement is processed.
Thanks!
February 4, 2021 at 2:48 pm
The comparison to a constant acts like a where clause -- since they are inner joins, only rows where that condition is true will be included.
February 4, 2021 at 4:05 pm
Thanks but unfortunately I do not get results consistent with what you are saying.
The results I get contain Col2 as Y and N and NULL.
It does not act as a WHERE clause.
Any idea?
February 4, 2021 at 5:51 pm
You'll have to include more details. What is "n and null"?
The ON clause is processed similar to how a WHERE clause, but it is done first. The joins and on clause are processed first, then the WHERE clause. Then GROUP BY, etc.
It isn't exactly like a WHERE clause because the ON is part of the FROM clause, which is done first. Therefore, when you have multiple tables, this pre filters what becomes available later, and affects the WHERE clause.
February 4, 2021 at 9:06 pm
You'll have to include more details. What is "n and null"?
The ON clause is processed similar to how a WHERE clause, but it is done first. The joins and on clause are processed first, then the WHERE clause. Then GROUP BY, etc.
It isn't exactly like a WHERE clause because the ON is part of the FROM clause, which is done first. Therefore, when you have multiple tables, this pre filters what becomes available later, and affects the WHERE clause.
That really depends on how SQL Server generates the execution plan. When used with INNER JOIN - the constant filter will often be pushed to after the join operation and you end up with the exact same execution plan regardless of where that predicate is placed.
These 2 statements are NOT equivalent...one is filtering based on values in TABLE1.COL2 and the other is filtering based on values in TABLE2.COL2.
The most important differences are when the join is an outer join (which I am assuming is the case here).
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.COL1=TABLE2.COL1 AND TABLE1.COL2='Y'
This statement will match rows from TABLE2 on the key (COL1) - only for those rows in TABLE1 where COL2 = 'Y'. For rows in TABLE1 where there is not a matching row on COL1 the values from TABLE2 will be NULL, and for those rows in TABLE1 where COL2 is not equal to 'Y' the values from TABLE2 will also be NULL.
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.COL1=TABLE2.COL1 AND TABLE2.COL2='Y'
For this statement - the same relationship exists for the key (COL1) so we also will see NULL values where TABLE1 does not have a matching row in TABLE2 on COL1 - but the difference is that it will also return null values in TABLE2 for all matching rows on COL1 where the value in TABLE2.COL2 is not equal to 'Y'.
Declare @table1 table (col1 int, col2 char(1));
Declare @table2 table (col1 int, col2 char(1));
Insert Into @table1 (col1, col2) Values (1, 'Y'), (1, 'N');
Insert Into @table2 (col1, col2) Values (1, 'Y'), (1, 'N');
Select * From @table1 t1 Left Join @table2 t2 On t2.col1 = t1.col1 And t1.col2 = 'Y';
Select * From @table1 t1 Left Join @table2 t2 On t2.col1 = t1.col1 And t2.col2 = 'Y';
For the above example - the first statement returns 3 rows and the second statement returns 2 rows.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply