-- The data from queries below can represent data from Table1 and Table2 

SELECT 1 idx FROM DUAL UNION      --Table1
SELECT 2 idx FROM DUAL            --Table1
MINUS 
SELECT 1 idx FROM DUAL UNION      --Table2
;

-- The output of the above query (MINUS syntax) will be 2
-- The MINUS is represents data in Table1 that is not in Table2
-- The EXCEPT also provides the same output as MINUS

SELECT 1 idx FROM DUAL UNION      --Table1
SELECT 2 idx FROM DUAL            --Table1
EXCEPT 
SELECT 1 idx FROM DUAL UNION      --Table2
;

-- Get common data between Table1 and Table2


SELECT 1 idx FROM DUAL UNION
SELECT 2 idx FROM DUAL
INTERSECT 
SELECT 1 idx FROM DUAL UNION
;

-- The output of the above query (INTERSECT syntax) will be 1
-- The INTERSECT is represents data in Table1 that is in Table2


Oracle UNION syntax

Oracle UNION ALL syntax


-- Fast way to perform NOT IN or NOT EXISTS using outer joins.  The  
-- performance between the two queries will be significant in 
-- database tables, with outer join (query-2) being much faster.

-- Query-1

SELECT qt_a.idx
FROM  
 (SELECT  10 as idx FROM DUAL UNION
  SELECT  20 as idx FROM DUAL UNION
  SELECT  30 as idx FROM DUAL UNION
  SELECT  40 as idx FROM DUAL UNION
  SELECT  50 as idx FROM DUAL)       qt_a
WHERE NOT EXISTS
 (SELECT  -1
  FROM  (SELECT  10 as idx FROM DUAL UNION
         SELECT  40 as idx FROM DUAL)       qt_b
  WHERE  qt_a.idx = qt_b.idx
 );



-- Query-2  (left join - to get missing data in Table B)

SELECT qt_a.idx                                    
FROM   (SELECT  10 as idx FROM DUAL UNION
        SELECT  20 as idx FROM DUAL UNION
        SELECT  30 as idx FROM DUAL UNION
        SELECT  40 as idx FROM DUAL UNION
        SELECT  50 as idx FROM DUAL)       qt_a
 LEFT JOIN 
       (SELECT  10 as idx FROM DUAL UNION
        SELECT  40 as idx FROM DUAL)       qt_b
 ON
WHERE   qt_b.idx IS NULL
;