23

I am building some prepared statements that use parametrized values. As an example:

SELECT * FROM "Foo" WHERE "Bar"=@param

Sometimes @param might be NULL. In such cases, I want the query to return records where Bar is NULL, but the above query will not do that. I have learned that I can use the IS operator for this. In other words:

SELECT * FROM "Foo" WHERE "Bar" IS @param

Aside from the differing treatment of NULL, are there any other ways in which the above two statements will behave differently? What if @param is not NULL, but is instead, let's say, 5? Is using the IS operator in that case a safe (and sane) thing to do? Is there some other approach I should be taking?

Dan Moulding
  • 211,373
  • 23
  • 97
  • 98
  • 8
    `NULL` is an unknown or unspecified value, so nothing can ever **equal** `NULL`. – JNK Mar 24 '11 at 18:32

5 Answers5

15

You want records from Foo where Bar = @param, or if @param is null, where Bar is null. Some of the proposed solutions will give you null records with nonnull @param, which does not sound like your requirement. 

Select * from Foo where (@param is null and Bar is null) or (Bar = @param)

This doesn't say whether this is Oracle or SQL Server or another RDBMS, because they each implement slightly different helper functions. SQL's ISNULL(first, second) like NVL(first, second). I like SQL Server's COALESCE() for the general applicability.

The IS comparison is only for null comparisons.

If you are using SQL Server and if you really need a different 3VL logic truth table to solve your problem (that is, if you have a specific need for "NULL=NULL" to be "true" at some point in time, and also recognize that this is deprecated and barring your reasons, not a good idea in general), within your code block you can use the directive

SET ANSI_NULLS OFF

Here's the BOL on it: http://msdn.microsoft.com/en-us/library/ms188048.aspx

user662852
  • 380
  • 1
  • 9
  • 1
    I am using SQLite, and it allows me to use `IS` to compare with non-null values. Can you cite a source that says that in standard SQL the "IS" operator is only for null comparisons? – Dan Moulding Mar 24 '11 at 20:13
  • Actually, I can: in the SQL-92 standard (free on the web), IS is a boolean operator. Both sides are be boolean, by ANSI standard. Null is a boolean value in 3VL logic. Search this document for "8.12 ": http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt, – user662852 Mar 24 '11 at 20:21
  • And, I should point out, any RDBMS vendor is free to implement the standard or add additional elements however they like. So if SQLLite wants to provide a syntactic sugar, that's their business. It's not like SQL Server or Oracle get the full standard correct themselves. – user662852 Mar 24 '11 at 20:25
  • 1
    Thank you for the source. So, it sounds like there is a quite a big difference between the two operators in **standard** SQL. In **SQLite** SQL (which I am using) it sounds like the only difference is the treatment of NULL. Thank you for your answer! – Dan Moulding Mar 25 '11 at 14:56
  • Yeah, you've solved both the specific issue and defeated my ability to locate the ANSI SQL standard - you've got my upvote as well. :) – Dan J Mar 25 '11 at 17:06
7

You may be thinking about this incorrectly. If you're talking about SQL Server, for example (since that's what I have to hand), your second example will result in a syntax error. The value on the right-hand side of IS cannot be 5.

To explain, consider MSDN's explanation of these two operators in T-SQL (note that asking about "SQL" and about "SQL Server" are not necessarily the same).

Equals (=) operator

IS NULL operator

Notice something important, there. There is no such thing as the "IS" operator in T-SQL. There is specifically the <expression> IS [NOT] NULL operator, which compares a single expression to NULL.

That's not the same thing as the = operator, which compares two expressions to each other, and has certain behavior when one or both of the expressions happens to be NULL!

