1Z0-082 practice question 3 of 25

Which statement is true about the INTERSECT operator used in compound queries?

Full set: 142 questions
Answer options
A: Multiple INTERSECT operators are not possible in the same SQL statement
B: It processes NULLs in the selected columns Correct answer
C: INTERSECT is of lower precedence than UNION or UNION ALL
D: It ignores NULLs
Correct Answer:
B. It processes NULLs in the selected columns
Explanation

The INTERSECT operator, like other set operators (UNION, MINUS), compares entire rows from the result sets of two queries. For the purpose of this comparison, two NULL values in the same column position are considered equal. Therefore, INTERSECT will return a row containing a NULL if a matching row (including the NULL) exists in both result sets. This behavior is distinct from most other SQL comparisons where NULL is not equal to NULL.

Why Incorrect

A. Multiple INTERSECT operators can be used in a single SQL statement to find the intersection of more than two query results.
C. The INTERSECT operator has a higher precedence than UNION, UNION ALL, and MINUS. Queries are evaluated from top to bottom.
D. The INTERSECT operator does not ignore NULLs; it treats them as matching values when comparing rows between result sets.

References

1. Oracle Database SQL Language Reference, 19c. "The INTERSECT Operator". States, "For the purpose of the INTERSECT operator, two nulls are considered equal."

2. Oracle Database SQL Language Reference, 19c. "Set Operators". Describes the precedence: "The INTERSECT operator has a higher precedence than the UNION, UNION ALL, and MINUS operators."

Scroll to Top