SQL INTERSECT

SQL Using INTERSECT

INTERSECT - returns common results from sets of queries—that is, only the duplicates. Showing only a unique representation of the duplicated row.

Pictorial representation :

A skeleton version of INTERSECT syntax is this:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
[ORDER BY column1, column1, ...] 
Example of using INTERSECT:
MySQL does not support INTERSECT.
SQL server, Oracle, PostgreSQL :
select CONCAT(Last_name,CONCAT(' ',First_name)) as Name,
   Telephone_type "Phone type",
   Telephone_number as "Phone number",
   us_state_nm as "USA State"
 FROM customers  c
 JOIN telephones t ON (c.Customer_Id=t.Customer_Id)
 JOIN Addresses a ON (c.Customer_Id=a.Customer_Id)
 JOIN US_states u ON (a.US_State_cd=u.US_State_cd)
 WHERE Address_type='Shipping'
INTERSECT
 select CONCAT(Last_name,CONCAT(' ',First_name)) as Name,
   Telephone_type "Phone type",
   Telephone_number as "Phone number",
   us_state_nm as "USA State"
 FROM customers  c
 JOIN telephones t ON (c.Customer_Id=t.Customer_Id)
 JOIN Addresses a ON (c.Customer_Id=a.Customer_Id)
 JOIN US_states u ON (a.US_State_cd=u.US_State_cd)
 WHERE Address_type='Home'
ORDER BY Name;
The result should be:
name Phone type Phone number USA State
Darnovsky Marcy Unknown Massachusetts
Darnovsky Marcy Unknown (881) 141-54 Massachusetts
Emerson Sandra Unknown Arkansas
Emerson Sandra Unknown (881) 141-54 Arkansas
Nixon Ricard Home (978) 667-94 Florida
Nixon Ricard Work (978) 667-94 Florida
Redford Robert Home (679) 234-94 California
Redford Robert Unknown (678) 223-94 California
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.