The INTERSECT And MINUS Operators In SQL

This post shows INTERSECT and MINUS operators that make nice additions to your SQL toolkit. First let's establish the tables we'll be querying. Say we have 2 tables: Employee and Job History defined as such:

Employees: employee_id,first_name,last_name,email,phone_number,hire_date,job_id,department_id
employee_id is primary key(PK).
And
Job_History:
employee_id,start_date,end_date,job_id,department_id
The combination of employee_id and start_date is the primary key and employee_id, alone, is the foreign key(FK) back to the Employees table.

Now, the INTERSECT operator returns all rows that are common to multiple queries as shown in the example below:

SELECT employee_id,job_id FROM employees
INTERSECT
SELECT employee_id,job_id FROM job_history;

The rules for using INTERSECT with multiple table queries are:
--The number of columns and datatypes being used in the SELECT statement must be the same. The names of the columns do not have to be identical.
--Reversing the order of the queries does not change the outcome.
--The intersect operation does not ignore NULL values.

On a single table you can achieve intersection results by putting the AND operator in the WHERE clause as follows:

SELECT last_name, first_name
FROM employees
WHERE job_id ='CLERK' AND Salary = 25000;

Here, the intersection is obtained by using specific column values.

The MINUS operator looks at the difference between multiple queries. With MINUS, the order of the two sets is important as it will yield different results when the order of the two query sets is reversed. Like the INTERSECT operator, the number of columns and their respective data types being used in the SELECT must be the same. And the names of the columns do not have to be identical. Here is an example of how the MINUS operator works:

SET 1: 3,5,7,9,12      SET 2: 2,3,5,7,9,11
The difference of set 1 and set 2: (3,5,7,9,12) - (2,3,5,6,9,11) = 12
The difference of set 2 and set 1: (2,3,5,7,9,11) - (3,5,7,9,12) = 2,11

Here the order of the sets was reversed and yielded different results. Now look at this query using the MINUS operator:

SELECT employee_id FROM employees
MINUS
SELECT employee_id FROM job_history;

Now based on the tables as previously defined, this query will yield employees who have never changed jobs. That's pretty handy....