Advanced SQL Topics Minus

Syntax

SELECT * FROM table_name 
MINUS 
SELECT * FROM another_table_name;

Example

Let’s say we have two tables, employees and temp_employees, which have the following data:

employees table:

emp_id	emp_name	dept_id
1	John	101
2	Mary	102
3	Tony	101
4	Lisa	103

temp_employees table:

emp_id	emp_name	dept_id
1	John	101
3	Tony	101
5	Alice	103
6	Bob	104

To find the employees who are not temporary, we can use the MINUS operator as follows:

SELECT * FROM employees
MINUS
SELECT * FROM temp_employees;

Output

The output of the above query will be:

emp_id	emp_name	dept_id
2	Mary	102
4	Lisa	103

This is because the employees with emp_id 1 and 3 are present in both tables and are eliminated from the result.

Explanation

The MINUS operator is used to subtract one query result from another. It returns only those records that are present in the first query result but not in the second query result.

Use

The MINUS operator can be useful in scenarios where we need to compare two sets of data and find the differences between them. For example, it can be used to find the records that are present in one table but not in another, or to find the records that match a certain condition in one table but not in another.

Important Points

• The MINUS operator is not supported by all database systems. In some systems, it is called EXCEPT.
• The columns in both queries must be of the same data type and in the same order.
• The number of columns in both queries must be the same.
• The MINUS operator eliminates duplicate records from the first query result.

Summary

The MINUS operator is used to subtract one query result from another. It returns only those records that are present in the first query result but not in the second query result. It can be useful in scenarios where we need to compare two sets of data and find the differences between them. However, it is not supported by all database systems and has certain requirements for the columns and number of columns in the queries.