If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > Not sure why this sql isn't working (NOT IN)

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2009
Posts: 21
Not sure why this sql isn't working (NOT IN)

select fruit_name from FRUITS where fruit_name not in (select FRUIT_NAME from new_fruits)

say there are two tables

Table:FRUITS

column: fruit_name
apples
bannanas
oranges


table: new_fruits
column: fruit_name
grapes
apples


I want the sql to return bannanas and orange but the sql returns nothing

using sql server 2005
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
two comments

first, sql server problems should really be posted in the sql server forum

ANSI SQL solutions don't always work in every database system

secondly, you've obviously "dumbed down" your situation, beyond the point where it can be solved

the query you posted ~will~ return the results you expect -- bannanas and oranges
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2009
Posts: 21
oh sorry didn't realize NOT IN was exclusive to only sql server? or to a couple of servers

i dumbed it down, since the tables contains thousands of rows(one table has 700k, other has 1000)

both use varchar(16) null for the column.

I don't know why NOT IN doesn't work,but using EXCEPT(T-sql) returns what I want as well.

select distinct fruit_name from FRUITS except
select distinct fruit_name from new_fruits

Last edited by fff398; 02-24-10 at 15:56.
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
Quote:
Originally Posted by fff398 View Post
oh sorry didn't realize NOT IN was exclusive to only sql server?
actually, it is ANSI SQL

however, a great number of problems posted in this thread are actually looking for a dbms-specific solution, not an ANSI SQL one



i don't know why your NOT IN didn't work either, the only thing i can think of is trailing spaces in the data values

but if you're happy with EXCEPT, that's fine

EXCEPT is ANSI SQL too
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Location: At work...
Posts: 84
Watch out for nulls!

If any FRUIT_NAME value is null in the new_fruits table, your query will return no rows at all.

Does this make any difference:
select fruit_name from FRUITS where fruit_name not in
(select FRUIT_NAME from new_fruits where FRUIT_NAME is not null)
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
dang!!! [ slaps forehead ]

i had forgotten about that particular whoopsie

nice one, jarlH

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Sep 2009
Posts: 21
yup nulls in the table got me, and EXCEPT inheritantly ignored them

thanks for the help
Reply With Quote
  #8 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,523
Nulls just got me too!
Thankfully I found this old thread that explained what I was seeing, or I'd have spent hours trying to figure this out.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On