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.

Answer 1

 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.

Answer 2

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