SQL Standard

SQL Minus

The SQL minus operator is used to subtract the result set of two select statements. For example, using the minus operator, you can pair two select statements and then remove the result of the first select statement from the second select statement.

It is a quirky but helpful feature that may come in handy when solutions like JOIN do not apply.

How It Works

Using the minus operator in SQL requires two select statements that return a similar number of records and similar data type as the result set.

The minus operator will then return unique records that exist in the first select statement but not the second.

The syntax of the minus operator in SQL is as shown:

SELECT col1, col2, ... colN

FROM table_name

[WHERE condition]

MINUS

SELECT col1, col2, ... colN

FROM table_name

[WHERE condition];

The syntax above illustrates how to combine two select statements using the minus operator.

NOTE: In some databases, the MINUS operator is not supported—for example, SQL Server, PostgreSQL, and Google BigQuery. You can use the EXCEPT statement or LEFT OUTER join as an alternative.

Suppose we have two tables:

Table 1

Table 2

We can perform a minus operation on the tables as shown:

select full_name, salary, active

from users

except

select full_name, salary, active

from users2;

NOTE: The above example uses PostgreSQL. Replace the except with minus for supported databases.

The query above should return:

Closing

This article introduced the SQL minus operator and how it works. Consider the documentation to learn more.

Thanks for reading!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list