You are here:

Oracle/union and intersect

Advertisement


Question
What is the difference between union and intersect in plsql?

Answer
Hi Santhosreddy,

In SQL (in this case ORACLE SQL) the UNION operator is used to combine the result sets of 2 or more Oracle SELECT statements. It removes duplicate rows between the various SELECT statements.

The UNION ALL operator does the same except it does not remove duplicate rows between the various SELECT statements.

The INTERSECT operator is used to is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

You would typically use the INTERSECT operator to find what's common between the result set of 2 or more ORACLE SELECT statements. The UNION operator is used to combine the results of 2 or more ORACLE SELECT statements (excluding duplicates).

Illustrated example: http://www.programering.com/a/MTO4gzMwATE.html

Reference:
UNION operator: http://www.techonthenet.com/oracle/union.php
UNION ALL operator: http://www.techonthenet.com/oracle/union_all.php
INTERSECT operator: http://www.techonthenet.com/oracle/intersect.php


I hope this helps.

Peter  

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Peter Choi

Expertise

I am a senior Oracle DBA, PeopleSoft HCM specialist and Project Manager with 15+ years experience. I have been working with PeopleSoft (HRMS/HCM 5, 7, 7.x and 8.9, 9.x), Oracle RDBMS (7.3 - 11gR2) on various Unix and MS-Windows platforms, and some Oracle Application Server (9i - 11gR1). I also have experience with the configuration and administration of PeopleSoft's Internet Architecture (PIA) and the Oracle 11g Fusion Middleware and Oracle Business Intelligence (OBI).

©2016 About.com. All rights reserved.