11 Set operators

What are the four set operators in Oracle SQL?

UNION, UNION ALL, INTERSECT, MINUS

What does the UNION operator do?

It merges row sets from two SELECT statements and eliminates duplicate rows.

What does the UNION ALL operator do?

It merges row sets from two SELECT statements but does not eliminate duplicates.

What does the INTERSECT operator do?

It returns only those rows that are present in both SELECT statements, eliminating duplicates.

What does the MINUS operator do?

It returns rows from the first SELECT that are not present in the second SELECT.

What requirements must SELECT statements meet to use set operators?

Same number of expressions in select list, matching data type groups (automatic data type conversion included) for each expression, no BLOB/CLOB, LOB types, ORDER BY only at the end. They don’t have to have matching column names or table names.

Can set operators be used without primary/foreign key relationships between tables?

Yes, the SELECT statements do not need relational keys.

Where can the ORDER BY clause be used when combining SELECT statements with set operators?

Only once, after the last SELECT statement.

What happens if the first SELECT statement has a different column heading?

The final output takes column names/aliases from the first SELECT.

How do set operators handle precedence?

All set operators have equal precedence and execute left to right unless parentheses change it.

How can you change the execution order of set operators?

Use parentheses to override default precedence.

What happens if you use ORDER BY inside an inner SELECT when combining with set operators?

It is not allowed; ORDER BY can only appear at the end of the full query.

What happens if duplicates exist in INTERSECT results?

Duplicates are eliminated.

What is different about MINUS compared to UNION, UNION ALL, and INTERSECT?

The order of SELECT statements matters for MINUS.

How can rows be sorted when using set operators?

By position or by reference, but only at the end of the query.

What does ORDER BY by position mean?

Refers to sorting using the column’s ordinal number in the select list.

What does ORDER BY by reference mean?

Refers to sorting using column names or aliases from the first SELECT statement.

Which SELECT statement's column names are valid for ORDER BY when using set operators?

Only column names or aliases from the first SELECT statement.

What is the correct placement of ORDER BY in queries with set operators?

It must be the final clause in the SQL statement.

Which keyword is NOT a set operator?

SET

Which set operator should you use to find rows in one table that are not in another?

MINUS

Which set operator’s result depends on the order of SELECT statements?

MINUS

Can set operators be used with INSERT statements?

No, they only work with SELECT statements.

How many ORDER BY clauses are allowed when combining SELECT statements with set operators?

Only one, and it must be at the end.

What do SET operators do?

They combine two or more SELECT statements similar to joins but in a different way

Is there a limit how many SET operators there can be?

No