CODEWITHSUNDEEP
mysqlisnull
changed
changed

Reputation: 2143

In MySql whats the difference between != NULL and IS NOT NULL

I get different results when i run select queries with != NULL OR IS NOT NULL .

    /** No results returned.*/
        SELECT *
        FROM PORT_INFO
        WHERE PORT_CODE != NULL;

   /** Results are returned.*/
    SELECT *
    FROM PORT_INFO
    WHERE PORT_CODE IS NOT NULL;

Upvotes: 2

Views: 108

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Any comparison or operation with NULL returns NULL, with the exception of IS NULL.

A NULL boolean is considered to be false.

So, expressions like <> NULL, = NULL, and so on are always false.

This is ANSI standard behavior.

Upvotes: 0

GreyBeardedGeek
GreyBeardedGeek

Reputation: 30088

In SQL, there is no value that is equal to NULL, including NULL itself. To get what you expect, use IS NOT NULL.

Upvotes: 0

Igor
Igor

Reputation: 33993

From the documentation:

Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.

So basically IS NOT NULL will actually do what you intend, while != will do nothing useful (since it too will just return NULL).

Upvotes: 2

Related Questions