Set Operators Flashcards

Which set operator returns all rows from both queries, including duplicate rows?

Answer: UNION ALL

UNION ALL combines result sets from both queries and retains all duplicate rows without performing any deduplication.

Which set operator returns only the rows that appear in both result sets?

Answer: INTERSECT

INTERSECT returns only the rows that are common to both SELECT statements, eliminating rows unique to either query.

Which set operator returns rows from the first query that do NOT appear in the second query?

Answer: MINUS

MINUS (Oracle-specific) returns all distinct rows selected by the first query that are not present in the second query result.

When combining two SELECT statements with a set operator, which of the following is a mandatory requirement?

Answer: Both SELECT statements must return the same number of columns

All set operators require that both SELECT statements return the same number of columns for the operation to execute successfully.

In a compound query using set operators, the column headers in the final result are determined by which SELECT statement?

Answer: The first SELECT statement

Oracle uses the column names or aliases from the first SELECT statement to label the columns in the combined result set.

Where must the ORDER BY clause be placed when used with set operators in Oracle SQL?

Answer: Only at the very end of the entire compound query

When using set operators, a single ORDER BY clause must appear at the end of the entire compound query and applies to the complete combined result set.

What happens when corresponding columns in a UNION query have completely incompatible data types (for example, DATE and NUMBER)?

Answer: An ORA-01790 error is returned

Oracle returns ORA-01790 ('expression must have same datatype as corresponding expression') when corresponding columns have incompatible data types in a set operator query.