1

I have table Party with columns party_Id, party_type

When I use the below queries, I get two different types of result.

SELECT * FROM party WHERE party_type = null

Returns nothing

SELECT * FROM party WHERE party_type IS NULL

Returns all possible rows

Need a query which should accept both null and values.

  • I don't understand your last question "Need a query which should accept both null and values", can you add some sample data for party_type and the expected outcome? – Lennart Jul 19 '17 at 7:58
4

As per SQL standard, comparisons (and boolean operations) use 3-Value logic (true, false, unknown), with unknown being represented by NULL. The following expression returns unknown (represented by NULL):

party_type = null

Whereas

party_type IS NULL

will return a pure boolean (true, false).

You probably want a "NULL-safe comparison predicate":

party_type IS NOT DISTINCT FROM null

See Comparison Predicates

4

The query:

SELECT * FROM party WHERE [predicate]

return all rows where [predicate] evaluates to TRUE.

The truth table for equals with domain FALSE, TRUE , NULL

=     |    FALSE  |  TRUE  |  NULL
----------------------------------
FALSE |    TRUE   |  FALSE |  NULL
TRUE  |    FALSE  |  TRUE  |  NULL
NULL  |    NULL   |  NULL  |  NULL

NULL in this context can be thought of as UNKNOWN, that is if we compare something with UNKNOWN the result is UNKNOWN (including if we compare UNKNOWN with UNKNOWN).

Your first predicate:

party_type = null

therefore always evaluate to NULL and as a result, no rows are returned from the query.

IS NULL is a special predicate that can be used to test if something is null. It evaluates according to:

IS NULL FALSE => FALSE
IS NULL TRUE  => FALSE
IS NULL NULL  => TRUE

Your second predicate, therefore, evaluates to TRUE when party_type IS NULL and those rows are returned from the query.

1

NULL means NO VALUE

If you're looking for NULL fields you must use:

WHERE SomeField IS NULL

In case you need empty (strings) or null fields you should check both conditions:

WHERE SomeField = '' OR SomeField IS NULL;

Or you can force the result when field is null:

WHERE COALESCE(SomeField, '') = ''

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.