SQL Set Operators | Intersect and Minus in SQL | Real Life Scenarios | Difference Between Intersect and Minus | Real Life Examples of Intersect and Minus

  • 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.

Intersect and Minus in SQL

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..

HOME

Leave a Reply