SELECT Help

Question

SELECT Help

rs

SSCrazy

Points: 2819
More actions
July 13, 2005 at 4:10 pm

#89954

I have two tables Table1 and Table2, foolowing are the descriptions
Table1
-----
Col1 CHAR(8),
Col2 CHAR(10),
Col3 CHAR(50)
Table2
-----
Col1 CHAR(8),
Col2 CHAR(10),
Col3 CHAR(50)
I got 1000 rows in Table1 and 758 rows in Table2
How can i query to get the records from table1 those are not exist in table2?
Any help would be appriciated.
Thanks,
RS.

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select T1.* from dbo.Table1 T1 where not exists (Select * from dbo.Table2 T2 where T2.colid = T1.Colid /*all the other conditions you need and T2. = T1...*/)

Ray M SSC-Insane Points: 21093 More actions · Answer 2

More than 1 way to skin a cat.

Select table1.*

from Table1

left join Table2 on table1.colid = table2.colid

where table2.colid is NULL

Performance was not a requirement.

ND-215452 SSCrazy Points: 2017 More actions · Answer 3

whic one is better?

personally, I still like Remi's query. It's easier to understand.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Really depends on the indexes... I like the exists better but the left join does the job really well .

ND-215452 SSCrazy Points: 2017 More actions · Answer 5

what do you mean by depends on indexes? can u give example?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Code both, check the execution plan. I don't have a global answer that works for every case. And as always I think I can safely assume that it depends (if you have other where conditions) .