SQL query excluding something

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

SQL query excluding something

Post by Bill H »

I discovered this by accident and have been unable to verify it in documentation, so perhaps somone here can do so. It seems to work, but...

I have an integer column which may contain values between 1 and 6 and I have been excluding records with a value of 2 with the following SQL statement:

Code: Select all

SELECT * FROM  Table WHERE (Col<2 OR Col>2) AND...
That's fine when it's the only condition, but becomes a bit awkward when it's part of a much (much) longer statement. This seems to do the same thing, but is it valid as part of a larger statement and can I count on it?

Code: Select all

SELECT * FROM  Table WHERE Col-2 AND...
Sorry, using MySQL.
:?:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

SELECT * FROM table WHERE Col NOT '2'

?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

OK, thanks.

Edit @ 4:09PDT The "Col NOT 2" syntax produces a MySQL error. When I change it to "Col-2" the query executes properly.

I cannot find anything in MySQL documentation for NOT or the minus sign in the SELECT syntax, so I'm a bit reluctant to use the "Col-2" syntax despite its convenience. The "Col NOT 2" does not work, with or without the single quotes around the value. (It should not need them since the column type is an integer.)
:?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

hmm.. I could have swore that worked in the past for me.. :?
Post Reply