I used the Solid database for many years, since they came out with their Linux version back in 1998. Initially they had this behavior. Later they added = NULL so that it worked like IS NULL. My guess is that, standard or not, this was so utterly confusing to most people that it was better to be slightly non-standard than field all the support questions.
Here's what Microsoft does (not that they should be any kind of standard bearer, that's for sure!) From http://msdn.microsoft.com/en-us/library/aa196339(SQL.80).aspx: ---------- Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option. When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, this comparison always yields UNKNOWN when ANSI_NULLS is ON: ytd_sales > NULL This comparison also yields UNKNOWN any time the variable contains the value NULL: ytd_sales > @MyVariable Use the IS NULL or IS NOT NULL clauses to test for a NULL value. This can add complexity to the WHERE clause. For example, the Region column in the Northwind Customers table allows null values. If a SELECT statement is to test for null values in addition to others, it must include an IS NULL clause: SELECT CustomerID, CompanyName, Region FROM Northwind.dbo.Customers WHERE Region IN ('WA', 'SP', 'BC') OR Region IS NULL Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL. Also, a comparison of two expressions that have both evaluated to null values yields TRUE. With ANSI_NULLS set OFF, this SELECT statement returns all the rows in the Customer table for which Region is a null value: SELECT CustomerID, CompanyName, Region FROM Northwind.dbo.Customers WHERE Region = NULL ------------ I didn't see where MySQL supports this extension, so the safest thing is to do it like the SQL standard says. The other problem with allowing = NULL comparisons is that it would be confusing when binding parameters. If you say: con.execute(''' select f1 from tab where f2 = ? ''', (NULL,)) That means you want the Python variable NULL, not the SQL constant NULL. And you can't use 'NULL', because that's a string with 4 characters, not the SQL constant. I think that's probably why the IS NULL syntax is required. Jim On 3/11/09, Hynes, Tom <[email protected]> wrote: > Thanks for the quick response! Yes, I understand the differences between > querying with IS NULL vs. = NULL. But I had always thought that when using > *parameter binding* a NULL query parameter would be treated like the IS NULL > case when doing the comparison, not the equality case. Hmm, Sounds like > I've had a misconception about this. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
