SQLite - Difference between IS and = (equals) in WHERE clause. (using JDBC PreparedStatement)

Question

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

---

I am confused about the keyword "IS" in SQLite.

I'm working on a project that requires me to use Java's prepared Statements. I've come across two types of WHERE clauses:

SELECT * FROM table WHERE column = ?

and

SELECT * FROM table WHERE column IS NULL

My question is there a difference a major between the equals sign "=" or the word "IS"? Google searches show that most people use = for value comparison and IS for comparing to null. However I attempted a few SQLite queries of my own.

• "IS" will return results as expected for "column IS NULL" and for "column IS value".
• "=" will return results as expected for "column = value" but not for "column = NULL".

My question is can I use "IS" for both situations without unexpected results? I would like to make one prepared statement for a single query who's constraint on a column may or may not be null. I hope I have been making sense.

To simplify everything I said, can I use the following Java code without unexpected repercussions from using "IS":

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

Thank You

Answer 1

The above answers are getting it wrong. They're saying that '=' must be used in all cases and 'IS' for nulls. That's not what the documentation is saying.

The documentation is saying that the two operators are equivalent, the only difference being in how they evaluate null. NULL on either side of = will evaluate to false. IS will evaluate the truth of the NULL condition.

This NULL = NULL will be false by the design of the operator and NULL is NULL will be true.

There are cases where you may not care if a column has a null value or has a legitimate doesn't match your query. In which case you would use = ... and there are cases where you do care about null in which case you would use 'is' .

Answer 2

In SQL, a comparison between a null value and any other value (including another null) a using a logical operator (eg =, !=, <, etc) will result in a null, which is considered as false for the purposes of a where clause. The reasoning is that a null means "unknown", so the result of any comparison to a null is also "unknown". So you'll get no hit on rows using my_column = null.

SQL provides the special syntax for testing if a column is null, via is null and is not null, which is a special condition to test for a null (or not a null).

 x is null checks whether x is a null value.
 x = null is checking whether x equals NULL, which will never be true