SQL Minus Operator Examples | How to use Minus in SQL?

In my previous articles I have given the detailed idea about SQL interseciton operator with real life example. In this article I would like to throw light on SQL Minus Operator with its real life industry examples. In many interview questions they are asking about Minus operator. There are so many Set operators in SQL which are useful. I would like to give you bullet points about SQL intersect operator with real time industry examples.

What you will find in this article?

1. What are multiple types of Set operators in SQL?
2. SQL Minus Operator with real industry examples
3. Some Important Interview Questions related to SQL Minus Operator.

What are multiple types of Set operators in SQL?

The set operators are used to perform multiple set operations on tables in database. There are following 4 basic set operators used in SQL :

1. Union
2. Union All
3. Intersect
4. Minus

The above set operators are used to perform multiple set operations on tables. You may get so many questions in interview about these set operators.

SQL Minus Operator with real industry examples :

In this section we will focus on SQL Minus operator and how it is used in real life industry examples in detail. We will see multiple real life examples of intersection in SQL.

1. SQL Minus Operator combines the result of two or more tables where the column names and datatypes of the multiple tables needs to be similar.
2. The Minus operator will fetch the non similar rows from one or more table. The output is same as non equi join.
3. Just make sure that Every select statement within minus must have same number of columns.
4. The columns must have similar datatype and we require to follow order as well.

5.In above diagram if we see there are two sets : One set contains 1,2,3,4 and other set contains 1,2 as values. If we require to combine the dataset  and fetch the data using minus operator which gives you the non similar records.

6.Minus operator only shows or fetches Non Similar records from left table.

7. Syntax of Minus operator:

Select column1…column n from table1;

minus

Select column1…column n from table2;

Real Life Example of Intersect operator :

If you want to fetch the common records from Student and Student_1 table.

Student :

Roll_no	Student_name	Class	Marks
1	Amit	BE	68
2	Rahul	BE Second Year	55
3	Raju	BE	43

Second table :

Table Name:Student_1

Roll_no	Student_name	Class	Marks
1	Amit	BE	68
2	Mohit	BE	47
3	Raju	BE	43

Query:

Select Roll_no,Student_name,Class,Marks from Student;

Minus

Select Roll_no,Student_name,Class,Marks from Student_1;

Output:

The above Query should fetch only the common records:

Roll_No	Student_name	Class	Marks
2	Rahul	BE Second year	55

Some important interview questions for SQL Minus Operator :

In this section we will see the SQL intersect operator interview questions :

Question 1 : What is difference between Intersect and Minus operator?

Answer :

Intersect	Minus
1.Intersect Set operator is used to fetch the common records from 2 different tables .	1.Minus Operator is used to fetch the records from first table which eliminates common records.
2.Syntax:Select col1,col2…from table1;IntersectSelect col1,col2…from table2;	2.Syntax:Select col1,col2…from table1;MinusSelect col1,col2…from table2;
3.For Performance tuning Intersect operator is not preferable as it takes time to fetch duplicate records	3.Minus operator is preferable operator in Performance tuning.

Question 2 : Which is faster operation – Minus or Join?

Answer :

The Join operator more faster than minus operator.

I hope you like this article on Minus operator in SQL. If you like this article or if you have any issues kindly comment in comments section.