Sql COALESCE entire rows?

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 loo开发者_如何学Ck 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.

---

 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.

---

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).

---

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.

---

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.