2

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

Community
  • 1
  • 1
TachisAlopex
  • 85
  • 2
  • 7
  • See the top answer to this question: http://stackoverflow.com/questions/9822154/standard-sql-boolean-operator-is-vs-equals-operator – Aurand Mar 05 '13 at 20:53

6 Answers6

18

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

nobody
  • 239
  • 2
  • 6
  • i hope this is right, otherwise how would you write a where clause in a prepared statement where the operand maybe be either NULL or not – Hogdotmac Sep 13 '17 at 16:12
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
PSR
  • 39,804
  • 41
  • 111
  • 151
  • 1
    Most useful answer so far. Normal operators cannot operate on a NULL value, so the result is NULL. `IS NULL` and `IS NOT NULL` are outside of that world a little bit, so you can check for NULL. Sqlite likes to be more flexible in its syntax, so that is why `=`, `==`, and `IS` all work for matching non-NULL values, but that's not standard. – Sonicsmooth Oct 15 '20 at 15:25
1

in SQL language "IS" is used only when comparing with NULL (IS NULL or IS NOT NULL) since x = NULL will always fail (NULL is not equal to anything)

Z .
  • 12,657
  • 1
  • 31
  • 56
  • 1
    IS is also used with IN, e.g.: WHERE id IS IN (1, 2, 3, 4) – Chris Cooper Mar 05 '13 at 20:57
  • @ChrisCooper: which DBMS supports that? That is certainly not allowed in Oracle, Postgres, DB2, MySQL and SQL Server (and is definitely non-standard). –  Mar 05 '13 at 21:17
1

I am not sure if you can use "Is" instead of "=" except for null but I can say you that you can not compare your column with null using "=" sign. you should use "Is null" or "Is not null". The reason is in SQL one null is not equals to another null. that means if you write something like null = null it will return you false. So think of a case where you have column which does not contain anything that means in database it is null and you are trying to compare this null with another null using "=" which will never going to retun you true.

Refer to Codd's Rule 3: Systematic treatment of null values. It says one null is not equal to another null in relational database.

I am sharing this from wiki : http://en.wikipedia.org/wiki/Null_(SQL)

For people who aren't database experts, a good way to remember what null means is to remember that in terms of information, "lack of a value" is not the same thing as "a value of zero"; similarly, "lack of an answer" is not the same thing as "an answer of no". For example, consider the question "How many books does Juan own?" The answer may be "zero" (we know that he owns none) or "null" (we do not know how many he owns, or doesn't own). In a database table, the field reporting this answer would start out with a value of null, and it would not be updated with "zero" until we have ascertained that Juan owns no books. Similarly, when the question is, "Does Juanita own a car?", the answer "we don't know" is not the same thing as "no". The former yields a database entry of "null"; only the latter yields a database entry of "no".

Hope this will help !!

GKP
  • 1,057
  • 1
  • 11
  • 24
0

If a column value is null, the = operator will never return true when comparing with null, so NULL != NULL, hence the need to test with IS NULL.

Chris Cooper
  • 4,982
  • 1
  • 17
  • 27
0

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'
  • Okay. Just to make sure I have this right. So "IS" is strictly to be used with NULL. So even though "_column IS value_" works, I should not use it and restructure the java code to run two different queries? – TachisAlopex Mar 05 '13 at 21:04
  • @TachisAlopex: `IS` isn't valid on it's own. The operators are `IS NULL` or `IS NOT NULL` (similar to `group by` which is an operator that consists of two keywords) –  Mar 05 '13 at 21:17
  • 1
    @a_horse_with_no_name: Sorry If I'm not making sense. I mean: `SELECT * FROM projects WHERE id IS 1` returns the record where id = 1, but is using the IS keyword. It functions, but if that is not what I suppose to do, I have to create multiple sql statements – TachisAlopex Mar 05 '13 at 21:21
  • @TachisAlopex: `where id is 1` is invalid SQL (at least in Oracle, Postgres, SQL Server and DB2). Which DBMS are you using? –  Mar 05 '13 at 21:42
  • @a_horse_with_no_name Really? that may be the reason this is works. I'm using SQLite. I just found this: http://stackoverflow.com/a/9102445/1470058. I'm going to edit my original question now. – TachisAlopex Mar 05 '13 at 21:48