Which statement is true regarding the INTERSECT operator?
-
It ignores NULL values.
-
Reversing the order of the intersected tables alters the result.
-
The names of columns in all SELECT statements must be identical.
-
The number of columns and data types must be identical for all SELECT statements in the query.
INTERSECT requires that all SELECT statements in the query have the same number of columns and matching data types (option D) - this is a fundamental requirement for set operations. Option A is false: INTERSECT treats NULL values like any other value and includes them in results. Option B is false: INTERSECT is commutative - order doesn't affect the result set. Option C is false: column names don't need to match; only position and data type must align.
To answer this question, you need to understand the INTERSECT operator in SQL. Let's go through each option to understand why it is correct or incorrect:
Option A) It ignores NULL values - This option is incorrect. The INTERSECT operator does not ignore NULL values. If a row contains NULL values in any of the intersected tables, it will not be included in the result.
Option B) Reversing the order of the intersected tables alters the result - This option is incorrect. The order of the intersected tables does not affect the result of the INTERSECT operator. The result will be the same regardless of the order of the tables.
Option C) The names of columns in all SELECT statements must be identical - This option is incorrect. The INTERSECT operator does not require the column names to be identical in all SELECT statements. However, the number of columns and their data types must be identical.
Option D) The number of columns and data types must be identical for all SELECT statements in the query - This option is correct. The INTERSECT operator requires the number of columns and their data types to be identical in all SELECT statements. If the number of columns or their data types differ, the query will result in an error.
The correct answer is D. This option is correct because the number of columns and data types must be identical for all SELECT statements in the query when using the INTERSECT operator.