September 22, 2015 at 4:13 am
create table #t1 (id int)
create table #t2 (id int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t2 values (1)
insert into #t2 values (2)
Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id and b.id is null
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id where b.id is null
September 22, 2015 at 4:28 am
squvi.87 (9/22/2015)
create table #t1 (id int)create table #t2 (id int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t2 values (1)
insert into #t2 values (2)
Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id and b.id is null
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id where b.id is null
It's the way that the predicates work for a LEFT JOIN.
First off, recall that a LEFT JOIN takes all rows from the left table (#t1), and attempts to join them to the right table (#t2). If a match is found on #t2, you get that value; otherwise you will get a NULL value. #t1 has three rows, values 1-3, and #t2 has two rows (1-2).
In the first query, the join condition includes that b.id is null. For the first two values in #t1 (1,2), there is a matching id in #t2... but the entire join predicate is false (a.id does equal b.id, but b.id is NOT null), therefore a NULL is returned for these values. For the #t1.id value of 3, there is no match on a.id=b.id, so this also returns a NULL.
In the second query, #t1.id values 1&2 have a match, so the join condition returns the matching #t2.id. However, the where clause specifies that b.id is null, which it isn't, so these two rows are excluded from the result set. For #t1.id value 3, there is no matching #t2.id value, so the left join specifies to return a NULL. This now satisfies the where clause, so this row is returned in the result set.
Do you understand this?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 22, 2015 at 6:43 am
Wayne's explanation is excellent.
Just remember, similar is not same. Just because a query has similar structures doesn't mean it has the same structures. Those differences really do matter.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2015 at 3:52 pm
Thanks Grant.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply