How To Use INTERSECT Operator In Oracle

This tutorial page is based on examples to make it easier to follow. Oracle INTERSECT operator allows to compare two select statements and the operator returns identical lines that are present in both queries. The syntax of Oracle INTERSECT operator is:

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

This following Oracle select example query will be used as a “source” for following Oracle INTERSECT examples. You can take a look at the output rows that are returned by the query to understand better how works the other Oracle INTERSECT examples below.

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

This second example SQL query will represent the second “source” query and the output is following:

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

This example demonstrates how to use operator INTERSECT in Oracle and as you see from the output only numbers 3, 4, 5 and 6 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;

When both queries has no identical lines then the INTERSECT operator will return nothing as on the next example. The query above has column value number 2 and the query below has value 3. Both queries have only one line and the output stays empty.

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

Important To Know:

Oracle INTERSECT compares the types per value and not by their outlook. For example number 2 in math equal 2.0. Even the look is a bit different; while they are declared as numbers the value remains the same and the INTERSECT operator treats them as the same value. Take a look at the following example.

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

As you see the output above number 2 is returned by the operator.

See Also:
Oracle Home