SQL Server 2014 - retrieve a value from another table if column is null

Question

Let's say I have a table setup with a few values, including a name, an ID, and a foreign key that references the ID of another table. The name can be null.

When I select all the records from this table, I want to get the name if it is not null. If it is, I want to get the name of the record referenced by the foreign key. It may possible that name of the record referenced by the foreign key can also null.

What are my options?

Which one is better between ISNULL & COALESCE?

Possible duplicate of [Using ISNULL vs using COALESCE for checking a specific condition?](https://stackoverflow.com/questions/7408893/using-isnull-vs-using-coalesce-for-checking-a-specific-condition) — tarheel, Dec 24 '18 at 06:57

score 0 · Answer 1 · answered Dec 24 '18 at 06:08

You can use either of them (ISNULL OR COLLASCE).

SELECT ISNULL(t1.name, (SELECT ISNULL(t2.name,"") FROM Table2 t2 WHERE ID=t1.my_foreign_id)) AS "name" FROM Table1 t1;  

SELECT COLLASCE(t1.name, (SELECT COLLASCE(t2.name,"") FROM Table2 t2 WHERE ID=t1.my_foreign_id)) AS "name" FROM Table1 t1;

score 0 · Answer 2 · answered Dec 27 '18 at 09:05

Also you can try to use CASE WHEN .

select case when t1.name is not null  then t1.name 
else 
     case when (select t2.name FROM Table2 t2 WHERE ID=t1.my_foreign_id ) is not null 
     then (select t2.name FROM Table2 t2 WHERE ID=t1.my_foreign_id ) else '' end 
end 
from  Table1 t1

score 0 · Answer 3 · answered Dec 27 '18 at 16:25

Use a LEFT JOIN then COALESCE the name values to account for nulls. As for COALESCE vs. ISNULL, COALESCE is part of the ANSI standards which will make it portable across other RDBMS such as Oracle and DB2. You mentioned that the name column may be null in both tables. If you want to substitute an additional value COALESCE will allow for this, while ISNULL only accepts two parameters. The data type of the result differs between both functions as well, with ISNULL using the same data type as the first parameter whereas COALESCE will use the data type with the highest precedence. Keep in mind, that this could lead to conversion errors if an attempt is made to implicitly convert a data type of a lower precedence to that of a high one. A common example is attempting to convert a VARCHAR/NVARCHAR type to a numeric one. A list of data types by precedence can be found here.

SELECT 
FT.ID,
COALESCE(FT.Name, PT.Name, 'Name Not Listed') as Name
FROM DBO.TableWithFK FT
LEFT JOIN DBO.TableWithPK PT on FT.ID = PT.ID

SQL Server 2014 - retrieve a value from another table if column is null

3 Answers3