An inverse JOIN query???

[groston]

I have two tables:

Table1: columns: tblID (unique, pk), data1, data2, etc
Table2: columns: tblID, data1, data2

Both tables have about 6000 rows.

What I want to do is find all rows in Table1 for which there is no related entry in Table2.

The dumb approach:
Select * from Table1, Table2 Where Table1.ID<>Table2.ID
basically yields an outer join with a bazillion records.

The programming approach:
for each record in Table1
Select * from table2 where table2.ID<>record.ID
if recordcount=0 then ...
next
would work, but this requires thousands of calls to the database.

There must be a better way. Would you please tell me how to do this.

Thanks!


----

Gerry Roston
gerry@pairofdocs.net

 

[nigelrivett]

select t1.*
from Table1 t1
left outer join Table2 t2
on t2.tblID = t1.tblID
where t2.tblID is null

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mwolf00]

nigelrivett

Love the quote &quot;Cursors are useful if you don't know sql.&quot;

I was taught SQL be someone who said, &quot;Cursors are the work of the devil&quot; repeatedly in a redundant fashion...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[groston]

Thank you both for your responses.

I actually went off and found a local, knowledgable person. The method he provided, which worked is this:

Select *
From Table1
Where Not Exists (
Select *
From Table2
Where Table1.ID=Table2.ID)

----

Gerry Roston
gerry@pairofdocs.net

 

[nigelrivett]

That'll do the same as mine but might be less efficient.
Another option

select *
from Table1
where id not in (select id from Table2)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JohnDTampaBay]

I believe the EXISTS may be more efficient because it stops the subquery execution as soon as it resolves to TRUE.

If table2 has 1 million rows for only two distinct IDs, nigel's subquery will resolve to 1 million IDs, which then have to searched for the desired ID. The EXISTS subquery will resolve to TRUE and stop at the first row with the desired ID.

I could be wrong though. Opinions?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.