Sunday, May 9, 2010

Understanding Intersect operator.

   The INTERSECT operator builds a relation consisting of tuples that appear  in both relations.
In other words, INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.
Query for INTERSECT operator.
(--Create a table)
create table S_Record
(
[Student Fname] varchar(50),
[Student Lname] varchar(50),
[Student course] varchar(30),
[Student Roll no]int
)
(--insert value in table)
Insert into S_Record values ( 'Jen' , 'Ambelang','BBA', 100)
Insert into S_Record values ( 'Abhijeet' , 'singh','BCA', 101)
Insert into S_Record values ( 'Madoka' , 'Kurosawa','B.Tech', 102)
Insert into S_Record values ( 'sonu' , 'goal','B.Sc.IT', 103)
(--Create new table)
Create table S_Details
(
[Student Fname] varchar(50),
[Student Lname] varchar(50),
[Student Roll no] int,
[Student Contact no] bigint,
[Student E-mail id] varchar(50)
)
(--insert value in table)
Insert into S_Details values ( 'Jen' , 'Ambelang', 100,2235689,'jen@gmail.com')
Insert into S_Details values ( 'abhijeet' , 'singh', 101,2355669,'Abhijeet@yahoo.com')
Insert into S_Details values ( 'adam' , 'Carlos', 102,2235689,'carlos@hotmail.com' )
Insert into S_Details values ( 'Madoka' , 'Kurosawa', 103,2235689,'Kurosawa@gmail.com')
Insert into S_Details values ( 'Hong',  'Annie', 104,2235689,'Annie@in.com')
Insert into S_Details values ( 'jhon',  'bhati', 105,2235689,'jhon@hotmail.com')
Insert into S_Details values ( 'jam',  'Annie', 106,2235689,'Annie.jam@yahoo.com')
(--Show intersect values)  
select [Student Fname],[Student Lname]  from S_Record
intersect
select [Student Fname],[Student Lname]  from S_Details  
NOTE:- Two table  S_Record and S_Details  contains Student Fname, Student Lname , Student course, Student Roll no and S_Details contains the Student Fname, Student Lname,  Student Roll no, Student Contact no, Student E-mail id.
From the resultset we understand:
a)      INTERSECT operator returns only the matching rows from the queries on left and right side of the INTERSECT OPERATOR.
b)      It removes duplicates and shows only the unique rows.     The result is displayed below
The two Set in the vain diagram below  shows records from the table S_Record and S_Details.
  When the  INTERSECT condition is applied.
Only intersected rows will be displayed..
 
 

No comments:

Recent Posts