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:
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?
SELECT * FROM Table WHERE Col-2 AND...

Hi BillH,

The way I read the second select is that you're subtracting 2 from 'Col' (which should return true, as long as Col's value is an int) -- how is that the same as less or greater than?

Vinny

I know it doesn't seem to make sense. What the query actually returns in my experimental test is all rows in which that column (which is an int, tinyint to be precise) contains any value other than 2. Yes indeed -- all the rows where that value is 1, 3, 4, 5, 6 or 7 are returned. So it seems that the minus sign excludes the stated value. It does not alter the data when located in the WHERE clause like this.

I discovered this by mistyping - instead of = one time, and wondered why the SQL statement didn't fail. So I experimented further. If this actually works as it seems to do (and I haven't actually found any cases where it does not) it would be a very convenient syntax. But one is reluctant to use undocumented features, so I'm wondering if anyone knows more about it.

Hi Bill,

Verrrry interesting :)

I googled (sql +where ) and didn't see anything in the first few pages (over 3 million results :eek:

Still and all, according to:
http://www.w3schools.com/sql/sql_where.asp
'-' isn't a legal operator in SQL. Not sure about MS SQL Server -- haven't find their manual as of yet.

So. What I guess I'm saying is:

if it's for self-use, go ahead and use it.
if it's for clients, don't touch it -- unless if it's a client you intend to dump and don't want to hear from again when the next version of the server comes out :)

[still looks like a subtraction to me :) ]
Vinny

Sorry, should have specified. I'm using MySQL. Cant find anything in their docs.