Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle INTERSECT Operator      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: INTERSECT

Definition:
The Oracle PL/SQL INTERSECT operator is an Oracle set operator which picks the common, or "intersecting" records from compound SELECT queries. The final result set contains the records which are the part of both of the SELECT queries, without duplicates, in sorted (ascending) order. In essence, it is a SQL set operation that selects only the common elements from two different SELECT statements.

Each statement used with INTERSECT must have the same number of fields in the result sets and have similar data types.

Example Syntax:

SELECT [query_1]
INTERSECT
SELECT [query_2]
[,ALL]


Notes:
  1. The number of columns in the compound queries must be equal and compatible in data types in all the participating SELECT queries
  2. The optional ALL keyword retains duplicates.
  3. Positional ordering should be used to order the final result set.
  4. INTERSECT does not work with columns of type LONG, BLOB, CLOB, BFILE, VARRAY, or nested tables.
  5. INTERSECT cannot be used in queries using TABLE collection expressions.
  6. The FOR UPDATE clause cannot be specified in the queries using SET operators.

Example Usage:

The query below intersects the result set of two SELECT statements to find the common JOB IDs in department '20' and '30':

SELECT JOB FROM EMPLOYEE
WHERE DEPT = 30
INTERSECT
SELECT JOB FROM EMPLOYEE
WHERE DEPT = 20



Related Links:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 263 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?