Simplest way to compare column against a value that may (or not) be null-CodePudding

I have a prepared SQLite statement...

SELECT * FROM a WHERE col1 = $someValue

But when someValue is null the query returns no rows, so I wrote this...

SELECT *
FROM a
WHERE (
    col1 = $someValue OR
    (
        $someValue IS NULL AND
        col1 IS NULL
    )
)

This query works perfectly regardless of someValue being null or not, but it's very verbose. Is there a simpler or more succinct way of achieving the same?

CodePudding user response：

If you would be using postgres there was is distinct from and I guess they have it because many feel with you:

SELECT * FROM a WHERE col1 is not distinct from $someValue

With sqlite it should be possible to write:

SELECT * FROM a WHERE col1 is $someValue OR col1 = $someValue

That is probably as short as it gets.

CodePudding user response：

From Operators, and Parse-Affecting Attributes:

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.

You can use the operator IS to compare non-null values as well:

SELECT * FROM a WHERE col1 IS $someValue;