COALESCE vs IS NOT NULL performance on checking empty string

sql teradata
Russell picture Russell · Oct 14, 2010 · Viewed 16.6k times · Source

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

I'm wondering which is better in terms of performance?

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

Or

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

Other than performance, are there any other concerns I should consider when deciding?

EDIT:

I'm using Teradata.

Answer

Martin Smith picture Martin Smith · Oct 14, 2010

This version is slightly more sargable and allows an index to be (potentially) used

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

It can be simplified to 

SELECT * FROM mytable WHERE mycolumn <> '';

The reason why I say "slightly" and "potentially" is that the non equality predicate may well mean you end up with a full scan anyway.

Related questions

SQL SELECT multi-columns INTO multi-variable

I'm converting SQL from Teradata to SQL Server in Teradata, they have the format SELECT col1, col2 FROM table1 INTO @variable1, @variable2 In SQL Server, I found SET @variable1 = ( SELECT col1 FROM table1 ); That only allows a single column/variable …

Read More
What is ROWS UNBOUNDED PRECEDING used for in Teradata?

I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the …

Read More
Find which rows have different values for a given column in Teradata SQL

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 456 Wall 2 2 456 Wall 3 1 789 Right 3 2 100 Left I'm just trying to figure out whether the address for each ID …

Read More