47

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.

Alexei Blue
  • 1,762
  • 3
  • 21
  • 36
  • 5
    "does this always happen in SQL?" -- short answer: no. SQL exhibits [three-valued logic (3VL)](http://en.wikipedia.org/wiki/Three-valued_logic). I say "exhibits" very deliberately because the specs for SQL's 3VL are inconsistent and incomplete. You simply have to learn all the edge cases. The best approach IMO is to avoid nulls in SQL. – onedaywhen Nov 07 '11 at 15:25
  • 1
    I know this adds no insight, but should the `OR` be an `AND`? – user123444555621 Jul 22 '14 at 08:58
  • From what I can remember when I was doing this having it as OR is correct i.e. I only wanted STATE 0 rows but I was also getting NULL rows. I wasn't aware of the three-valued logic as @Vash describes in his answer. I should have used STATE NOT IN('1','2') as Michael Durrant pointed out. My SQL's a lot better then it was back then lol. – Alexei Blue Jul 22 '14 at 13:58
  • I feel a bit stupid just remembering this but it just occurred to me that @Pumbaa80 is right, I did mean AND, OR wouldn't make any sense. STATE NOT IN(...) is also correct. – Alexei Blue Jul 23 '14 at 12:48
  • @onedaywhen excellent comment, specially `best approach IMO is to avoid nulls in SQL` due to inconsistencies on SQL specs. This comment deserved to be an answer, would help a lot of folks – Jamil Said Dec 20 '18 at 22:23

5 Answers5

42

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.

Alex
  • 5,240
  • 1
  • 31
  • 38
  • 1
    I see, cheers for the quick answer Vash :) I will definitely keep that in mind in the future. – Alexei Blue Nov 07 '11 at 15:08
  • 1
    I think at stackexchange you will receive answers very fast in general. If you try answering questions yourself you will find out that it is hard to find an unanswered question that you are able to answer and you want to answer. However this was a short answer as well, and now that I am at home I will update it too, to reflect three-valued logic. – Alex Nov 07 '11 at 17:32
  • Best answer by a mile, addresses the real issue here, which is `three-valued logic`. Thanks! – Jamil Said Dec 20 '18 at 22:18
  • Am I the only person out there who thinks that this is fundamentally a wrong way to go about the design of any type of programming language? It makes no sense to me. If I say, 'where [value] != 2' ... null does not equal 2! Whoever decided to use this 'three-valued logic' - it just seems like the wrong decision. – Greg Blass Nov 25 '19 at 22:25
  • @GregBlass I think that calling an unknown value "NULL" causes the main confusion. It is not just about comparing values for equality, but also about whether the data can be compared at all. – Alex Nov 26 '19 at 14:57
  • @Alex - I appreciate you trying to help me understand. I still don't agree with it though. It's like "Give me all the orders that do not have an ad source = 'facebook'". Tons of orders could have no ad source. Their ad source, also, is not facebook. You can easily compare that, and they should be returned in the query. I'll die on this hill lol – Greg Blass Nov 26 '19 at 16:22
  • It is just like SQL works, if you do not want this behavior, you may want to think of making the column not nullable. This of course should not be a general dogma to avoid nullable columns, it just should fit your use case. – Alex Jan 14 '20 at 08:48
9

Yest it's normal, you can maybe put a database settings to fixed that

But you could modify your code and do something like that : 

SELECT * FROM STATUS WHERE STATE != '1' OR STATE != '2' or STATE is null;

Look at this for more info : http://www.w3schools.com/sql/sql_null_values.asp

GregM
  • 2,634
  • 3
  • 22
  • 37
6

multiple or's with where's can quickly become hard to read and uncertain as to results.

I would recommend extra parenthesis plus the use of the IN statement (NOT IN in this case), e.g.

SELECT * FROM STATUS WHERE (STATE NOT IN ('1', '2')) or STATE is null;

Implmentations can vary between database vendor but the above explicit syntax should make sure of the results.

Michael Durrant
  • 93,410
  • 97
  • 333
  • 497
4

use NVL like so: SELECT * FROM STATUS WHERE NVL(STATE,'X') != '1' AND NVL(STATE,'X')!= '2';

Madam Zu Zu
  • 6,437
  • 19
  • 83
  • 129
0

I created script to research Oracle behavior:




create table field_values
(
is_user_deletable VARCHAR2(1 CHAR),
resource_mark VARCHAR2(3 CHAR)
)

insert into field_values values (NULL, NULL);
insert into field_values values ('Y', NULL);
insert into field_values values ('N', NULL);

select * from field_values; -- 3 row

-- 1 row, bad
update field_values set resource_mark = 'D' where is_user_deletable = 'Y'; 
update field_values set resource_mark = 'D' where is_user_deletable <> 'N'; 
update field_values set resource_mark = 'D' where is_user_deletable != 'N'; 
update field_values set resource_mark = 'D' where is_user_deletable not in ('Y');

-- 2 rows, good, but needs more SQL and more comparisons. Does DB optimizes?
update field_values set resource_mark = 'D' where is_user_deletable = 'N' or is_user_deletable is null; 

-- it better to have ('Y' and NULL) or ('N' and NULL), but not 'Y' and 'N' and NULL (avoid quires with https://en.wikipedia.org/wiki/Three-valued_logic)
-- adding new column which is 'Y' or 'N' only into existing table may be very long locking operation on existing table (or running long DML script)

Dzmitry Lahoda
  • 939
  • 1
  • 13
  • 34