How do the SQL "IS" and "=" operators differ?

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开发者_如何学Python using the IS operator in that case a safe (and sane) thing to do? Is there some other approach I should be taking?

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

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!

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

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.

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