Edit: talking with a_horse_with_no_name I found that "IS" is a little bit different in SQLite allowing comparisons between NULL and values using "IS": stackoverflow.com/a/9102445/1470058. This clears up a lot of confusion for me. Thanks:
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 =.
SELECT * FROM table WHERE column = ?
SELECT * FROM table WHERE column IS NULL
private static final String queryProjectSql = "SELECT * FROM fields WHERE project IS ?";
// later in a method
sqlStatement = connection.prepareStatement(queryProjectSql);
sqlStatement.setString(1, project); //Project may be a String or null
The "IS" keyword is strictly to be used with NULL value. (eg. IS NULL or IS NOT NULL).
If you are looking for a match you need to use =.
If you are concerned about handling null column values then you should wrap the columns of concern with the isnull() function.
SELECT Col1 , Col2 , isnull(Col3,'') AS Col3 FROM myTable Where col1 = 'somevalue'