What is the difference between NOT(x > y) and (x < y) in an SQL query?

Question

What is the difference between the following query:

SELECT * FROM employee  WHERE NOT(start_date > '01-JAN-1970');

and this query:

SELECT * FROM employee  WHERE start_date < '01-JAN-1970';

Is there any difference, and if so, how is NOT(x > y) used differently from (x < y). Can anyone provide an example?

Thanks.

Answer 1

Other than the obvious not(start_date > '01-JAN-1970') would include Jan 1, 1970 whereas start_date < '01-JAN-1970' would exclude it, I would think that these statements are identical.

Answer 2

not start_date > '01-JAN-1970' implies the start date can be equal to '01-JAN-1970' start_date < '01-JAN-1970' means employees with '01-JAN-1970' for a start date is not selected

Answer 3

NOT() is simply a negation of whatever is inside the parentheses. In your example:

SELECT * FROM employee WHERE not(start_date > '01-JAN-1970');

This would match employees whose start date is before 1970. Yes, you can reverse the <> bracket to negate it in this case, but in a more complex case or with other operators NOT is the only way to flip it.

Answer 4

The non-NOT equivalent of

SELECT * FROM employee WHERE not(start_date > '01-JAN-1970');

is

SELECT * FROM employee WHERE start_date <= '01-JAN-1970';

not

SELECT * FROM employee WHERE start_date < '01-JAN-1970';

since that would miss the case where start_date = '01-JAN-1970'

Answer 5

In MySQL and PostgreSQL

SELECT * FROM employee WHERE not(start_date > '01-JAN-1970')

will not use an INDEX on start_date if any, their optimizers are not smart enough.

Otherwise, if you correct the condition not be not strict in exaсtly one of cases (either not(start_date >= '01-JAN-1970') or start_date <= '01-JAN-1970'), the queries are equal.

Answer 6

You're forgetting about rows where start_date == '01-JAN-1970' ;-)

SELECT * FROM employee WHERE start_date <= '01-JAN-1970';

Answer 7

In your example the two clauses are pretty much equivalent - but if you want them to return exactly the same results then you should be using < and >= (or <= and >) rather than both < and >.

NOT becomes much more useful when you have set-based clauses. For example:

SELECT * FROM my_table
WHERE my_column NOT IN (273, 430, 9567, 8, 433, 765, 6252, 13)

SELECT * FROM my_table
WHERE my_column NOT IN (SELECT another_column FROM another_table)

Without using NOT these queries would become at best cumbersome, or at worst impossible.

Answer 8

Disregarding the anything SQL-specific here, but from a logic point of view, the negation of < is >= (likewise, the negation of > is <=), so (x > y) and !(x < y) are not equivalent.

This is plain to see when you use a simple example like "What is not less than Y?". The answer being "Y and everything above it";

!(x < y) == (x >= y)

Answer 9

I suspect (but haven't verified) that the NOT variant will also pick up rows where "start_date" is NULL -- which is probably why MySQL doesn't use an index in that case.

EDIT: After reading Date/Darwen's Guide to the SQL Standard, this answer is incorrect. Null introduces a third state into comparison operators (see pp 240, 241 or the fourth edition).

However, I'm going to leave this answer, because it's quite possible that whomever wrote that expression had the same thought that I did, and was trying to capture null values. Or its possible that the particular RDBMS doesn't follow the standard.