SQL Minus vs Except
Introduction
In this article, we will be discussing the difference between “except” clause and the “minus” clause in an SQL database.
Clauses
Clauses in the SQL are keywords that have definite meaning (similar to their English language counterpart) and purpose. The purpose is self-explanatory by the name of the clause. For example, the WHERE clause is used to query a specific condition. GROUP BY clause is used to group the data on a specific attribute. ORDER BY clause is used to order the data by a specific attribute, etc.
Some clauses in SQL can only be used with another clause and they usually enhance the functionality of the clause they are being used with. For example, the HAVING clause can only be used with a group by clause and it restricts the results of the group by clause on a condition. The FROM clause is mandatory with the SELECT statement to specify from which table the data needs to be selected. SQL EXCEPT clause:
EXCEPT
In the real world, when we use the word except, we are defining an exception from what we stated in the statement preceding the ‘except’ and the statement succeeding describes that exception. For example, “I like all the fruits except oranges and jackfruits”. Now in this statement, “I like all the fruits” is the original statement but “oranges and jackfruits” are not included in the fruits the person likes. In SQL, EXCEPT works in pretty much the same way. EXCEPT is used to restrict the results from a SELECT FROM WHERE query on the query after the EXCEPT clause. The general syntax is:
1 2 3 4 5 6 7 8 9 | SELECT columnA [, columnB ] FROM tableA [, tableB ] QUERRY A [WHERE condition ] EXCEPT SELECT columnA [, columnB ] FROM tableA [, tableB ] QUERRY B [WHERE condition] |
Now, this syntax is identical to the one we use in the English language, so it is very easy to understand. It would give all the results of query A except those which are in query B too.
Consider the following example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | CREATE TABLE customers ( c_id INT PRIMARY KEY, c_name TEXT NOT NULL, c_age INT NOT NULL, c_address CHAR(50), c_salary INT NOT NULL ); INSERT INTO customers ( c_id, c_name, c_age, c_address, c_salary ) VALUES (0,'JOHN CONNER',19,'23 BOULEVARD ST.',20000), (1,'ADAM SMITH',21,'28 BOULEVARD ST.',25000), (2,'BLAKE JONES',23,'22 BOULEVARD ST.',30000), (3,'BRIDGETTE JOHNSON',18,'21 BOULEVARD ST.',40000), (4,'CMAILIA BLACK',20,'27 BOULEVARD ST.',35000), (5,'AMY WINEHOUSE',20,'23 BOULEVARD ST.',20600), (6,'CHRIS JONES',23,'01 BOULEVARD ST.',37000); CREATE TABLE orders ( o_id INT PRIMARY KEY, o_date TEXT NOT NULL, c_id INT FOREIGN KEY REFERENCES customers(c_id), o_amount INT NOT NULL ); INSERT INTO orders ( o_id, o_date, c_id, o_amount ) VALUES (0,'09-01-2018',5, 3000), (1,'11-02-2019',1, 2110), (2,'20-03-2015',0, 4500), (3,'25-03-2019',6, 1010), (4,'26-12-2018',0, 350); |
When the above code is executed, we get the following tables:
Now if we want to see which customers did not purchase anything, we can do that by:
1 2 3 4 5 | SELECT c_id FROM customers EXCEPT SELECT DISTINCT c_id FROM orders; |
The except statement reduces to the result from the upper query to only those that are not in the lower query as evident by the output. The EXCEPT clause is not supported in all SQL databases. It does not work with MySQL and Oracle databases. Only PostgreSQL, SQL Server, and SQLite support the EXCEPT clause.
MINUS
The MINUS clause in SQL, as the name suggests, is used to subtract the results of one query from another. So it limits the results of one query depending on the results of another.
MINUS clause can only be used between two SELECT statements and it returns the results from one SELECT statement that is not returned by the other SELECT statement. The general syntax is:
1 2 3 4 5 6 7 8 9 | SELECT columnA [, columnB ] FROM tableA [, tableB ] QUERRY A [WHERE condition ] MINUS SELECT columnA [, columnB ] FROM tableA [, tableB ] QUERRY B [WHERE condition] |
The syntax of MINUS clause is the same as the EXCEPT statement and it works in the same way too as it returns all the results from query A that are not a part of query B. So they have the same functionality.
For example, if we want to know the customers who did not purchase anything, we did that using the EXCEPT statement. Now we can rewrite the query using the MINUS clause.
1 2 3 4 5 | SELECT c_id FROM customers MINUS SELECT DISTINCT c_id FROM orders; |
This would give the same results as we got with the EXCEPT clause:
The MINUS clause is not supported by all SQL databases. It does not work in PostgreSQL, SQLite and SQL Server. It works in MySQL and Oracle databases.
Conclusion
There is absolutely no difference in the EXCEPT clause and the MINUS clause. They both serve the same purpose and they are simply two different ways of achieving the same functionality. The difference is that EXCEPT is available in the PostgreSQL database while MINUS is available in MySQL and Oracle.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started