What is Minus Query in SQL?
FREE
What is a MINUS Query in SQL?
Let's say you are running a stationery shop where you provide a pen set and a book. A person can buy either of these items. You have two databases containing information about people who bought the book and pen set from your shop. You wish to check the number of people who buy the book but do not buy the pen set with it. How will you do it?
You can get details of every person who buys the book from the first database, then you extract information from every person who buys the pen set from the second database. Now, you find results common in both these statements and remove them from the book result. After this, you will have a list of all people who only bought books and not the pen set.
The above statement might seem lengthy, but there is a simple method of doing it. You can use the MINUS query in SQL. The MINUS query in SQL is applied to two SELECT statements. It returns all rows present in the first SELECT statement, that is not present in the second SELECT statement. In terms of mathematics, it refers to A-B.
Note: The MINUS query is not compatible with all SQL databases. It can be used in Oracle and MySQL. However, if you are working on PostgreSQL or SQLite, then you can use the EXCEPT query. It works similarly to the MINUS Query.
Syntax
Let’s look at the syntax of the MINUS query in SQL
As we can see from syntax, the MINUS query in SQL consists of 2 SELECT statements. You can use the where condition to filter out data more (if it's required).
Parameters
Let's understand various parameters associated with the MINUS query
- column: column refers to a column of SQL database that you wish to retrieve.
- table_name: table_name refers to your table name.
- where condition: where condition is used in SQL to filter all records. It returns only those rows that fulfill the condition mentioned in the where statement.
Note: The column order and the number of columns in both the select statements should be the same while using MINUS. The data type of column should also be the same in both tables.
How does the MINUS Query Work in SQL?
Before proceeding with some hands-on example of a MINUS query in SQL, let us understand how the MINUS query work. The MINUS query works similarly to subtraction in SQL. When the two select statements are resolved it gets two sets of rows that contain information extracted from the database.
Now it will iterate through each row present in the first SELECT statement and find if there is a similar row present in the second set of rows. If it does not find one, then it will include it in the final answer. If it finds one, it will ignore it and move forward with the next one.
Perhaps you can get more insight from the pseudo-code given below.
Now we know both the MINUS query syntax and the theory behind it. Let's get some hands-on experience with some real-life examples.
Examples for MINUS Query in SQL
Example 1: Get the Name of All Students that Play Cricket but do not Play Football
For this example, we will be using the below-given tables.
Cricket table
| Name |
|---|
| Rahul |
| Krishna |
| Virat |
| Rakesh |
| Shantanu |
| Aman |
Football table
| Name |
|---|
| Messi |
| Ronaldo |
| Rakesh |
| Drogba |
| Madhur |
In this example, we are asked to find the name of all students that play cricket but does not play football. For this, we first need students who play cricket and football from their respective databases. So their SELECT statement should be.
Now we need to find students who play cricket and not football. hence we need to subtract football rows from Cricket rows. So our final query should be.
Output
Example 2: Get the name and age of all students who are above the age of 18 and only play the piano.
For this example, we will be using the below-given tables.
Piano table
| Name | Age |
|---|---|
| Rahul | 21 |
| Krishna | 18 |
| Virat | 17 |
| Rakesh | 16 |
| Shantanu | 14 |
| Aman | 32 |
Guitar table
| Name | Age |
|---|---|
| Messi | 27 |
| Ronaldo | 28 |
| Rakesh | 16 |
| Drogba | 26 |
| Aman | 32 |
In this example, we are asked to find the name and age of all students that only play the piano. For this, we first need students who play piano, and guitar and are above the age of 18 from their respective databases. So their SELECT statement should be.
Now we need to find students who play piano and not the guitar. hence we need to subtract guitar rows from piano rows. So our final query should be.
Output
Learn More
Conclusion
- MINUS Query in SQL is applied on two SELECT statements. It returns all rows that are present in the first SELECT statement, but not in the second SELECT statement.
- To apply the MINUS query in SQL, the number of columns and order of columns in both the select statements should be the same and their data types should be compatible.
- MINUS Query in SQL is only available in Oracle.
- MINUS Query in SQL can be combined with other SQL Operators to get data as per-user convenience.