SQL MINUS, SQL EXCEPT

SQL MINUS query or SQL EXCEPT query is used to subtract out the result of second query from the result of the first qeury. It takes the distinct result set of the first query, then filter out the records which appear in the result set of the second query. If the second query includes the records which are not in the first query, they will be ignored.

The two query statements should have same column structure: same number of columns, same or compatible data types and in same order.

Some databases use SQL MINUS and some databases use SQL EXCEPT. Please check documentation before use them. For some popular databases, here is a quick reference: MS SQL Server uses SQL EXCEPT, Oracle uses SQL MINUS, and mySQL doesn’t support any of them.

Note: The result of SQL MINUS query or SQL EXCEPT query is distinct.

SQL MINUS Query or SQL EXCEPT Syntax

SELECT Column(s) FROM Table1
MINUS
SELECT Column(s) FROM Table2

Or

SELECT Column(s) FROM Table1
EXCEPT
SELECT Column(s) FROM Table2

SQL MINUS query or SQL EXCEPT query Example

Table: Table1

Table: Table2

SELECT ID, ClientName FROM Table1
MINUS
SELECT ID, ClientName FROM Table2

The result will look like: