Mutually Exclusive Query

Question

Post reply

Mutually Exclusive Query

lbrigham

SSCommitted

Points: 1567
More actions
January 5, 2010 at 7:42 am

#143868

First post, be kind. 🙂
I'm looking for a better way of doing the below as what I'm doing now results in a table scan and I know there's a better way since the rows being referenced in where clause are indexed.
Basically, I have 2 tables (TABLE1 and TABLE2 for simplicity). Both tables have 2 key fields (KEY1 and KEY2) along with the other accompanying columns. KEY1 and KEY2 are char(15). I want a result set from TABLE1 where KEY1 and KEY2 are not in TABLE2. The backwards/inefficient way I'm accomplishing this now is the following:
SELECT KEY1,KEY2,OTHERCOL1,OTHERCOL2,OTHERCOL3 FROM TABLE1
WHERE RTRIM(KEY1)+RTRIM(KEY2) NOT IN (SELECT RTRIM(KEY1)+RTRIM(KEY2) FROM TABLE2)
Not only does this table scan TABLE2, but also table scans TABLE1 because of RTRIM(KEY1)+RTRIM(KEY2).

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 1

SELECT KEY1,KEY2,OTHERCOL1,OTHERCOL2,OTHERCOL3 FROM TABLE1

WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE2.KEY1=TABLE1.KEY1 AND TABLE2.KEY2=TABLE1.KEY1)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

lbrigham (1/5/2010)
First post, be kind. 🙂
I'm looking for a better way of doing the below as what I'm doing now results in a table scan and I know there's a better way since the rows being referenced in where clause are indexed.
Basically, I have 2 tables (TABLE1 and TABLE2 for simplicity). Both tables have 2 key fields (KEY1 and KEY2) along with the other accompanying columns. KEY1 and KEY2 are char(15). I want a result set from TABLE1 where KEY1 and KEY2 are not in TABLE2. The backwards/inefficient way I'm accomplishing this now is the following:
SELECT KEY1,KEY2,OTHERCOL1,OTHERCOL2,OTHERCOL3 FROM TABLE1
WHERE RTRIM(KEY1)+RTRIM(KEY2) NOT IN (SELECT RTRIM(KEY1)+RTRIM(KEY2) FROM TABLE2)
Not only does this table scan TABLE2, but also table scans TABLE1 because of RTRIM(KEY1)+RTRIM(KEY2).

select

t1.* -- should list the columns you actually need/want

from

dbo.table1 t1

left outer join dbo.table2 t2

on (t1.KEY1 = t2.KEY1

and t1.KEY2 = t2.KEY2)

where

t2.KEY1 is null;

Does this help?

lbrigham SSCommitted Points: 1567 More actions · Answer 3

Lynn Pettis (1/5/2010)
lbrigham (1/5/2010)
First post, be kind. 🙂
I'm looking for a better way of doing the below as what I'm doing now results in a table scan and I know there's a better way since the rows being referenced in where clause are indexed.
Basically, I have 2 tables (TABLE1 and TABLE2 for simplicity). Both tables have 2 key fields (KEY1 and KEY2) along with the other accompanying columns. KEY1 and KEY2 are char(15). I want a result set from TABLE1 where KEY1 and KEY2 are not in TABLE2. The backwards/inefficient way I'm accomplishing this now is the following:
SELECT KEY1,KEY2,OTHERCOL1,OTHERCOL2,OTHERCOL3 FROM TABLE1
WHERE RTRIM(KEY1)+RTRIM(KEY2) NOT IN (SELECT RTRIM(KEY1)+RTRIM(KEY2) FROM TABLE2)
Not only does this table scan TABLE2, but also table scans TABLE1 because of RTRIM(KEY1)+RTRIM(KEY2).
select
t1.* -- should list the columns you actually need/want
from
dbo.table1 t1
left outer join dbo.table2 t2
on (t1.KEY1 = t2.KEY1
and t1.KEY2 = t2.KEY2)
where
t2.KEY1 is null;
Does this help?

EDIT ~ Yes, thanks! 🙂

lbrigham SSCommitted Points: 1567 More actions · Answer 4

Mark-101232 (1/5/2010)
SELECT KEY1,KEY2,OTHERCOL1,OTHERCOL2,OTHERCOL3 FROM TABLE1
WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE2.KEY1=TABLE1.KEY1 AND TABLE2.KEY2=TABLE1.KEY1)

Still have table scans on TABLE1 and TABLE2. Processing time unchanged.

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 5

lbrigham (1/5/2010)
Mark-101232 (1/5/2010)
SELECT KEY1,KEY2,OTHERCOL1,OTHERCOL2,OTHERCOL3 FROM TABLE1
WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE2.KEY1=TABLE1.KEY1 AND TABLE2.KEY2=TABLE1.KEY1)
Still have table scans on TABLE1 and TABLE2. Processing time unchanged.

Apologies, should have been this

SELECT KEY1,KEY2,OTHERCOL1,OTHERCOL2,OTHERCOL3 FROM TABLE1

WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE2.KEY1=TABLE1.KEY1 AND TABLE2.KEY2=TABLE1.KEY2)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537