How To Use INTERSECT Operator In Oracle

How To Use INTERSECT Operator In Oracle

July 07, 2020
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle intersect oracle intersect in oracle the stock market plumbing

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;

dba oracle dba database management system oracle performance tuning oracle intersect oracle intersect in oracle the stock market plumbing

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;

dba oracle dba database management system oracle performance tuning oracle intersect oracle intersect in oracle the stock market plumbing

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;

dba oracle dba database management system oracle performance tuning oracle intersect oracle intersect in oracle the stock market plumbing

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;

dba oracle dba database management system oracle performance tuning oracle intersect oracle intersect in oracle the stock market plumbing

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

See Also:
Oracle Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*