Select records in Table A that are NOT in Table B?

Last Post 18 May 2011 08:52 AM by hopeful. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
hopeful
New Member
New Member

--
16 May 2011 04:33 PM
I have two tables A and B in a 1:1 relationship.  Table A has many records, Table B a few, and the tables share a key unique index field "kindex".  That is, any record in B corresponds to a record in A having the same value of kindex, but most records in A have no corresponding record in B.

How do I write a query that selects all records in Table A that do NOT have a corresponding record in Table B?  In other words, "B" is a list of "A" records EXCLUDED from the query.
hopeful
New Member
New Member

--
16 May 2011 04:50 PM
I should add that when I say table B has "only a few" records, I mean only tens of thousands, whereas A has millions. So I want a solution that's very efficient in terms of processing time. It seems to me that there should be a type of join for this, but I couldn't find one - or couldn't recognize it.
russellb
New Member
New Member

--
17 May 2011 04:36 AM
SELECT a.*
FROM TableA a
LEFT JOIN
TableB b
On a.kindex = b.kindex
WHERE b.kindex IS NULL;
hopeful
New Member
New Member

--
18 May 2011 08:52 AM
It works. Thanks!


Acceptable Use Policy
---