SQL - ISNULL Record Value
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,
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
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
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).
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.
精彩评论