TachisAlopex TachisAlopex - 1 year ago 89
Java Question

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

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



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 Source

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.

   , Col2
   , isnull(Col3,'') AS Col3
FROM myTable
Where col1 = 'somevalue'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download