Comparing two potentially NULL values in SQLite query

Question

I have an SQLite query like:

SELECT max(e), url, branch
FROM (
    SELECT max(T1.entry) e, T1.url, T1.branch
    FROM repo_history T1
    WHERE (
        SELECT active
        FROM repos T2
        WHERE url = T1.url
          AND branch = T1.branch
          AND project = ?1
    )
    GROUP BY T1.url, T1.branch
    UNION
    SELECT null, T3.url, T3.branch
    FROM repos T3
    WHERE active
      AND project = ?1
)
GROUP BY url
ORDER BY e

Note that there are two occurrences of the ?1 parameter. Anyway, in some cases, it can be null (None in Python, as far as I know, becomes NULL in SQLite). This is a problem because I don't understand null handling but basically I don't get anything back.

How do I handle where "project" = ?1 when ?1 is a null? I'd like to avoid having 2 separate queries for it. I looked around but I can only find stuff about IS NULL/IS NOT NULL, which doesn't work for me because I'm not trying to check if a column is null or not null, but rather, I'm trying to check if two nullable values match, be they null or not null.

null always compares false in `=`. You might want `IS` instead. — Shawn, May 19 '19 at 12:03
@Shawn wait, what does `x IS expr` *do*? literally every tutorial I've found only uses `is null` or `is not null` and I've never seen `is expr` before. — SoniEx2, May 19 '19 at 12:08
From [the documentation](https://www.sqlite.org/lang_expr.html): *The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.* — Shawn, May 19 '19 at 12:14
@SoniEx2: The reason you have not found it in SQL tutorials is that this is not "standard" SQL accepted by all DBMSs. For instance, Postgres has [`a IS NOT DISTINCT FROM b`](https://www.postgresql.org/docs/current/functions-comparison.html), instead of the plain `IS` from SQLite. — danuker, May 19 '19 at 20:34
Possible duplicate of [How to: Representing NULL in SQLite queries](https://stackoverflow.com/questions/4600551/how-to-representing-null-in-sqlite-queries) — jpmc26, May 20 '19 at 06:48
@danuker oh, what's the portable way of doing it? `((?1 IS NULL) AND (foo IS NULL)) OR foo = ?1`? (still think there should be a canonical question about this that covers these cases) — SoniEx2, May 20 '19 at 19:05

score 13 · Answer 1 · answered Dec 19 '19 at 18:04

In SQLite you can use the IS operator instead of = for NULL tolarant comparisons. Works also with ? insertions (unlike MikeT meant).

Python example:

>>> c.execute('SELECT * FROM mytable WHERE userid = ? AND recipe = ?', (3, None)).fetchall()
[]

>>> c.execute('SELECT * FROM mytable WHERE userid = ? AND recipe IS ?', (3, None)).fetchall()
[<Row object>, <Row object>]

>>> c.execute('SELECT * FROM mytable WHERE userid = ? AND recipe is ?', (3, 'TestRecipe')).fetchall()
[<Row object>]

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.

For older MySQL / Mariadb versions at least the NULL tolarant comparison operator is <=> and in PostgreSQL its IS NOT DISTINCT FROM .

The PostgreSQL variant is defined in the SQL:2003 standard. For provisional compatibility maybe insert the suitable operator from a Python dict ...

Comparing two potentially NULL values in SQLite query

1 Answers1