Sql COALESCE entire rows?

Question

I just learned about COALESCE and I'm wondering if it's possible to COALESCE an entire row of data between two tables? If not, what's the best approach to the following ramblings?

For instance, I have these two tables and assuming that all columns match:

tbl_Employees

Id     Name     Email     Etc
-----------------------------------
1      Sue      ...       ...  
2      Rick     ...       ...

tbl_Customers

Id     Name     Email     Etc
-----------------------------------
1      Bob      ...       ...  
2      Dan      ...       ...  
3      Mary     ...       ...

And a table with id's:

tbl_PeopleInCompany

Id     CompanyId 
-----------------
1      1
2      1
3      1

And I want to query the data in a way that gets rows from the first table with matching id's, but gets from second table if no id is found.

So the resulting query would look like:

Id     Name     Email     Etc
-----------------------------------
1      Sue      ...       ...  
2      Rick     ...       ...  
3      Mary     ...       ...

Where Sue and Rick was taken from the first table, and Mary from the second.

What is the purpose of the `tbl_PeopleInId` table? Looks like you can just `FULL OUTER JOIN` from `tbl_Employees` to `tbl_Customers` to get the result you want. Also I assume you are not asking for any practical purpose. — Martin Smith, Sep 13 '11 at 20:50
Hey Martin, i guess the tbl_PeopleInIs is too diluted for the example. I edited it to make better sense. Basically a table for relationships between two sets of data. — Levitikon, Sep 13 '11 at 20:54

score 8 · Accepted Answer · answered Sep 13 '11 at 20:51

 SELECT Id, Name, Email, Etc FROM tbl_Employees
      WHERE Id IN (SELECT ID From tbl_PeopleInID)
 UNION ALL
 SELECT Id, Name, Email, Etc FROM tbl_Customers
      WHERE Id IN (SELECT ID From tbl_PeopleInID) AND
            Id NOT IN (SELECT Id FROM tbl_Employees)

Depending on the number of rows, there are several different ways to write these queries (with JOIN and EXISTS), but try this first.

This query first selects all the people from tbl_Employees that have an Id value in your target list (the table tbl_PeopleInID). It then adds to the "bottom" of this bunch of rows the results of the second query. The second query gets all tbl_Customer rows with Ids in your target list but excluding any with Ids that appear in tbl_Employees.

The total list contains the people you want — all Ids from tbl_PeopleInID with preference given to Employees but missing records pulled from Customers.

+1 Avoid nulls in the first place then you won't need `COALESCE()`! Although the narrative of this answer has been 'dumbed down', the code uses sound relation operators: union, semi join (`IN`) and semi difference (`NOT IN`). — onedaywhen, Sep 14 '11 at 08:20

score 4 · Answer 2 · answered Sep 13 '11 at 20:56

4

You can also do this:

1) Outer Join the two tables on tbl_Employees.Id = tbl_Customers.Id. This will give you all the rows from tbl_Employees and leave the tbl_Customers columns null if there is no matching row.

2) Use CASE WHEN to select either the tbl_Employees column or tbl_Customers column, based on whether tbl_Customers.Id IS NULL, like this:

CASE WHEN tbl_Customers.Id IS NULL THEN tbl_Employees.Name ELSE tbl_Customers.Name END AS Name

(My syntax might not be perfect there, but the technique is sound).

answered Sep 13 '11 at 20:56

JohnC

844
4
10

I'm trying to avoid coalescing individual columns because the two tables have a lot of columns and might grow over time. – Levitikon Sep 13 '11 at 21:01
1

@JohnC Instead of a `case`, you could use `coalesce(tbl_Employees.Name, tbl_Customers.Name) Name` which has the same semantics if `tbl_Employees.Name` is non-null. – Josh Sep 13 '11 at 21:02
@Levitikon: Doing a "SELECT *" along with a "UNION ALL" is fragile. It requires the two tables to be exactly the same forever. If a column is added to either table it breaks down. To be more resilient in the face of such changes, I'd suggest specifying the column names. – JohnC Sep 14 '11 at 12:33

score 1 · Answer 3 · answered Sep 13 '11 at 21:20

This should be pretty performant. It uses a CTE to basically build a small table of Customers that have no matching Employee records, and then it simply UNIONs that result with the Employee records

;WITH FilteredCustomers (Id, Name, Email, Etc)
AS
(
    SELECT  Id, Name, Email, Etc
    FROM    tbl_Customers C
            INNER JOIN tbl_PeopleInCompany PIC
            ON C.Id = PIC.Id
            LEFT JOIN tbl_Employees E
            ON C.Id = E.Id
    WHERE   E.Id IS NULL
)

SELECT  Id, Name, Email, Etc
FROM    tbl_Employees E
        INNER JOIN tbl_PeopleInCompany PIC
        ON C.Id = PIC.Id

UNION

SELECT  Id, Name, Email, Etc
FROM    FilteredCustomers

Using the IN Operator can be rather taxing on large queries as it might have to evaluate the subquery for each record being processed.

No tag on the question indicates this is SQL Server, so the user may not have CTE available. Also, subqueries do not require per-row evaluation unless they're correlated, which these are not. — Larry Lustig, Sep 13 '11 at 21:31
very fair, a temp table could also take the place of the CTE above: — ericb, Sep 13 '11 at 21:53

score 0 · Answer 4 · answered Sep 13 '11 at 20:52

I don't think the COALESCE function can be used for what you're thinking. COALESCE is similar to ISNULL, except it allows you to pass in multiple columns, and will return the first non-null value:

SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product

This article should explain it's application:

http://msdn.microsoft.com/en-us/library/ms190349.aspx

It sounds like Larry Lustig's answer is more along the lines of what you need though.

Sql COALESCE entire rows?

4 Answers4

Linked