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 data below:
Customer_CRMSystem
Customer_HRSystem
Records that contain null values for CustomerCode can be uniquely identified by CustomerName.
You need to display customers who appear in both tables and have a non-null CustomerCode.
Which Transact-SQL statement should you run?
Answer: B
Explanation:
INTERSECT returns distinct rows that are output by both the left and right input queries operator.
Incorrect Answers:
A: INNER JOIN returns records that have matching values in both tables but it returns duplicate records.
C: LEFT OUTER JOIN returns ALL records from the left table, and the matched records from the right table.
D: EXCEPT returns distinct rows from the left input query that are not output by the right input query.
E, F: UNION and UNION ALL combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.
G: CROSS JOIN returns all possible combinations of data from both tables.
H: FULL OUTER JOIN returns all records when there is a match in either left or right table
Note: NULL values are treated as distinct values in join operations.
References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transactsql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sqlserver-2017 https://www.w3schools.com/sql/sql_join.asp
Leave a Reply