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

Last Post 18 May 2011 09:52 AM by hopeful. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
hopeful
New Member
New Member

--
16 May 2011 05: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 05: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 05: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 09:52 AM
It works. Thanks!
You are not authorized to post a reply.

Acceptable Use Policy