How To Use Oracle INTERSECT Operator
  Home
 

This page is based on examples to be easier to follow. Oracle Intersect operator allowing you to compare two select statements and the operator returns identical lines that are present in both queries. The Oracle Intersect operator syntax is:

SELECT <columns>
  FROM <table_1>
INTERSECT
SELECT <columns>
  FROM <table_2>;

The first query will be used as a “source” through Oracle Intersect examples. Take a look at the data that is returned by the query to make the following example above more understandable.

 SELECT rownum + 2 AS nr
   FROM DUAL
    CONNECT BY rownum < 5;

online tech support computer help computer technician computer problems computer intersect oracle intersect in oracle database software database retirement planning retirement

The second SQL query will represent the second “source” and the output is lightly different from the first “source” query.

 SELECT rownum AS nr
   FROM DUAL
    CONNECT BY rownum < 10;

online tech support computer help computer technician computer problems computer intersect oracle intersect in oracle database software database retirement planning retirement

The first INTERSECT example shows how to use the operator inside SQL and it did return only numbers 3, 4, 5 and 6 as they are present in both queries.

 SELECT rownum + 2 AS nr
   FROM DUAL
    CONNECT BY rownum < 5
INTERSECT
 SELECT rownum AS nr
   FROM DUAL
    CONNECT BY rownum < 10;

online tech support computer help computer technician computer problems computer intersect oracle intersect in oracle database software database retirement planning retirement

When all queries have no any identical line then Oracle Intersect will return no rows as on the example below. The query below has value 2 and the second query has number 3. Both queries returning only 1 line and since no rows are identical the output is empty.

 SELECT 2 AS nr
   FROM DUAL
INTERSECT
 SELECT 3 AS nr
   FROM DUAL;

online tech support computer help computer technician computer problems computer intersect oracle intersect in oracle database software database retirement planning retirement

Important To Know:

Oracle Intersect operator compares the types per value and not by the outlook. For example number 2 in math is equal as 2.0. Even the look is lightly different; while they are declared as numbers the value remains the same and the Intersect Operator treats them as same value. See the following example to see how Intersect works with 2 and 2.0.

 SELECT 2 AS nr
   FROM DUAL
INTERSECT
 SELECT 2.0 AS nr
   FROM DUAL;

online tech support computer help computer technician computer problems computer intersect oracle intersect in oracle database software database retirement planning retirement

As you see from the last example output the 2.0 was turned to number 2 and since this matches with the first SQL value it gets returned by the operator.


See Also:
See Also:
Oracle Select Oracle Union Oracle Minus Home

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*