PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL comparisons

I am confused why here the result is TRUE

select 'Clark' in ('Dave', null, 'Clark');

But here it’s NULL (instead of evaluating to TRUE)

select 'Ava' not in ('Dave', null, 'Clark');

Can somebody please explain?

Tags:

2

Answers


  1. 0

    SELECT 'Clark' IN ('Dave', NULL, 'Clark'); → TRUE

    ‘Clark’ is in the list, so IN returns TRUE, ignoring NULL.

    SELECT 'Ava' NOT IN ('Dave', NULL, 'Clark'); → NULL

    NOT IN checks all values. Since NULL makes Ava <> NULL unknown, the result is NULL.

    To fix it,You should use IS DISTINCT FROM to avoid NULL issues: here is an example:

    SELECT 'Ava' IS DISTINCT FROM ALL (ARRAY['Dave', NULL, 'Clark']);

    This returns TRUE

    Login or Signup to reply.
  2. 0

    In SQL, logical operations are three-valued because of the presence of NULL (missing data). Clark = NULL cannot be defined as TRUE or FLASE because it is not known what it is compared to.

    In the first example, you check whether Clark is present in the list Dave, null and Clark:

    Clark = Dave  -> FALSE
Clark = NULL  -> NULL
Clark = Clark -> TRUE

    Clearly Clark is present in the list, despite the unknown (NULL) value

    In the second example, you check whether Ava is not present (it really doesn’t matter) in the list Dave, null, Clark:

    Ava = Dave  -> FALSE
Ava = NULL  -> NULL
Ava = Clark -> FALSE

    The problem here is, we don’t know what Ava = NULL actually returns. Depending on the data, it can be either TRUE or FLASE. Since there is no way to determine this unambiguously, the final result is NULL (cannot be determined)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search