Intersect, Inner Join sounds similar…but technicallyyy, is it.. hmm??? let’s find; (Series-I Intersect n Innerjoin)

Intersect operator: it retrieves the common record among the tables, most of time you will see the result of intersect operation is same as inner join query, but we are going to explore where it differs from inner join.

Note: while using Intersect operator the number and order of column must be same in all query as well as the datatype of both right and left query of intersect operator must be compatible.

Lets take an example to see the difference between intersect and inner join.

Here we will be creating two table ServiceProviderContact and ServiceProviderAddress and populate with some dummy data..

create table ServiceProviderContact

(serviceProviderId int identity(1,1) primary key,

serverProviderName varchar(10),

serverProviderCompany varchar(10),

modifiedDate datetime)

create table ServiceProviderAddress

(serviceProviderAddressId int identity(1,1) primary key,

serviceProviderId int references ServiceProviderContact(serviceProviderId),

serviceProviderAddress varchar(15),

modifiedDate datetime)

Now our table is ready, lets populate it with some data..

insert into ServiceProviderContact

values('akshita','ABC Ltd',getdate())

insert into ServiceProviderContact

values('Riddhi','ABC Ltd',getdate())

insert into ServiceProviderContact0

values('Samprada','ABC Ltd',getdate())

insert into ServiceProviderAddress

values(1,'ADI-Paldi', getdate())

insert into ServiceProviderAddress

values(1,'ADI-Sahibaug', getdate())

insert into ServiceProviderAddress

values(2,'ADI-Navragpura', getdate())

select * from ServiceProviderContacts

serviceProviderId serverProviderName serverProviderCompany modifiedDate

----------------- ------------------ --------------------- -----------------------

1                 akshita            ABC Ltd               2012-12-22 12:15:30.843

2                 Riddhi             ABC Ltd               2012-12-22 12:15:30.860

3                 Samprada           ABC Ltd               2012-12-22 12:15:30.860

(3 row(s) affected)

select * from ServiceProviderAddress

serviceProviderAddressId serviceProviderId serviceProviderAddress modifiedDate

------------------------ ----------------- ---------------------- -----------------------

4                        1                 ADI-Paldi              2012-12-22 12:21:10.513

5                        1                 ADI-Sahibaug           2012-12-22 12:21:10.513

6                        2                 ADI-Navragpura         2012-12-22 12:21:10.590

(3 row(s) affected)

Now lets fire intersect operation

select serviceProviderId, convert(varchar(12),modifiedDate,106) from  ServiceProviderAddress

intersect

select serviceProviderId, convert(varchar(12),modifiedDate,106) from  ServiceProviderContact

serviceProviderId

----------------- ------------

1                 22 Dec 2012

2                 22 Dec 2012

(2 row(s) affected)

The output shows the records that are common in both tables. It show only two in the above example.

Now lets try using Inner Join

select c.serviceProviderId, convert(varchar(12),c.modifiedDate,106)

from  ServiceProviderAddress a,ServiceProviderContact c

where  c.serviceProviderId = a.serviceProviderId and convert(varchar(12),c.modifiedDate,106) = convert(varchar(12),a.modifiedDate,106)

serviceProviderId

----------------- ------------

1                 22 Dec 2012

1                 22 Dec 2012

2                 22 Dec 2012

(3 row(s) affected)

The result are different as compared to Intersect Operator, since inner join first perform cartisen product then filter the data according to join condition.

Conclusion : So what we found inner join is similar to interest except it result in duplicate data where as intersect give the common record among left and right table of intersect operator.

Next Article would be: Stimulate Intersect operator using Inner join

Play through code and enjoy…

Post Reference: Vikram Aristocratic Elfin Share