Set operators are used to combine multiple queries together. We call such queries “compound queries”. All set operators have the same precedence unless parentheses are usedYou can only combine queries with the same number of columns and same data types, otherwise, Oracle will throw an error.
We have 4 set operators available in Oracle:
- UNION ALL
I will now show you how to use them and about what you should be cautious. Let’s imagine these two tables as a source used for the examples below.
UNION operator combines the results of two queries together and removes duplicate data.
SELECT id FROM table_A UNION SELECT id FROM table_B id ---------- 1 2 3 4
UNION ALL operator combines the results of two queries together and returns all data as they are. Does not bother with duplicates or the order. UNION ALL is much faster than UNION because it needs to sort the data first and then eliminate duplicates. Try to stick to this one especially if you are sure there are no duplicates in your result sets.
SELECT id FROM table_A UNION ALL SELECT id FROM table_B id ---------- 1 2 3 2 3 4
INTERSECT combines the result of two queries and returns only those rows that match (=returned by both queries).
SELECT id FROM table_A INTERSECT SELECT id FROM table_B id ---------- 2 3
MINUS combines the result of two queries and returns only unique rows return by the first query that is not present in the second query.
SELECT id FROM table_A MINUS SELECT id FROM table_B id ---------- 1