SQL Basics | DML: Data Manipulation Language | MINUS
MINUS is a command used with two SQL statements. MINUS first checks the result of the first SQL statement. It then checks whether the retrieved result exists in the result of the second SQL statement. If it does, that data is removed and does not appear in the final result. Data that does not appear in the first SQL statement’s result is excluded.
MINUS syntax
The MINUS command is as follows.
[SQL statement 1]
MINUS
[SQL statement 2];
MINUS example
Suppose you have the following two tables.
store_information table
| store_name | sales | txn_date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-2018 |
| San Diego | 250 | Jan-07-2018 |
| Los Angeles | 300 | Jan-08-2018 |
| Boston | 700 | Jan-08-2018 |
internet_sales table
| txn_date | sales |
|---|---|
| Jan-07-2018 | 250 |
| Jan-10-2018 | 535 |
| Jan-11-2018 | 320 |
| Jan-12-2018 | 750 |
To check which dates have store sales but no internet sales, use the following SQL statement.
SELECT txn_date FROM store_information
MINUS
SELECT txn_date FROM Internet_sales;
The SQL execution result is as follows.
| txn_date |
|---|
| Jan-05-2018 |
| Jan-08-2018 |
Jan-05-2018, Jan-07-2018, and Jan-08-2018 are returned by SELECT txn_date FROM store_information. Among them, Jan-07-2018 exists in the result of SELECT txn_date FROM internet_sales, so it does not appear in the final result.
Note that the MINUS statement displays each distinct value only once based on the statement result.