MINUS
Description
The MINUS
operator in SQL is used to return all rows from the first SELECT
statement that are not present in the second SELECT
statement. It is effectively the same as the EXCEPT
operator and is commonly used in Oracle databases.
Supported Databases: The MINUS
operator is supported by Oracle and some other SQL databases but not by all, such as MySQL or SQL Server. In databases that do not support MINUS
, similar functionality can be achieved using LEFT JOIN
or subqueries.
The MINUS
and EXCEPT
operators serve the same purpose i.e. they return all rows from the first SELECT
statement that are not present in the second SELECT
statement. However, their interchangeability depends on the specific SQL database management system (DBMS) being used.
Eliminates Duplicates: The
MINUS
operator automatically removes duplicate rows from the result set.Column Match: The number of columns and the data types of the columns in the
SELECT
statements must match.Order of Queries Matters: The order of the
SELECT
statements determines which rows are subtracted from which.
Syntax
Example
Consider two tables, employees
and managers
, with the following data:
employees
table:
emp_id | name | dept_id |
---|---|---|
1 | Alice | 101 |
2 | Bob | 102 |
3 | Charlie | 103 |
4 | Dave | 104 |
managers
table:
mgr_id | name | dept_id |
---|---|---|
2 | Bob | 102 |
3 | Charlie | 103 |
5 | Eve | 105 |
To find employees who are not managers, you can use the MINUS
operator as follows:
Result
name | dept_id |
---|---|
Alice | 101 |
Dave | 104 |
In this example, the MINUS
clause returns the rows where the name
and dept_id
are present in the employees
table but not in the managers
table.
Last updated