ORACLE
ORACLE OPERATORS
UNION
UNION ALL
INTERSECT
MINUS
ORACLE TUTORIAL
Oracle Introduction
What Is Oracle
ORACLE TABLES
Create Tables
Create Table As
Alter Table
Drop Table
Global Temorary Tables
Local Temorary Tables
ORACLE VIEWS
Create View
ORACLE QUERIES
SELECT
INSERT
INSERT ALL
UPDATE
DELETE
TRUNCATE TABLE
ORACLE CLAUSES
DISTINCT
FROM
ORDER BY
GROUP BY
HAVING
ORACLE JOINS
INNER JOIN
OUTER JOIN
EQUI JOIN
SELF JOIN
CROSS JOIN
ANTI JOIN
SEMI JOIN
ORACLE ADVANCE
PROCEDURES
FUNCTION
CURSOR
TRIGGER

INTERSECT



Oracle - Intersect


The Oracle INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.


Syntax :

SELECT expression1, expression2, ... expression_n  
FROM table1  
WHERE conditions  
INTERSECT  
SELECT expression1, expression2, ... expression_n  
FROM table2  
WHERE conditions;  

Parameters :


  • expression1, expression2, ... expression_n: It specifies the columns that you want to retrieve.

  • table1, table2: It specifies the tables that you want to retrieve records from.

  • conditions: it specifies the conditions that must be fulfilled for the records to be selected.


    Oracle INTERSECT Example: (with single expression)


    Suppliers Table :

    SUPPLIER_IDSUPPLIER_NAMESUPPLIER_ADDRESS
    1Bata shoesAgra
    2KingfisherDelhi
    3VOJOLucknow
    4Apple-
    5Flipkart-

    Orders details Table :

    SUPPLIER_IDSUPPLY_DATESUPPLY_ADDRESS
    15-12-19Ahmedabad
    26-12-19Bangalore
    37-12-19Mumbai

    SELECT supplier_id  
    FROM suppliers  
    INTERSECT  
    SELECT supplier_id  
    FROM order_details;  
    

    In the above example, the supplier_id appears in both the suppliers and order_details table. Now the common entries will be returned in the result set.


    Output :

    SUPPLIER_ID
    1
    2
    3

    Oracle INTERSECT Example: (with multiple expressions)


    Supplier Table :

    SUPPLIER_IDFIRST_NAMELAST_NAME
    1dhirubhaiambani
    2kanchabhaimotwani
    3lallulal

    Customer Table :

    CUSTOMER_IDFIRST_NAMELAST_NAMEDEPARTMENT_ID
    1Aryantomar-
    2Ajeetmaurya-
    3richagoyal-
    4dhirubhaijotwani-
    32alexpandian1

    SELECT supplier_id, last_name, first_name  
    FROM supplier  
    WHERE first_name <> 'dhirubhai'  
    INTERSECT  
    SELECT customer_id, last_name, first_name  
    FROM customer  
    WHERE customer_id < 5;  
    

    Output :
    First_nameLast_name
    mauryaajeet

    The above example returns the records from the supplier table where the supplier_id, last_name and first_name values match the customer_id, last_name, and first_name value of customer table.



    What Is Oracle

    What Is Oracle

    posted on 2019-11-29 01:11:21 - ORACLE Tutorials


    TRIGGER

    ORACLE - Trigger

    posted on 2019-11-28 22:14:22 - ORACLE Tutorials


    CURSOR

    ORACLE - Cursor

    posted on 2019-11-28 22:13:54 - ORACLE Tutorials


    MySQL TRIGGERS

    MySQL - Triggers

    posted on 2019-11-29 21:44:07 - mysql Tutorials


    Grant_ Revoke Privilege

    MySQL - Grant_ Revoke Privilege

    posted on 2019-11-26 23:15:04 - mysql Tutorials


    MySQL Vs SQL

    MySQL Vs SQL

    posted on 2019-11-25 05:02:26 - mysql Tutorials