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;
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;
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;
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;
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;
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