you can also use ifnull(myvar1,'') = ifnull(myvar2,'') or something in the same way.
I used it for avoiding creating 2 queries for each cases. Cheers, Sylvain On Wed, Mar 11, 2009 at 4:14 PM, Jim Wilcoxson <[email protected]> wrote: > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
