CODEWITHSUNDEEP
sql
apsommer
apsommer

Reputation: 632

How does the SQL function COALESCE() work?

The SQL COALESCE() function can be described in a single sentence: COALESCE returns the first non-NULL value passed for each row. Please rephrase this sentence in a simple, logical manner with an example.

-- assume the a.id column contains the one and only NULL value in a large table
SELECT COALESCE(a.id, a.id) AS modified_id
FROM accounts a

This has the surprising result of actually creating a value in the new column modified_id! If the first argument in the function is NULL, how can the second argument (the value to replace the NULL in the first argument) produce anything in the resulting modified_id? We know the value in second argument must be NULL as it is literally the first argument.

Edit:

The following code produces the result in 2018-08-05_20-39-19.jpg

SELECT *
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

The following code produces the result in 2018-08-05_20-43-14.jpg

SELECT
    COALESCE(a.id, a.id) AS filled_id,
    a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, o.*
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

Upvotes: 1

Views: 8477

Answers (3)

tunnelview
tunnelview

Reputation: 47

COALESCE(First_Column_Value, Second_Column_Value, Third_Column_Value) as Derived_Column_Name Explanation: We can use multiple column_names in COALESCE and which ever column has as value that will be treated as the first value and it will be captured in the derived_column_name. If the values in the First_Column_Value is present, display that else check for the value in the Second_Column_Value and if its present display that, if not then check Third_Column_Value and display that.

Example: COALESCE(ea.AdmissionDate,ta.AdmissionDate) as Patient_Admission_Date Here 'ea' and 'ta' are two different tables or they could be same table with two different aliases in case of a self join.

Likewise, COALESCE() can be used for multiple columns. COALESCE(ea.Admission_ID,ta.Transfer_ID, ca.Exit_ID) as Patient_Admission_Date

Similarly, COALESCE() can be used with another inbuilt function, such as DATEADD().

COALESCE(DATEDIFF(DD,ca.AdmissionDate,ca.DischargeDate), DATEDIFF(DD,ta.AdmissionDate,ta.DischargeDate)) AS Difference_Of_Days

Needless to say that COALESCE() is usually written in your select statement as it can be used for a derived_column,Nonetheless, it can also be used in the join clause.

For instance, Select * from Employees ea left join Sales_DB..Sales_Pipeline p on COALESCE(ea.SRN, ta.SRN) = p.SRN

where, ea is an alias for Employees table, left joined to a table called Sales_Pipeline aliased as p on the COALESCE(either of the two column values) SRN means sales_record_number.

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

You must have a typo, or some other simple reason.

It is very easy to verify, just return both a.id and COALESCE(a.id, a.id) in the same query together. Make sure to use aliases, so that you know that you are looking at a.id and not id from some other table.

SELECT
    a.id AS a_id,
    COALESCE(a.id, a.id) AS filled_id,
    a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, o.*
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

Upvotes: 2

Phil Baines
Phil Baines

Reputation: 467

For this example you've provided, if a.id is null of course the second a.id will be null.

COALESCE is helpful with left joins for e.g

select coalesce(DE.user_id, KBU.user_id, OUD.user_id) as User_ID          
        LEFT JOIN dm.EDW.DIM_Employee DE
            ON de.user_id = RIGHT(SubQuery.[UserID], LEN(SubQuery.[UserID]) - 5)
            AND de.dss_current_flag = 'Y'
            AND de.Dss_Deleted_Flag = 'N'

        -- Some of the users are missing from our Dim Table so need to use other sources
        LEFT JOIN tts..load_KBNetwork_Users KBU
            ON KBU.UID = RIGHT(SubQuery.[UserID], LEN(SubQuery.[UserID]) - 5)

        LEFT JOIN dm.dbo.organisationunitdimension OUD
            ON oud.OrgUnitLevel5SourceId = RIGHT(SubQuery.[UserID], LEN(SubQuery.[UserID]) - 5)
            AND oud.dss_current_flag = 'Y'

if DE is null then I can get the first null value from the next two (if possible)

Upvotes: 0

Related Questions