Sqlite Query Equal String Even if Null

Query table transactions by user_key, where user_key could be a valid string or null.

NOTE: user_key = ? won't work correctly for null

My favourite solution is to use IS.

SELECT *
FROM transactions
WHERE user_key IS ?
AND is_active=1

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

This work as well.

SELECT *
FROM transactions
WHERE ((user_key = ?) OR (? IS NULL AND user_key IS NULL))
AND is_active=1

Sample of declaring Room query on Android.

@Query("SELECT * FROM transactions WHERE (user_key IS :userKey) AND is_active = 1")fun fetchAllSync(userKey: String?): List<Transaction>