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
- UNION ALL
- INTERSECT
- MINUS
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.
| Table_A | |
|---|---|
| id | value |
| 1 | Johnny |
| 2 | Marry |
| 3 | Teddy |
| Table_B | |
|---|---|
| id | value |
| 2 | Marry |
| 3 | Teddy |
| 4 | Jane |
UNION
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
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
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
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