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
ID | ClientName |
---|---|
1 | Yoiia |
2 | Hiyfad |
3 | Miofa |
4 | Suiaf |
Table: Table2
ID | ClientName |
---|---|
1 | Yoiia |
2 | Hiyfad |
5 | Uifdad |
6 | Joyfa |
SELECT ID, ClientName FROM Table1 MINUS SELECT ID, ClientName FROM Table2
The result will look like:
ID | ClientName |
---|---|
3 | Miofa |
4 | Suiaf |