Skip to main content
HomeTutorialsSQL

How to Use SQL MINUS

The SQL MINUS operator subtracts one result set from another. It is simple to use: Just place MINUS between two queries to exclude common records from the first set.
Jun 2024  · 10 min read

In SQL, the MINUS operator is a powerful tool for subtracting one result set from another. This operator is essential for scenarios where we must identify the unique rows in one dataset but not another.

In this article, we will explore the purpose, syntax, and practical applications of the SQL MINUS operator, ensuring a deep understanding of effective SQL querying and data manipulation.

The Quick Answer: How to Use SQL MINUS

The SQL MINUS operator subtracts one result set from another, returning only the unique rows from the first set that are not present in the second.

Let’s imagine we have two employee tables, one from 2023 and one from 2024. We want to find out which employees were present in Employees2023 but are not in Employees2024. We could use an SQL MINUS statement to get the answer. 

SELECT EmployeeID, Name, Department
FROM Employees2023
MINUS
SELECT EmployeeID, Name, Department
FROM Employees2024;
Employees2023:

| EmployeeID | Name    | Department |

|------------|---------|------------|

| 1          | Alice   | HR         |

| 2          | Bob     | IT         |

| 3          | Charlie | Sales      |

| 4          | David   | IT         |


Employees2024:

| EmployeeID | Name    | Department |

|------------|---------|------------|

| 2          | Bob     | IT         |

| 3          | Charlie | Sales      |

| 4          | David   | IT         |

| 5          | Eve     | Marketing  |


Expected Output:

| EmployeeID | Name  | Department |

|------------|-------|------------|

| 1          | Alice | HR         |

What is the SQL MINUS Operator?

The SQL MINUS operator compares two SELECT statements and returns rows from the first SELECT statement that are absent in the second. Specifically, it performs a set difference operation, which is crucial for various data analysis tasks. This is particularly useful for data cleaning, validation, and consolidation efforts where we must pinpoint discrepancies between datasets.

Syntax of the SQL MINUS Operator

The basic syntax of the MINUS operator is as follows:

SELECT column1, column2, ...
FROM table1
MINUS
SELECT column1, column2, ...
FROM table2;

The components of the query are:

  • SELECT statement: The SELECT statement specifies the desired columns and the table from which to retrieve them.
  • MINUS operator: The MINUS operator subtracts the second result set from the first.
  • Optional components like the WHERE clause can be included to filter the data.
SELECT column1, column2, ...
FROM table1
WHERE condition
MINUS
SELECT column1, column2, ...
FROM table2
WHERE condition;

Important Points to Remember

To use the MINUS operator correctly, keep in mind these key points:

  • Both SELECT statements must have the same number of columns.
  • The data types of corresponding columns must be the same.
  • Although the column names don’t have to be identical, their data types must align.
  • MINUS removes duplicate rows from the final result set, returning only distinct rows.

Practical Examples

Let’s now take a look at some practical examples to solidify our understanding.

Example with a single expression

We use the following query to find the unique departments in 2019 that are not present in 2020:

SELECT Department
FROM Employees2019
MINUS
SELECT Department
FROM Employees2020;

Example with ORDER BY clause

We use the following query to get the unique employees in 2019, sorted by their names:

SELECT EmployeeName
FROM Employees2019
MINUS
SELECT EmployeeName
FROM Employees2020
ORDER BY EmployeeName;

Common Use Cases of the MINUS Operator

The SQL MINUS operator is a versatile tool that can be applied in various scenarios to improve data analysis and management. Below, we discuss several everyday use cases where the MINUS operator proves particularly useful.

Data validation in ETL processes

Ensure data consistency and accuracy in ETL (Extract, Transform, Load) processes. The MINUS operator can validate data by comparing source and target datasets.

Let's take a look an an example. To validate that all records from the staging table have been loaded into the final table, we can use the following query:

SELECT *
FROM StagingTable
MINUS
SELECT *
FROM FinalTable;

Identifying missing data between two datasets

When consolidating data from multiple sources, it is essential to identify any missing records to ensure completeness:

SELECT CustomerID
FROM SourceA
MINUS
SELECT CustomerID
FROM SourceB;

Limitations and Alternatives

While the SQL MINUS operator is a powerful tool for comparing datasets, it has certain limitations and may not always be the best choice for every scenario. Understanding these limitations and knowing the available alternatives can help us make better decisions when working with SQL queries.

  1. Not Supported by All Databases: The MINUS operator is not universally supported across all SQL database management systems. While it is available in Oracle, it is not supported in SQL Server, which uses the EXCEPT operator.
  2. Column and Data Type Requirements: Both SELECT statements used with the MINUS operator must have the same number of columns, and corresponding columns must have compatible data types. This can be restrictive when working with complex queries involving different datasets.
  3. Case Sensitivity: The MINUS operator is case-sensitive. For example, 'Apple' and 'apple' are considered different values. If case sensitivity is not considered, this can lead to unexpected results.
  4. No Sorting: The result set returned by the MINUS operator is not sorted. If a specific order is required, an additional ORDER BY clause must be used.
  5. Performance Issues: The MINUS operator can be slow when dealing with large datasets, as it needs to perform a complete comparison between the two result sets. This can lead to performance bottlenecks in large-scale applications.

An alternative to the MINUS operator is the EXCEPT operator, which is functionally similar and widely supported across different SQL databases.

  • MINUS: Standard in Oracle and some other databases.
  • EXCEPT: Standard in SQL Server, PostgreSQL, and other databases.

Conclusion

Understanding the SQL MINUS operator is crucial for effective SQL querying and data manipulation. This operator helps us identify unique rows between two datasets, making it valuable for data validation, cleanup, and consolidation tasks. Practice with different datasets to master the use of the MINUS operator and enhance your SQL skills.

If you really want to improve your SQL skills, take a DataCamp course like Introduction to SQL or Learn SQL. Both give a good foundation for more advanced topics. If you’re aiming for certification, the SQL Associate Certification can be a great goal to work towards. Intermediate learners can benefit from an Intermediate SQL course, which dives deeper into SQL complexities, including set operations like MINUS.


Photo of Oluseye Jeremiah
Author
Oluseye Jeremiah

Tech writer specializing in AI, ML, and data science, making complex ideas clear and accessible.

Frequently Asked Questions

What is the SQL MINUS operator used for?

The SQL MINUS operator is used to subtract one result set from another, returning only the unique rows from the first set that are not present in the second.

What are the syntax requirements for using the SQL MINUS operator?

Both SELECT statements in the MINUS operation must have the same number of columns, and the data types of corresponding columns must be the same.

Can the SQL MINUS operator be used in all SQL databases?

No, the MINUS operator is not supported by all SQL databases. For example, it is not supported in MySQL but is available in Oracle.

What are some common use cases for the SQL MINUS operator?

Common use cases include data validation in ETL processes, identifying missing data between two datasets, and cleaning up and consolidating data from multiple sources.

What is the difference between the SQL MINUS and EXCEPT operators?

The MINUS operator is primarily used in Oracle and some other databases, while the EXCEPT operator serves a similar function and is used in SQL Server, PostgreSQL, and other databases. Both operators return unique rows from the first SELECT statement that are not present in the second.

How is the SQL MINUS operator different than an anti join?

The MINUS operator in SQL is used to return all rows in the first query that are not present in the second query. It is a set operation that compares the results of two queries and returns the difference. An anti join, on the other hand, is a type of join that returns rows from the first table where no matching rows exist in the second table. This can be implemented using a LEFT JOIN combined with a WHERE clause to filter out rows where the join condition is not met.

Topics

Learn SQL with DataCamp

Course

Introduction to SQL

2 hr
713.5K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

tutorial

Set Operators in SQL: A Comprehensive Guide

Set operations in SQL are techniques for combining or comparing the results of two or more SELECT statements.
Kurtis Pykes 's photo

Kurtis Pykes

9 min

tutorial

How to Use the SQL IN Operator: A Guide to Master Efficient Filtering Techniques

The SQL IN operator allows you to filter query results to include only rows that meet specified conditions. Explore its full potential and alternatives.
Allan Ouko's photo

Allan Ouko

8 min

tutorial

SQL NOT IN Operator: A Comprehensive Guide for Beginners

Master SQL's NOT IN operator with this beginner's guide. Learn to filter data effectively, avoid common pitfalls, and explore efficient alternatives
Abid Ali Awan's photo

Abid Ali Awan

5 min

tutorial

How to Use the SQL BETWEEN Operator

The SQL BETWEEN operator helps filter values within specific ranges, enabling efficient data analysis. Discover its various applications and full potential.
Allan Ouko's photo

Allan Ouko

10 min

code-along

Getting Started in SQL

Learn how to write basic queries in SQL and find answers to business questions.
Kelsey McNeillie's photo

Kelsey McNeillie

code-along

SQL for Absolute Beginners

Start from the very basics of what SQL is and why it's essential, move through key components such as retrieving data from databases, manipulation of data, and basic SQL queries.
Adel Nehme's photo

Adel Nehme

See MoreSee More