SQL - ISNULL Record Value

Question

Hello,

I have a SQL Statement where i need to display the value from another table if a joining record exists. To attempt this, I'm using ISNULL. As a demonstration, here is a sample query:

SELECT
 FirstName,
 LastName,
 ISNULL(select top 1 birthdate from BirthRecords where [SSN]=p.SSN, false) as HasRecord
FROM
 Person p

Please note, this is a small snippet. I know there is a better way to do this specific query. However, I cannot do an outer join in my FROM clause. Because of this, I'm trying to do an inline statement. I thought ISNULL was the correct approach. Can someone please explain how I should do this?

Thank you,

Answer 1

Use a left outer join to return the birthdate if it exists, otherwise null:

SELECT
    FirstName,
    LastName,
    birthdate
FROM Person AS p
LEFT JOIN BirthRecords AS b ON p.SSN = b.SSN

Answer 2

Doing a JOIN is really your best bet from a performance and readability perspective.

Why can you not do a JOIN?

Another option is to create a FUNCTION and call it. Something like GET_BIRTHDATE(p.SSN) and it would return a scaler value of null or the date. This is clearly a performance issue because the function will get called on every row...so the JOIN would still be better.

Answer 3

Try this and see if it works for ya.

SELECT
 FirstName,
 LastName,
 CASE when R.BirthDate IS NULL THEN FALSE
 ELSE TRUE
END  as HasRecord
FROM
 Person p
left join BirthRecords R on p.SSN = R.SSN

Answer 4

Your question is incomplete. You should at least specify:

• what DBMS you use (I guess MS SQL Server, because of ISNULL)
• what does/does not work

That said, I don't think you can use ISNULL like this. According to the docs, the replacement and the original column must be type compatible. So you cannot use "false" as the replacement, it must be a date (like birthdate).