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?
- What are multiple types of Set operators in SQL?
- SQL Minus Operator with real industry examples
- 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 :
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.
- 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.
- The Minus operator will fetch the non similar rows from one or more table. The output is same as non equi join.
- Just make sure that Every select statement within minus must have same number of columns.
- 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;
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.
|2||Rahul||BE Second Year||55|
Second table :
Select Roll_no,Student_name,Class,Marks from Student;
Select Roll_no,Student_name,Class,Marks from Student_1;
The above Query should fetch only the common records:
|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?
|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?
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.