There are a number of restrictions related to the use of set operators. The number and datatypes of the columns in each query must be the same, but the column lengths can be different. The names of the columns in the result set are the names used in first query (so aliases specified in the first query apply for the second query). In terms of order-of-operation, all set operators have the same precedence. Operators with the same precedence are evaluated from left to right. Parenthesis can be used to change the order that the queries are evaluated.
In many cases, the functionality provided by the set operators is available by writing the query differently. However, the use of set operations can (at times) provide a clearer representation of the intention of the programmer creating the query than any alternative.
Within the HR Table, there are two tables that include a job_id field: JOB and JOB_HISTORY. The job_ids in these two fields will be compared using the set operators in the demonstrations below.
SELECT count(distinct job_id) FROM jobs; SELECT count(distinct job_id) FROM job_history;
Retrieve a count of distinct job ids from job. and the job history table. This information gives us a baseline for the queries that will use the set operators to reconcile job_ids in the two tables.
SELECT job_id FROM jobs INTERSECT SELECT job_id FROM job_history; -- -- This result is equivalent to using an INNER JOIN and DISTINCT -- SELECT DISTINCT j.job_id FROM jobs j INNER JOIN job_history jh ON j.job_id = jh.job_id;
This example of the INTERSECT operator is equivalent to a query that selects distinct rows from the jobs and job_history table related by an inner join.
SELECT distinct job_id FROM jobs MINUS SELECT distinct job_id FROM job_history; -- -- This query produces the same results as an antijoin -- between jobs and job_history -- SELECT job_id FROM jobs WHERE job_id NOT IN ( SELECT job_id FROM job_history );
This example of the MINUS operator is equivalent to using an antijoin (NOT IN clause) between the two tables.
SELECT distinct job_id FROM jobs UNION SELECT distinct job_id FROM job_history; -- -- This is equivalent to a full outer join with a distinct -- operation to eliminate duplicates -- SELECT DISTINCT j.job_id FROM jobs j FULL OUTER JOIN job_history jh ON j.job_id = jh.job_id;
The example of the UNION operator (which does not preserve duplicates) is shown in comparison to an analagous query that uses a full outer join and the distinct operator to remove duplicates.
SELECT distinct job_id FROM jobs UNION ALL SELECT distinct job_id FROM job_history ORDER BY job_id;
The UNION ALL operator preserves duplicates between both of the tables. Because it preserves all duplicates and does not involve a join, there is no simple similar query using a join to provide the same results.