Coalesce and ISNULL not grabbing columns with NULL

Question

Coalesce and ISNULL not grabbing columns with NULL

Matt.Altman

Say Hey Kid

Points: 705
More actions
November 17, 2016 at 2:19 pm

#315916

I am trying to combine data from two tables. 1 table has a column where a value could be NULL. When I run my select, I never see the row with the NULL value. Here is my command
SELECT
COALESCE (ISNULL(k.Application, ''), ISNULL(n.Application, '')) AS Application
FROM Table1 k
INNER JOIN Table2 n
ON k.Application = n.Application
GROUP BY COALESCE (ISNULL(k.Application, ''), ISNULL(n.Application, ''))

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Matt.Altman (11/17/2016)
I am trying to combine data from two tables. 1 table has a column where a value could be NULL. When I run my select, I never see the row with the NULL value. Here is my command
SELECT
COALESCE (ISNULL(k.Application, ''), ISNULL(n.Application, '')) AS Application
FROM Table1 k
INNER JOIN Table2 n
ON k.Application = n.Application
GROUP BY COALESCE (ISNULL(k.Application, ''), ISNULL(n.Application, ''))

You're joining the two tables on Application, this excludes nulls from the Application column on both sides, even when both are null. What exactly are you trying to do, understand null handling (which can be tricky if you're a recent convert to SQL) or understand joins?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Matt.Altman Say Hey Kid Points: 705 More actions · Answer 2

Understand joins. Didn't realize that would exclude nulls. Thank you.

drew.allen SSC Guru Points: 76988 More actions · Answer 3

The following two expressions are essentially equivalent.

COALESCE (ISNULL(k.Application, ''), ISNULL(n.Application, ''))

ISNULL(k.Application, '')

COALESCE evaluates the expressions in order until it finds one that is not null or it runs out of expressions to evaluate. ISNULL does the same thing, except that it is limited to two expressions.

In this case, the first expression in the COALESCE will never be null, so the second expression is never evaluated. If k.Application is not null, it will return k.Application. If k.Application is not null, then it will return ''.

I think what you want is

COALESCE(k.Application, n.Application, '')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Matt.Altman Say Hey Kid Points: 705 More actions · Answer 4

I tried that and the NULL values still will not show up. I am joining on a different column now.

Matt.Altman Say Hey Kid Points: 705 More actions · Answer 5

Seems like this worked

SELECT COALESCE (n.Application, k.Application) Application

FROM Table1 n

FULL OUTER JOIN Table2 k

ON n.Application = k.Application

GROUP BY COALESCE (n.Application, k.Application)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

Matt.Altman (11/17/2016)
Seems like this worked
SELECT COALESCE (n.Application, k.Application) Application
FROM Table1 n
FULL OUTER JOIN Table2 k
ON n.Application = k.Application
GROUP BY COALESCE (n.Application, k.Application)

Looks good to me, Matt.

As a hardcore TSQL developer I use ISNULL over COALESCE in these situations. There are functional differences between the two. You're on 9/10. How do you feel about upping that to a 10/10 by researching those differences using BOL (SQL Server help)?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 7

Matt.Altman (11/17/2016)
Seems like this worked
SELECT COALESCE (n.Application, k.Application) Application
FROM Table1 n
FULL OUTER JOIN Table2 k
ON n.Application = k.Application
GROUP BY COALESCE (n.Application, k.Application)

That looks like you are trying to get a distinct list of applications from two different tables.

Instead of the join/group, I'd recommend

SELECT Application

FROM Table1

UNION

SELECT Application

FROM Table2

That will likely perform better as it will only require a concatenation of the tables, followed by a sort.

Be careful with Full Outer Joins. If there are a lot of rows in your tables, the FOJ/Group can bring your server to its knees. Make sure your sample data volume accounts for future production volumes.

Wes
(A solid design is always preferable to a creative workaround)