Welcome to WebmasterWorld Guest from 54.196.17.193

Forum Moderators: open

Message Too Old, No Replies

SELECT records NOT in another table

Help creating a query

     
5:27 pm on Mar 30, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Jan 14, 2010
posts: 46
votes: 0


I've got two tables. Table A is Fruit, Table B is Vegetables.

Table A
-----------
id|name

1|orange
2|apple
3|grape
4|tomato

Table B
-----------
id|name

5|carrot
6|cucumber
4|tomato

I want to select all the items from table B, that are NOT in table A? What is the best way to accomplish this?
5:32 pm on Mar 30, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5668
votes: 76


You could use a LEFT OUTER JOIN:
SELECT TableB.id,TableB.name
FROM TableB LEFT OUTER JOIN TableA ON TableB.id=TableA.id
WHERE TableA.id IS NULL
5:39 pm on Mar 30, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Jan 14, 2010
posts: 46
votes: 0


Beautiful.
1:29 am on Apr 1, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0


another way is to use NOT EXITS:

SELECT id, name
FROM TableB
WHERE NOT EXITS (SELECT * FROM TableA
WHERE id=TableB.id)
10:06 pm on Apr 1, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Jan 14, 2010
posts: 46
votes: 0


Thanks for the tip. Worked well also.

For anyone who might come across this in the future... please note it is NOT EXISTS. Took me a minute to figure out why it wasn't working...
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members