why cant we use minus keyword rather than the minus sign

Question

+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

If I use the minus keyword then its not working but when i use the actual minus sign it works like

SELECT (COUNT(CITY)- COUNT(DISTINCT CITY))
FROM STATION;

why is that?

SELECT COUNT(CITY) FROM STATION
MINUS 
SELECT COUNT(DISTINCT CITY) FROM STATION;

Answer 1

minus is a similar operator as union - it combines multiple sets of rows to a new set of rows. It is Oracle's non-standard version of the EXCEPT operator.

EXCEPT (or MINUS) returns all rows from the first set that are not contained in the second.

Assuming the tables contains 10 rows and 5 different cities, then

So the query SELECT COUNT(CITY) FROM STATION returns the following result set:

count
-----
   10

and SELECT COUNT(DISTINCT CITY) FROM STATION returns

count
-----
    5

As MINUS returns the rows from the first set that are not contained in the second, the result of the MINUS operator is only the first row.

This also works with multiple rows and columns. Assume you have two queries.

The first one returns this

id | city  
---+-------
 1 | Berlin
 2 | Vienna
 3 | Paris 

and the second one returns

id | city  
---+-------
 1 | Berlin

combining both with EXCEPT (or MINUS) will return

id | city  
---+-------
 2 | Vienna
 3 | Paris 

Because those are the rows from the first set, that are not present in the second.

---

- subtracts one number value from another so it only works on columns in the SELECT list, not on complete sets of rows.

Answer 2

below is the purpose of using MINUS keyword

"The SQL MINUS operator is used to return all rows(actual rows not number) in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset."

if you want to minus number like you are doing in your code (count cities and minus from other distinct cities) thats means it will minus a number from a number (4 - 3) But if you are trying minus actual rows like stated above then you have to use MINUS keyword.