Geek Answers Handbook

COALESCE vs IS NOT NULL performance when checking an empty string

Some articles I found on the Internet compared ISNULL with COALESCE, so I think my question is a little different.

I am wondering what is better in terms of performance?

SELECT * FROM mytable WHERE mycolumn IS NOT NULL AND mycolumn <> '';

or

 SELECT * FROM mytable WHERE COALESCE(mycolumn,'') <> '';

Besides performance, are there other issues that I should consider when deciding?

EDIT:

I am using Teradata.

+6
sql teradata
source share
2 answers

This version is slightly more mobile and allows the use of a (potentially) index

 SELECT * FROM mytable WHERE mycolumn IS NOT NULL AND mycolumn <> '';

It can be simplified to

 SELECT * FROM mytable WHERE mycolumn <> '';

The reason I say “slightly” and “potentially” is because the non-equality predicate can mean that you still get a full check.

+7
source share

You can also use the ANSI_NULL ON parameter. This implicitly filters out the null values ​​in the column in which you give the search argument. This simplifies your request, I'm not sure if it makes it faster. Logically, however, it should be, because fewer filter arguments need to be evaluated, and no functions are used in where where, which should provide full index selectivity. As an example, you could re-submit your request as follows:

 SET ANSI_NULLS ON; SELECT * FROM mytable WHERE NOT mycolumn = '';

Hope this helps.

+1
source share

More articles:


All Articles