The UNION and INTERSECT operators in SQL allow you to combine the results of two or more SELECT queries and return only unique rows or only rows that are present in both queries, respectively.
Employee Table
| emp_id | emp_name | salary | dept_id | manager_id |
|---|---|---|---|---|
| E1 | Rahul | 15000 | D1 | M1 |
| E2 | Manoj | 15000 | D1 | M1 |
| E3 | James | 55000 | D2 | M2 |
| E4 | Michael | 25000 | D2 | M2 |
| E5 | Ali | 20000 | D10 | M3 |
| E6 | Robin | 35000 | D10 | M3 |
Department Table
| dept_id | dept_name |
|---|---|
| D1 | IT |
| D2 | HR |
| D3 | Finance |
| D4 | Admin |
Here is an example of how you can use the UNION operator to combine the results of two SELECT queries:
SELECT emp_name
FROM Employee
WHERE dept_id = 'D1'
UNION
SELECT dept_name
FROM Department;
Output
| emp_name |
|---|
| Admin |
| Finance |
| HR |
| IT |
| Manoj |
| Rahul |
The above query selects the emp_name column from the Employee table where the dept_id is D1, and the dept_name column from the Department table. The UNION operator combines the results of these two SELECT queries and removes any duplicates. The result of this query is a list of employee names from the IT department and department names from the Department table.
To write an INTERSECT query for the Employee and Department tables that you have provided, and display the output, you can use the following SQL statement:
SELECT emp_name, dept_name
FROM Employee e
JOIN Department d
ON e.dept_id = d.dept_id
INTERSECT
SELECT emp_name, dept_name
FROM Employee e
JOIN Department d
ON e.dept_id = d.dept_id;
Output
| emp_name | dept_name |
|---|---|
| James | HR |
| Manoj | IT |
| Michael | HR |
| Rahul | IT |
The above query selects the emp_name and dept_name columns from the Employee and Department tables, and return a row for each combination of an employee and a department where the dept_id in the Employee table matches the dept_id in the Department table. The INTERSECT operator returns only rows that are present in both queries, which in this case is every row that is returned by the JOIN clause.
The result of this query is a list of employee names and department names for all employees who have a matching dept_id in both tables.
Code Runner
Copy the below queries in the code runner to see the result
SELECT emp_name
FROM Employee
WHERE dept_id = 'D1'
UNION
SELECT dept_name
FROM Department;
SELECT emp_name, dept_name
FROM Employee e
JOIN Department d
ON e.dept_id = d.dept_id
INTERSECT
SELECT emp_name, dept_name
FROM Employee e
JOIN Department d
ON e.dept_id = d.dept_id;