Search code examples
sqliteoperatorsoperator-precedenceor-operator

SQLite doesn't treat || like OR

I'm self-answering in the name of posterity. The question was why this SQLite query doesn't do what I expected:

SELECT is_current FROM sp_table WHERE is_current != 1 || is_current IS NULL;

It only selects rows where is_current is NULL! Bah.

Solution

  • The problem is in SQLite the || operator is not a logical or! It's a string concatenation operator.

    OR is the correct operator.

    Also, || has higher precedence than all other operators in SQLite so my expressions weren't even being evaluated in the order I thought.

    The correct expression is:

    SELECT is_current FROM sp_table WHERE is_current != 1 OR is_current IS NULL;

    Finally, it's worth noting that you can't replace that expression with: SELECT is_current FROM sp_table WHERE is_current != 1 because in SQLite NULL != 1 evaluates false. You must explicitly check is is_current IS NULL.

    SQLite reference