NOT Logical Operator 1

[torturedmind]

Hi all,

Which is the proper way of using the NOT logical operator? Is it

SELECT * FROM anytable WHERE [b]NOT[/b] anyfield IS NULL

or

SELECT * FROM anytable WHERE anyfield IS [b]NOT[/b] NULL

TIA

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[johnherman]

The latter is the proper syntax for NOT NULL, NOT EXISTS, NOT TRUE, etc.

====================================
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[torturedmind]

Thanks John. I reckon the first one is also acceptable since it will negate any expression, logically speaking. Anyway, I've been using the second example as my standard in coding. But my fellow system devs mostly use the first example which I don't know if will have an impact on their apps in the future.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[ggriffit]

both should give the same result, I'd be tempted to run an explain plan etc to see if there is a difference in how the database uses them or whether it resolves to the same query. It may also be worth looking at the EXISTS clause too - see

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[ChrisHunt]

I think, strictly speaking, the first one would be the "proper way of using the NOT logical operator"; because the "NOT" in "IS NOT NULL" isn't being used as a logical operator - it's a piece of SQL syntax in its own right. To my mind, you can only describe NOT as a logical operator if it's being used in the form "NOT <boolean expression>".

Having said that, the two ways of writing that clause will have the same effect, and I wouldn't expect there to be any particular efficiency benefit to one over the other. However, I find the "IS NOT NULL" form much easier to read and parse - as SQL should read like normal (if highly structured) English where possible.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd