SQL CASE WHEN a is NULL-CodePudding

I have 3 columns all contain email addresses

email
alt_email
main_email

I wanted to do case when email is null then alt_email when alt_email is null then email and when both are null then main_email end as all_emails.

I am not getting the correct answer as the total count for all_emails is way 90% less than the total all customers.

TIA

CodePudding user response：

SELECT
CASE
    WHEN email IS NULL AND alt_email IS NULL THEN main_email
    WHEN email IS NULL AND alt_email IS NOT NULL THEN alt_email
    WHEN alt_email IS NULL AND email IS NOT NULL THEN email
    ELSE email
END AS all_emails
FROM some_table;

CodePudding user response：

As @sparky mentioned, this is a job for COALESCE:

SELECT COALESCE(email, alt_email, main_email) AS all_email FROM ...

it gets the first non-null value from the columns mentioned, in that order.

See docs for SqlServer, but the same syntax applies to other DBMS's as well.

CodePudding user response：

Select CASE WHEN (email != '') THEN email WHEN (alt_email != '') THEN alt_email ELSE main_email END AS emaiId From table