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

HOME