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

Second table

Table Name:Employee_Cognos

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:

“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

Second table

Table Name:Employee_Cognos

Query:

Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;

Minus

Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;

Output:

It has eliminated the record of Employee  named Rohit.

Difference in tabular format:

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