The MINUS operator in SQL is used to return the rows from the first query that are not present in the second query. It serves to find the differences between two result sets. The two queries should have the same number of columns, and the data types in corresponding columns should be compatible.
Here’s a general outline of how to use the MINUS operator:
Basic Syntax
SELECT column1, column2, ...
FROM table1
WHERE [conditions]
MINUS
SELECT column1, column2, ...
FROM table2
WHERE [conditions];
Example
Imagine you have two tables: employees_current and employees_former. You want to find the list of employees who are currently employed but are not in the former employees list.
SELECT employee_id, employee_name
FROM employees_current
MINUS
SELECT employee_id, employee_name
FROM employees_former;
Explanation:
1. First Query: This selects all current employees from the employees_current table.
2. MINUS Operation: This subtracts the result of the second query from the first.
3. Second Query: This selects all former employees from the employees_former table.
4. Result: The final output will be a list of employees who are currently employed and are not listed as former employees.
Important Points:
- Column Match: The number of columns in both SELECT statements must match, and the data types of corresponding columns should be compatible.
- Duplicates: The MINUS operation returns only distinct rows. If both queries return the same row, it will appear only once in the result set.
- Alternative in Other DBMS: If you are using databases that do not support the MINUS operator (such as MySQL), you can achieve similar results using a LEFT JOIN or NOT IN or EXCEPT depending on the capabilities of the SQL dialect being used.
Alternative with NOT IN:
If you are using a SQL variant that does not support MINUS, you can achieve similar results using:
SELECT employee_id, employee_name
FROM employees_current
WHERE employee_id NOT IN (
SELECT employee_id
FROM employees_former
);
This will yield the same result as the MINUS example for finding current employees not listed as former employees.
Using these techniques, you can effectively find differences between two queries in SQL.