Optimal Way To Fill In Missing Values After A Left Join?

I will simplify my problem to make the core issue clear: I have a query that does TableA INNER JOIN TableB LEFT JOIN TableC. The result of the left join is that in the result set,

Solution 1:

You can use COALESCE(...) (MSDN - COALESCE) instead.

You query will then look like:

select a, b, COALESCE(TableB.c, 'replacement value')
from TableA INNERJOIN TableB LEFTJOIN TableC ...

Add another join for your replacement table and put the column you want to replace NULL values in the COALESCE function in you don't want to use a static value.

Solution 2:

"To fill in the missing values I have to loop over the result set and query another database that has the data (so it is not possible to join in the first place)."

Consider a different solution then looping to fill in data.

1.Another database on the same server==easy. Just join using db.schema qualified names.

1. Another database on a another server, still possibly depending on you network topography. Then join using server.db.schema qualified names.

2. Consider replicating the data you need if you regularly need to do this.