CODEWITHSUNDEEP
phpmysqlsql
Aashan Ghimire
Aashan Ghimire

Reputation: 5

How do I exclude the rows with '0' values but not the rows with NULL values in SQL?

I have a column reference under my table. It includes NULL, 0 and 1 as its values. I want to exclude the rows that contain 0. Also, I want the rows with NULL values to appear. I tried doing reference != 0 but it also excludes the rows with NULL value. How do I prevent that?

Upvotes: 0

Views: 3822

Answers (4)

zambonee
zambonee

Reputation: 1647

NULLS are a tricky beast- you cannot compare a null to 0, so null != 0 always returns unknown, which is treated as a FALSE in your case. Instead, you can specify nulls in your statement: 

... WHERE reference IS NULL OR reference != 0

or you can replace null with another value in the evaluation: 

... WHERE COALESCE(reference, 2) != 0

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Use NULL save comparisons. In MySQL, this is <=> for equality. So, you can do:

where not (reference <=> 0)

In standard SQL, this would be:

where reference is distinct from 0

Upvotes: 0

Nishant
Nishant

Reputation: 222

NULLs in Databases represents a missing value, this means if you do a comparison with null the result is always unknown.

Databases hence follow a Ternary logic or a Three-valued logic 3VL, so a logical expression in SQL results in True, false and Unknown. So when you ask for reference != 0 NULLs are not returned as they are evaluated to UNKNOWN instead of TRUE

To answer your problem you need to include both the checks i.e select reference from tablename where reference is null or reference = 1

Search for Three-Valued Logic in databases or NULL handling in databases for more information

Upvotes: 0

The Law
The Law

Reputation: 334

SELECT reference FROM yourTable WHERE reference = 1 OR reference IS NULL;

That will return only values which you want

Upvotes: 4

Related Questions