The key to performance is elegance, not battalions of special cases. Jon Bentley and Doug McIlroy

### Difference between UNION, MINUS and INTERSECT

April 10, 2016 9:16 pm

### Difference between UNION, MINUS and INTERSECT

The employee table contains the following fields

```
Name           Null     Type
-------------- -------- ------------
EMPLOYEE_ID    NOT NULL NUMBER(6)
FIRST_NAME              VARCHAR2(20)
LAST_NAME      NOT NULL VARCHAR2(25)
EMAIL          NOT NULL VARCHAR2(25)
PHONE_NUMBER            VARCHAR2(20)
HIRE_DATE      NOT NULL DATE
JOB_ID         NOT NULL VARCHAR2(10)
SALARY                  NUMBER(8,2)
COMMISSION_PCT          NUMBER(2,2)
MANAGER_ID              NUMBER(6)
DEPARTMENT_ID           NUMBER(4)
```

#### UNION

The union uses to combine the result of two or more tables from the select statements. The union select statement should select the same number of fields in the result set. The UNION operator returns only distinct rows that appear in either result. The UNION ALL operator returns all rows from two result sets. The UNION ALL operator does not eliminate duplicate.

#### MINUS

MINUS takes the first result set, and removes any that exist in the second result set; it also removes any duplicates.

#### INTERSECT

The intersect return the results from two or more select statements.

### Solution

```
-- UNION
SELECT * FROM HR.employees WHERE DEPARTMENT_ID = 90
UNION
SELECT * FROM HR.employees WHERE DEPARTMENT_ID = 60;

-- MINUS
SELECT * FROM HR.employees
MINUS
SELECT * FROM HR.employees WHERE DEPARTMENT_ID >= 60;

-- INTERSECT
SELECT * FROM HR.employees WHERE EMPLOYEE_ID IN (100, 103, 105)
INTERSECT
SELECT * FROM HR.employees WHERE  EMPLOYEE_ID in (100, 102, 104, 105)
```