Note: This question is part of a series of questions that use the same or similar answer choices. An
answer choice may be correct for more than one question in the series. Each question is independent
of the other questions in this series. Information and details provided in a question apply only to that
question.
You have a database that contains tables named Customer_CRMSystem and Customer_HRSystem. Both
tables use the following structure:
The tables include the following records:
Customer_CRMSystem
Customer_HRSystem
Records that contain null values for CustomerCode can be uniquely identified by CustomerName.
You need to display a list of customers that do not appear in the Customer_HRSystem table.
Which Transact-SQL statement should you run?
A.
B.
C.
D.
E.
F.
G.
H.
Explanation:
EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.
https://msdn.microsoft.com/en-us/library/ms188055.aspx
D is correct
17
0
agree
0
0
the right anwser is C
the except operator is a set operator. and NULL is treated differently by the set operators UNION, EXCEPT, and INTERSECT than it is in search conditions. This difference is one of the main reasons to use set operators. When comparing rows, set operators treat NULL values as equal to each other. so with D you have this output
customercode customername
NULL francisco
cus4 jack
and this not fullfil the requirement.
with the left outer join (C)
the output is the one desired
customercode customername
cus4 jack
1
3
It’s true that using a set operator to compare two NULLs you get true. However, option D will not return the Francisco because this row exists in the CRM table while doesn’t exist in the HRS table, excluded by EXCEPT.
As for option C, the ON clause matches CustomerCodes, which contain NULLs. While comparing NULLs, the result is unknown and hence the row will be discarded. That means the Jane entry will be included in the result.
0
0
Correction to the previous reply:
The row NULL francisco will be included in the option D result and that is expected as HRSystem table doesn’t include this customer. The option D returns:
NULL Francisco
CUS4 Jack
To use OUTER JOIN for this task, as when comparing NULLs in OUTER JOIN the result is unknown, the query returns un-matched rows, i.e.
SELECT C.CustomerCode, C.CustomerName, H.CustomerCode, H.CustomerName
FROM dbo.Customer_CRMSystem AS C
LEFT OUTER JOIN dbo.Customer_HRSystem AS H
ON C.CustomerCode = H.CustomerCode
returns:
CUS1 Roya CUS1 Roya
CUS9 Almudena CUS9 Almudena
CUS4 Jack NULL NULL
NULL Jane NULL NULL
NULL Francisco NULL NULL
Notice that the Jane entry is not matched because of NULL in its CustomerCode, so the ON clause should be modified as:
ON C.CustomerCode = H.CustomerCode
OR H.CustomerCode IS NULL AND C.CustomerCode IS NULL AND C.CustomerName = H.CustomerName
and it returns:
CUS1 Roya CUS1 Roya
CUS9 Almudena CUS9 Almudena
CUS4 Jack NULL NULL
NULL Jane NULL Jane
NULL Francisco NULL NULL
Now filter out the result as:
WHERE H.CustomerName is NULL
and it returns:
CUS4 Jack NULL NULL
NULL Francisco NULL NULL
With the same effect as option D.
0
0