Nov 9, 2013

Except and Intersect in SQL Server

EXCEPT and INTERSECT operators were introduced in SQL Server 2005. Both operator works on two result-sets.

Basic rules for combining two result-sets
  • No. of columns in both result-set should be equal
  • The order of columns should be same
  • Corresponding data type of each column in each select should be compatible
Syntax
SELECT col1, col2 FROM tab1
EXCEPT
SELECT col1, col2 FROM tab2

EXCEPT returns distinct rows from the first result-set which not exists in second result-set.

INTERSECT returns rows which exists in both result-set

Example
declare @tab1 table (col1 int, col2 varchar(10))
insert into @tab1
select 1, 'val1' union all
select 1, 'val2' union all
select 2, 'val1'

declare @tab2 table (col1 int, col2 varchar(10))
insert into @tab2
select 1, 'val1' union all
select 1, 'val3' union all
select 3, 'val1'

select col1, col2 from @tab1
except
select col1, col2 from @tab2

select col1, col2 from @tab1
intersect
select col1, col2 from @tab2
OUTPUT

Look at the Output
  • First output using EXCEPT where we are getting 2 rows which exist in table 1 but not in table 2
  • Second output using INTERSECT where we are getting 1 row which exists in both tables.
Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio