June 17, 2009 at 2:01 pm
I have a table that has multiple rows for any id I want join all ids that have a one code but not another or no code but i want to achieve this without joining to the code table twice(once to find code i am looking for and once to exclude code.
The statement below produces the results i am looking for but i would rather join the #tmp table once to a cte instead of joining twice to #tmp_code. thanks in advance for any help.
create table #tmp(id int identity(1,1),
val varchar(10));
go
create table #tmp_code(id int identity(1,1),
tmp_id int,
code varchar(50));
go
insert into #tmp(val)
values ('one'),('two'),('three'),('four'),('five');
go
insert into #tmp_code(tmp_id,code)
values(1,'good'),(1,null),(1,'na'),(2,null),(3,'good'),(3,null),(4,'na'),(4,null),(5,null),(5,null);
go
select a.val,b.code from #tmp a
left outer join #tmp_code b
on b.tmp_id = a.id and b.code = 'good'
left outer join #tmp_code c
on c.tmp_id = a.id and c.code = 'na'
where c.code is null;
June 17, 2009 at 2:59 pm
Bob,
Your example does not work. Your INSERT INTO....VALUES construct is not correct. Also, I have a feeling your SELECT code in your example is not producing the correct results based off of the description you gave. Your example (after correcting it) returns 3 rows. Based off of your description, I would expect it to return only 1 row for the ID of 3. Can you verify this?
Also, are you seeing performance problems with the code written the way it is? Why do you think you need to use a CTE for this?
Here's your example, corrected to work w/o error:
create table #tmp(id int identity(1,1), val varchar(10))
create table #tmp_code(id int identity(1,1), tmp_id int, code varchar(50))
insert into #tmp(val)
select 'one' union all
select 'two' union all
select 'three' union all
select 'four' union all
select 'five'
insert into #tmp_code(tmp_id,code)
select 1,'good' union all
select 1,null union all
select 1,'na' union all
select 2,null union all
select 3,'good' union all
select 3,null union all
select 4,'na' union all
select 4,null union all
select 5,null union all
select 5,null
select a.val,b.code
from #tmp a
left outer join #tmp_code b
on b.tmp_id = a.id and b.code = 'good'
left outer join #tmp_code c
on c.tmp_id = a.id and c.code = 'na'
where c.code is null
June 17, 2009 at 3:19 pm
Assuming your example is as you intended (it worked for me) I can't see a way of replacing the two joins on #tmp_code with a cte - but you could replace the second outer l join with a not exists sub-query like this.
select a.val,b.code from #tmp a
left outer join #tmp_code b
on b.tmp_id = a.id and b.code = 'good'
WHERE
NOT EXISTS ( SELECT * FROM #tmp_code c
WHERE c.tmp_id = a.id and c.code = 'na' )
Which gets the same results - but on my system has exactly the same execution plan too. :ermm:
June 17, 2009 at 3:24 pm
Sorry about that, I had created the example on a 2008 instance. And this is the result I want, either one code but not another or no code at all.
As for the purpose of this, in the example that would be no real difference but this is just small part of a view. The view is called quite often(about 20-30/min) and it takes on average 1.5 sec to run. I have already made a few changes (adding indexes, reorganizing) which results in 30% improvement but I am still looking for more and this looked like good candidate. On my production system what would be the #tmp_code table has over 500000 rows so I figured limiting a call to that table to once instead of twice might result in a quicker result.
June 17, 2009 at 3:24 pm
I would favor the original query over the NOT EXISTS query. Your execution plan my look the same on this small example, but you'll see different results when you get to real life table sizes. The NOT EXISTS is a correlated sub-query and will not perform as well as the JOIN.
June 17, 2009 at 3:41 pm
John Rowan (6/17/2009)
I would favor the original query over the NOT EXISTS query. Your execution plan my look the same on this small example, but you'll see different results when you get to real life table sizes. The NOT EXISTS is a correlated sub-query and will not perform as well as the JOIN.
I remember reading something which stated that a NOT EXISTS sub-query would out-perform the left null/anti join (not quite sure of the correct terminology) - So I figure its worth checking performance on both. Still I could have remembered wrong.
June 17, 2009 at 3:48 pm
Some of that may depend on what is in the NOT EXISTS() clause. In this example, the SELECT statement in the NOT EXISTS is referencing a table outside the NOT EXISTS which means it has to run the SELECT statement once for each row returned by the outer resultset.
I've seen examples where NOT EXISTS out performs the ANSI joins, but the ANSI join is usually wins. It is also the safer way to go since you know what you're getting as the tables sizes grow and many sub-queries masquerade as good-performers until the tables reach a certain size.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply