Title: case when in sql
Tags: sql;tsql
Question: I have table when contains who's two column contains data/null , i want to capture only rows which is not null.
example of t1
Application_channel | reason_for_joining
call    | null
null    | do not wish to provide
null    | null
I want output as
Status
do not wish to provide
query i wrote is somthing like:-
select case
when reason_for_joining is null and application_channel is not null then       application_channel
when application_channel is null and reason_for_joining is not null then
reason_for_joining else application_channel end as status
from t1
the problem is its also taking null when both the column has null value which i dont want. Any help much appreciated.
Comment: Hint : `COALESCE()` with `WHERE` clause.

Comment: @GonzaloLorieto. . . For instance i would say `COALESCE()` is ANSI SQL Function which works almost in all `DBMS`, while `ISNULL()` is only `SQL Server` specific, apart from that it functionally behaves different.

Comment: @GonzaloLorieto COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine. Both ISNULL and COALESCE can be used to get the same results but there are some differences.                                           1. Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence

Comment: @YogeshSharma what's the difference between `COALESCE()` and `ISNULL()`?

Comment: And in `SQL Server`?

Here is the accepted answer: He also wanted to filter those that are empty, so it would be:
select coalesce(Application_channel, reason_for_joining) as status
from t1
where coalesce(Application_channel, reason_for_joining) is not null
Alternatively you could also filter like this:
WHERE Application_channel IS NOT NULL OR reason_for_joining IS NOT NULL
Comment for this answer: von spickzak, thanks buddy, i have used the atternative one, and it worked . Much appreciated your help. i have also tried coalesce.

Here is another answer: You want coalesce():
select coalesce(Application_channel, reason_for_joining) as status
from t1
Comment for this answer: thanks brother Gordon. i have used WHERE Application_channel IS NOT NULL OR reason_for_joining IS NOT NULL seems to fix my issue. any difference between coalesce and is not null?
Comment for this answer: Basically ISNULL takes only two arguments while COALESCE takes multiple arguments (it returns the first non-NULL). For more details see here: https://stackoverflow.com/questions/7408893/using-isnull-vs-using-coalesce-for-checking-a-specific-condition

Here is another answer: Is case of two parameter ISNULL also do same work
select ISNULL('call',null),isnull(null,'do not')
so in you query
select ISNULL(Application_channel, reason_for_joining) as status
from t1
where ISNULL(Application_channel, reason_for_joining) is not null

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 5, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2018:00:10/25/18', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<sql><tsql>', 'Title': 'case when in sql', 'ViewCount': 63, 'id': 52989144, 'user_AboutMe': '<p>Working as a Data administrator for a not for profit organization. I have 8+ years experience on MI Reporting. 4+ year experience on Tableau and started to work on Power BI since last year.</p>\n', 'user_AccountId': 11179416.0, 'user_DisplayName': 'Biswa', 'user_Reputation': 331.0, 'user_Views': 82.0, 'user_id': 8203881.0}}