SQL not displaying null values on a not equals query

nulloraclesql

This is just a question out of curiosity but I am looking at a database and pulling data from a table with a query on one of the columns. The column has four possible values null, 0, 1, 2. When I run the query as:

SELECT * FROM STATUS WHERE STATE != '1' AND STATE != '2';

I get the same results as running:

SELECT * FROM STATUS WHERE STATE = '0';

I.e. rows with a null value in the top command in the queried column seem to be omitted from the results, does this always happen in SQL?

I'm running my commands through Oracle SQL Developer.

Best Answer

In several languages NULL is handled differently: Most people know about two-valued logic where true and false are the only comparable values in boolean expressions (even is false is defined as 0 and true as anything else).

In Standard SQL you have to think about three-valued logic. NULL is not treated as a real value, you could rather call it "unknown". So if the value is unknown it is not clear if in your case state is 0, 1, or anything else. So NULL != 1 results to NULL again.

This concludes that whereever you filter something that may be NULL, you have to treat NULL values by yourself. Note that the syntax is different as well: NULL values can only be compare with x IS NULL instead of x = NULL. See Wikipedia for a truth table showing the results of logic operations.

Related Topic