| Author |
Topic |
|
jassie
Constraint Violating Yak Guru
332 Posts |
 Posted - 2015-03-26 : 11:45:29
|
| In t-sql 2012 there is the coalesce statement being used twice in a where clause. I do not understand what the where clause is doing. Thus can you explain what the where clause means in the following statement where coalesce is being used twice:where coalesce(table1.status, table2.status) = 'A' and coalesce(table1.code, 'UNV') in ('ABS', 'EUF', 'UNV', 'LEG', ILL', 'SUP')Thus would you tell me what the above query means? |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2015-03-26 : 11:50:01
|
The basic logic is:WHERE (table1.[status] = 'A' OR (table1.[status] IS NULL AND table2.[status] = 'A')) AND (table1.code IN ('ABS', 'EUF', 'UNV', 'LEG', 'ILL', 'SUP') OR table1.code IS NULL) |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-26 : 11:52:09
|
| The clause says: Only include rows in the result set where 1.table1.status = 'A' or, if table1.status is null, then where table2.status = 'A'and2. table1.code = one of the following: 'ABS', 'EUF', 'UNV', 'LEG', ILL', 'SUP' or table1.code is null |
|
|
|
jassie
Constraint Violating Yak Guru
332 Posts |
Posted - 2015-03-26 : 12:39:04
|
| In my statement, "coalesce(table1.code, 'UNV') in ('ABS', 'EUF', 'UNV', 'LEG', ILL', 'SUP') there is the part of 'coalesce(table1.code, 'UNV')'. What is the 'UNV' being used for? I would think it is being used since it is listed, correct? |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-26 : 12:40:59
|
| 'UNV' is the value to be used in the case where table1.code is null.https://msdn.microsoft.com/en-CA/library/ms190349.aspx?f=255&MSPPError=-2147217396 |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-03-26 : 15:19:49
|
| The COALESCE function takes a list of values and returns the first one that is not NULL. With that in mind, we can easily determine what your specific statements do:where coalesce(table1.status, table2.status) = 'A'If table1.status is not null, it will be compared to 'A'. If table1.status is null, it will be ignored, and table2.status will be compared. If both are NULL, the result of the COALESCE is NULL, which be compared to 'A' and will never match (NULL is never "=" any value).and coalesce(table1.code, 'UNV') in ('ABS', 'EUF', 'UNV', 'LEG', ILL', 'SUP')If table1.code is not null, it will be checked against the IN list. If it is null, then 'UNV' will be checked against the list, since the literal 'UNV' will never itself be NULL. Btw, I'd suggest moving 'UNV' to first in the list since that's the default value; it might mildly speed up comparisons, esp. where there are a lot of NULL value in table1.code:and coalesce(table1.code, 'UNV') in ('UNV', 'ABS', 'EUF', 'LEG', ILL', 'SUP') |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-26 : 15:31:15
|
quote:
Originally posted by ScottPletcher
The COALESCE function takes a list of values and returns the first one that is not NULL. With that in mind, we can easily determine what your specific statements do:where coalesce(table1.status, table2.status) = 'A'If table1.status is not null, it will be compared to 'A'. If table1.status is null, it will be ignored, and table2.status will be compared. If both are NULL, the result of the COALESCE is NULL, which be compared to 'A' and will never match (NULL is never "=" any value).and coalesce(table1.code, 'UNV') in ('ABS', 'EUF', 'UNV', 'LEG', ILL', 'SUP')If table1.code is not null, it will be checked against the IN list. If it is null, then 'UNV' will be checked against the list, since the literal 'UNV' will never itself be NULL. Btw, I'd suggest moving 'UNV' to first in the list since that's the default value; it might mildly speed up comparisons, esp. where there are a lot of NULL value in table1.code:and coalesce(table1.code, 'UNV') in ('UNV', 'ABS', 'EUF', 'LEG', ILL', 'SUP')
or simply where table1.code in ('UNV', 'ABS', 'EUF', 'LEG', ILL', 'SUP') or table1.code is null |
|
|
|
|
|
|
t-sql 2012 coalesce