Web Analytics Made Easy - Statcounter

Intersection command in SQL Server

This function is used to returns distinct values that are returned by both the query on the left and right sides of the INTERSECT operand. For intersection command, below basic rules must be followed.
  1. The number and the order of the columns must be the same in all queries.
  2. The data types must be compatible.
 Syntax:-

{ query_specification }
 INTERSECT
{ query_specification }

In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)

We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A001′,’Vivek’,’Johari’,’samir@abcmail.com’,200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A002′,’Amit’,’Kumar’,’amit@abcmail.com’,100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A003′,’Neha’,’Sharma’,’neha@abcmail.com’,300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A004′,’Chandra’,’Singh’,’vivek@abcmail.com’,320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A005′,’Avinash’, ‘Dubey’,’avinash@abcmail.com’,250000)

Example

If we execute the below queries individually, we get the following result:-

Select Empnumber from employee where salary>200000


Result

A003
A004
A005

Select Empnumber from employee where salary between 100000 and 320000

Result

A001
A002
A003
A004
A005


Now if we use Intersection command between these queries, we get the following result.

Select Empnumber from employee where salary>200000
 INTERSECT
Select Empnumber from employee where salary between 100000 and 320000
Result

A003
A004
A005

Above example shows that the Intersection command will return only those distinct records which are return by both the queries(left side query as well as right side query).

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

31 Comments

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading