Sunday, January 22, 2012

Intersect and Except in sql server

These are called as Set operators.The set operators are used to perform mathematical set operations on the data available in the tables .To work with set of operations we need to write multiple select statements the data retrieved by each select statement will be treated as one set and then set operator are apply on those sets.Sql Server supports three set operators
INTERSECT: The set operator INTERSECT will written the values that are common in both the sets by eliminating duplicate values.Here i will show an example to find the jobs that are available in both the departments 40 and 30
select Job from emg where deptno=30
INTERSECT
select Job from emg where deptno=40
EXCEPT:
The set operator INTERSECT will written the values that are available in first but not in second set.Here i will show an example to find the jobs that are available in first
select Job from emg where deptno=30
EXCEPT
select Job from emg where deptno=40
Post a Comment

Bel