SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2000
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Opposite of IN in mysql query?

    Hi,

    I often use something like 'SELECT * FROM table WHERE id IN (1,2,3)', but how do I do the opposite? Let's say I want to select everyone except those with id 1,2 or 3. I've tried with things like NOT IN or NOTIN, but didn't work, and searching the documentation on mysql.com gave nothing, so I hope someone can answer me.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the opposite of IN is NOT IN

    ... WHERE id NOT IN (1,2,3)

    this is documented here: http://dev.mysql.com/doc/refman/5.0/...operators.html

    an equivalent condition is --

    ... WHERE NOT ( id IN (1,2,3) )

    however, do not make the mistake and omit those outer parentheses --

    ... WHERE NOT id IN (1,2,3)

    this is not the same thing!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT * FROM table WHERE id NOT IN (1,2,3) should work ok and does work ok for me

  4. #4
    SitePoint Evangelist
    Join Date
    Oct 2000
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I must have made a mistake then because I did try NOT IN, I'm going to give it another look.

    Thank you both for the answers and thanks r937 for the documentation link, I was unable to find that


Bookmarks

Posting Permissions

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