Intersect and Minus in SQL :
SQL Set Operators combines the result of 2 queries or components on to the single result. The queries containing the different set operators like union, union all, intersect minus are simply called as Compound Query. SQL set operators used to get meaningful data from 2 or more different tables. In real world scenarios set operators are very useful in reporting,analytics,creating datawarehouse. In this article I will give you the basic idea of Intersect and Minus in SQL. Both are very important sql set operators.
When We want the dataset from only a table between 2 tables Intersect and Minus Operators are used. Vein diagram of Intersect operator and inner join is same but it is different because of its joining conditions.
Intersect and Minus in SQL:
- Intersect Operator:
When user wants to fetch the common records from the two different tables then intersect operator come in to picture.Intersect operator fetches the record which are common between 2 tables. Mysql does not support Intersect operator.For Intersecting 2 tables the datatype and column name must be same between 2 tables.
Syntax:
Select column1…column n from table1;
Intersect
Select column1…column n from table2;
Example Real Life Scenario:
Question:Kindly select the common records of employees from 2 different tables given below:
Suppose following is first table:
Table name:
Employee_OBIEE
Employee_num | Employee_name | Department | Salary |
1 | Amit | OBIEE | 680000 |
2 | Rohan | OBIEE | 550000 |
3 | Rohit | COGNOS | 430000 |
Second table
Table Name:Employee_Cognos
Employee_num | Employee_name | Department | Salary |
1 | Pradnya | Cognos | 522000 |
2 | Mohit | Cognos | 471100 |
3 | Rohit | COGNOS | 43000 |
Query:
Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;
Intersect
Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;
Output:
The above Query should fetch only the common records:
Employee_num | Employee_name | Department | Salary |
1 | Rohit | COGNOS | 43000 |
“Intersect and Minus does not support in Mysql…”
- Minus Operator:
When user wants to fetch the record from one table only and not the common records between two tables user needs to use Minus operator.Minus operator selects all the rows from first table but not from second table.It eliminates duplicate rows from first and second table.It removes the results from second table and always considered first table only.
Syntax:
Select column1…column n from table1;
Minus
Select column1…column n from table2;
Example Real Life Scenario:
Question:Write a query to select all records from Employee_OBIEE table but not common records from Employee_OBIEE and Employee_COGNOS table.
Suppose following is first table:
Table name:
Employee_OBIEE
Employee_num | Employee_name | Department | Salary |
1 | Amit | OBIEE | 680000 |
2 | Rohan | OBIEE | 550000 |
3 | Rohit | COGNOS | 430000 |
Second table
Table Name:Employee_Cognos
Employee_num | Employee_name | Department | Salary |
1 | Pradnya | Cognos | 522000 |
2 | Mohit | Cognos | 471100 |
3 | Rohit | COGNOS | 43000 |
Query:
Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;
Minus
Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;
Output:
Employee_num | Employee_name | Department | Salary |
1 | Amit | OBIEE | 680000 |
2 | Rohan | OBIEE | 550000 |
It has eliminated the record of Employee named Rohit.
Difference in tabular format:
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; Intersect Select col1,col2…from table2; |
2.Syntax:
Select col1,col2…from table1; Minus Select 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. |
Hope you will get idea about Intersect and Minus in SQL.If you like the article on Intersect and Minus in SQL please dont forget to comment in comment secton.
Click here to get 20 Most important Complex Sql Queries..
Very Good explanation for both the topic
Thank you Gopinath for good words!!