Results 1 to 8 of 8
  1. #1
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    87
    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)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dang!!! [ slaps forehead ]

    i had forgotten about that particular whoopsie

    nice one, jarlH

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Posts
    21
    yup nulls in the table got me, and EXCEPT inheritantly ignored them

    thanks for the help

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •