NULLIF vs IFNULL

Hirschel Wasserman

NULLIF vs IFNULL
Any comment on the differing approaches between

SELECT * from TABLE1
WHERE IFNULL(POSTAL_CDE,:BLANK) = :VAR1
vs
WHERE POSTAL_CDE = NULLIF(:VAR1,:BLANK)

where VAR is a CHAR VARYING host variable.
and POSTAL_CDE is a column

For ref
The IFNULL function returns the first nonnull expression.
The NULLIF function returns null if the two arguments are equal;
otherwise, it returns the value of the first argument.

If the operator is =, the result of the predicate is:

*
True if all pairs of corresponding value expressions evaluate to
true.
*
False if any one pair of corresponding value expressions
evaluates to false.
*
Otherwise, unknown (that is, if at least one comparison of
corresponding value expressions is unknown because of a null value and
no pair of corresponding value expressions evaluates to false).


I do notice differing results, because "where x = NULL" and "where x IS
NULL" are different.
Also, any thoughts on performance when POSTAL_CDE is an indexed field.

Appreciate any thoughts and insights


Hirschel Wasserman



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Peter Vanroose

Re: NULLIF vs IFNULL
(in response to Hirschel Wasserman)
> SELECT * from TABLE1
> WHERE IFNULL(POSTAL_CDE,:BLANK) = :VAR1
> vs
> WHERE POSTAL_CDE = NULLIF(:VAR1,:BLANK)

In the case where :VAR1 equals :BLANK, the first query will include rows with POSTAL_CDE empty
(NULL), while the second one will not.
Reason: in that case, NULLIF(:VAR1,:BLANK) is NULL, but POSTAL_CDE = NULL is never true, not even
when POSTAL_CDE is NULL.
Otherwise said: the second query makes no sense.

Note that also the first qurey is not very performant because of the presence of a scalar function
in the condition.
Better replace it by
SELECT * from TABLE1
WHERE POSTAL_CDE = :VAR1
OR POSTAL_CDE IS NULL

In D2 v8, you can also use
SELECT * from TABLE1
WHERE POSTAL_CDE IS NOT DISTINCT FROM :VAR1

-- Peter.





_________________________________________________________
Flyger tiden iväg? Fånga dagen med Yahoo! Mails inbyggda
kalender. Dessutom 250 MB gratis, virusscanning och antispam. Få den på: http://se.mail.yahoo.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: NULLIF vs IFNULL
(in response to Peter Vanroose)
When :VAR1 is spaces and POSTAL_CODE is null, do *you* want the row
returned or not? It isn't for us to tell you the answer.

If you want the result from the first predicate, it might be better, pre V8, to
use
SELECT * from TABLE1 WHERE POSTAL_CDE = :VAR1
union all
SELECT * from TABLE1 WHERE POSTAL_CDE IS NULL
as this allows both sides to use index lookup rather than an index scan.

James Campbell

On 12 Jan 2007 at 13:55, Wasserman, Hirschel wrote:

>
> Any comment on the differing approaches between
>
> SELECT * from TABLE1
> WHERE IFNULL(POSTAL_CDE,:BLANK) =:VAR1
> vs
> WHERE POSTAL_CDE = NULLIF(:VAR1,:BLANK)
>
> where VAR is a CHAR VARYING host variable.
> and POSTAL_CDE is a column
>
> For ref
> The IFNULL function returns the first nonnull expression.
> The NULLIF function returns null if the two arguments are equal; otherwise, it returns the
> value of the first argument.
>
> If the operator is =, the result of the predicate is:
> * True if all pairs of corresponding value expressions evaluate to true.
> * False if any one pair of corresponding value expressions evaluates to false.
> * Otherwise, unknown (that is, if at least one comparison of corresponding value expressions is
> unknown because of a null value and no pair of corresponding value expressions evaluates
> to false).
>
> I do notice differing results, because "where x = NULL" and "where x IS NULL" are different.
> Also, any thoughts onperformance when POSTAL_CDE is an indexed field.
>
> Appreciate any thoughts and insights
>
>
> Hirschel Wasserman
>
> --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list.
> To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm