Choose id FROM clients WHERE type Isn't Null


Choose id FROM clients WHERE NOT type IS NULL

The information that either of the aforementioned will return is going to be the identical.

What's the difference and why would one of these be more suitable?

It appears in my experience that there can be a positive change if this involves performance. Anybody choose to elaborate about this?

There's no difference.

It appears in my experience that there can be a positive change if this involves performance. Anybody choose to elaborate about this?

All major engines (that's MySQL, SQL Server, Oracle and PostgreSQL) will merge these predicates on parsing stage, making identical plans from their store.

Handling of those conditions is much more complex that mere using operators in one or two order.

For example, in Oracle, an Isn't NULL (or NOT IS NULL) condition suggests possible to make use of a catalog, so a question such as this:

Choose  column

FROM    mytable

WHERE   column Isn't NULL

will most most likely be performed by having an index fast full scan, without any additional inspections produced in runtime (because the NULL values just will not allow it to be in to the index, therefore it is no use to check on them).

Even when each record will have to be looked into, an order of inspections is going to be based on the optimizer (and never through the order the predicates and operators come in the WHERE clause).

For example, this is a arrange for an Oracle query:

SQL> EXPLAIN Arrange For


  2  Choose *

  3  FROM   t_test

  4  WHERE  NOT column IS NULL

  5  /


SQL> Choose  *

  2  FROM    TABLE(DBMS_XPLAN.display())

  3  /



Plan hash value: 958699830


 Id   Operation          Title    Rows   Bytes  Cost (%CPU) Time     


     Choose STATEMENT              30   1260      3   () 00:00:01 

*  1   TABLE ACCESS FULL T_TEST     30   1260      3   () 00:00:01 


Predicate Information (recognized by operation id):


   1 - filter("COLUMN" Isn't NULL)

As you can tell, the filter was converted internally into an Isn't NULL (which Oracle together with most commenters appears to think to become a appropriate form)


As Jonathan Leffler stated, these is difference when looking for tuples (instead of single posts).

A tuple composed of mixed NULL and non-NULL values is neither a NULL nor a NOT NULL.

In PostgreSQL (which assists this predicate against tuples), these two expressions:

Choose  (1, NULL) IS NULL

Choose  (1, NULL) Isn't NULL

evaluate to false.

Isn't NULL is really a comparison operator, much like IS NULL or =, >, <, etc.

NOT is really a logical operator that functions around the relaxation from the condition. To help you say NOT type = 5, NOT type IS NULL, as well as NOT type Isn't NULL.

My point here's to indicate that they're two completely different operators, despite the fact that it makes sense exactly the same. Obviously, in boolean logic, there's no distinction between NOT (column IS NULL) and column Isn't NULL, but it is smart to understand the main difference.

For performance, Isn't NULL might help you save a couple of cycles over NOT ... IS NULL since you are utilizing just one operator rather than two operators, but any reasonable optimizer will determine those are the same factor prior to the totally run.