Skip to main content

Set Operator in SQL

Set Operators

Set operator used to combine multiple queries into a single query
Types of set operator
1. UNION/UNION ALL
2. INTERSECT
3. MINUS

Guideline to use Set

  • The expressions in the SELECT lists must match in number.
  • The data type of each column in the second query must match the data type of its corresponding column in the first query.
  • Parentheses can be used to alter the sequence of execution.
  • ORDER BY clause can appear only at the very end of the statement.

Set Operator

  • Column names from the first query appear in the result
  • Duplicate rows are automatically eliminated except in UNION ALL
  • The output is sorted in ascending order by default except in UNION ALL

Consider this two table Employees and Job_History

Employees Table

g1

JOB_HISTORY Table

job_history

1. UNION/UNION ALL

UNION

  • The UNION operator returns rows from both queries after eliminating duplications.

union

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

uniion

UNION ALL

  • The UNION ALL operator returns rows from both queries, including all duplications.

UNION ALL

Query:Display the current and previous departments of all employees

SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;

union-all

2. INTERSECT

  • The INTERSECT operator returns rows that are common to both queries.

intersect

Query: Display the employee IDs and job IDs of those employees who currently have a job title that is the same as their previous one

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

intersect

3. MINUS

  • The MINUS operator returns all the distinct rows selected by the first query, but not present in the second query result set.

minus

Query: Display the Employee IDs of those employees who have not changed their jobs even once

SELECT employee_id
FROM employees
MINUS
SELECT employee_id
FROM job_history;

minus