April 29, 2015 at 10:38 am
DonlSimpson (4/29/2015)
Luis Cazares (4/29/2015)
chgn01 (4/29/2015)
How about use ISNULL?-- Q3
SELECT
*
FROM
#abc a
LEFT JOIN #xyz x
ON ISNULL(a.a, 9) = ISNULL(x.x, 9)
AND ISNULL(a.b, 9) = ISNULL(x.y, 9);
In this case, they'll act the same way. But that won't be always the case.
If there's no coercing data going on, it should be the same. There's probably some esoteric exception though.
There are 2 main differences between ISNULL() and COALESCE():
The first (and widely known) difference is that ISNULL() will always accept 2 parameters (no more, no less) and COALESCE() can accept more parameters.
The second difference is the way they handle the data types. ISNULL() will use the data type of the first parameter and will try to convert the second parameter into the first parameter data type. COALESCE(), on the other hand, will use the data type with the highest data type precedence and convert the rest of the parameters to that data type. If you're unaware of these rules, you might get some unexpected errors.
April 29, 2015 at 11:58 am
Carlo Romagnano (4/29/2015)
Toreador (4/29/2015)
Only 54% correct? I was expecting close to 100%!It depends if one has enough time to read correctly the question of th day.
Yup! I *almost* answered wrong because I focused too much on the join condition and the NULL values, causing me to overlook that the query uses an outer join.
Noticed my mistake just in time...
May 1, 2015 at 11:34 am
I think I must be getting old and decrepit. Got it right, but it strained my brain 🙁
Tom
May 11, 2015 at 12:15 am
Correct your 2nd table insert statement to "INSERT INTO #xyz values". Not "INSERT INTO #abc values".
June 4, 2015 at 8:32 am
When you look carefully you will know it.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply