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.

so, 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 don'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.

Answer 1

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 ...