Dan J
  • 16,319
  • 7
  • 50
  • 82
  • 1
    I am using SQLite and it let's me put `5` on the right hand side of `IS` and behaves as I would intuitively expect. – Dan Moulding Mar 24 '11 at 20:09
  • Can you cite a source that says that, in standard SQL, `5` can't be put on the right hand side of `IS`? Or are you saying that `IS` isn't even standard SQL? – Dan Moulding Mar 24 '11 at 20:17
  • [Source](http://stackoverflow.com/questions/511361/how-do-i-use-on-delete-cascade-in-mysql) for standard `IS NULL/IS NOT NULL` predicate (as opposed to `IS` operator). I have yet to find reference to `IS` as a standard SQL operator, though the SQLite documentation implies it *is* a standard *reserved word*. – Dan J Mar 24 '11 at 20:59
6

Edit: (Update from OP: This doesn't do what I If @param is 5, then I want to see only records where Bar is 5. I want to see records where Bar is NULL if, and only if, @param is NULL. I apologize if my question didn't make that clear.)

In that case, I think you should try something like this:

SELECT * FROM Foo WHERE Bar=@param OR (Bar IS NULL AND @param IS NULL)

Previous post:

Why not simply use OR ?

SELECT * FROM "Foo" WHERE "Bar"=@param OR "Bar" IS NULL

In SQL Server, you can use ISNULL:

SELECT * FROM "Foo" WHERE ISNULL("Bar",@param)=@param
Hari Menon
  • 33,649
  • 14
  • 85
  • 108
  • 2
    Not to be a contrarian, but, while it does provide alternatives, this doesn't actually answer the OP's question, i.e. *what is the difference between the IS and = operators*... – Dan J Mar 24 '11 at 19:00
  • 2
    "In such cases, I want the query to return records where Bar is NULL, but the above query will not do that." .. "Is using the IS operator in that case a safe (and sane) thing to do? Is there some other approach I should be taking?" I believe the OP is primarily interested in a safe way to achieve this. I really don't know the difference between those except that IS is meant for NULL and I think that that was the secondary question. – Hari Menon Mar 24 '11 at 19:04
  • 1
    I do not think this is what the OP wanted; this always returns rows where `Bar IS NULL`, while the question says "Sometimes `@param` might be NULL. In such cases, I want the query to return records where `Bar is NULL`". Not the same thing. – Dour High Arch Mar 24 '11 at 19:55
  • This doesn't do what I want. If `@param` is 5, then I want to see only records where `Bar` is 5. I want to see records where `Bar` is `NULL` if, and only if, `@param` is `NULL`. I apologize if my question didn't make that clear. – Dan Moulding Mar 24 '11 at 20:00
3

I don't know what version of SQL you are using but IS makes no sense in the context you just described. I get a syntax error if I try to use it the way you described. Why would you want to use it over = anyway? This is the common usage and the one software maintainers woudl expect to find.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • I want to use it over `=` precisely because I need the query to return results where `Bar` is NULL if `@param` is NULL. The `=` operator won't do that. It doesn't result in a syntax error on the system I'm using (SQLite). – Dan Moulding Mar 24 '11 at 20:10
  • Can you cite a source that shows that my use of `IS` is a syntax error in standard SQL? (I'm using SQLite, which allows it, but maybe that's a non-standard extension). – Dan Moulding Mar 24 '11 at 20:18
  • IS only works if the value is null in standard SQL. I Have never used SQL lite, but have you tried it when you have a numeric or string value insted of null? – HLGEM Mar 24 '11 at 20:38
2

What specific database are you using?

If you're doing searches based on null (or not null), using IS is the way to go. I cannot provide a technical reason but I use this syntax all the time.

SELECT * FROM Table WHERE Field IS NULL

SELECT * FROM Table WHERE Field IS NOT NULL
justacoder
  • 2,684
  • 6
  • 47
  • 78
  • I am using SQLite. The problem is I don't know whether I will be comparing to NULL or not. I am comparing to `@param`. At runtime, `@param` will sometimes be NULL, and at other times it will be non-NULL. – Dan Moulding Mar 24 '11 at 20:15