Union,Except/Minus and Intersect

Pre-requirement to perform set operator's "UNION","EXCEPT or MINUS" and "INTERSECT" is to make sure all the tables in the script have identical table/column structures. 

 

UNION/UNION ALL: 

UNION All outputs all the records from table1 and table2 whereas UNION outputs unique records i.e. if there are any duplicate records they will get eliminated.

 

SQL Server

 

UNION Example:

 

 ;WITH TABLE1 AS

 (

 SELECT 1 AS COL1

 UNION ALL

 SELECT 1 AS COL1

 UNION ALL

 SELECT 2 AS COL1

                        )

 , TABLE2 AS

 (

 SELECT 2 AS COL2

                        )

 SELECT

COL1

 FROM TABLE1

 UNION

 SELECT

COL2

FROM TABLE2;

 

Above we are creating 2 CTE tables "table1" and "table2" and performing union operation between table1 and table2, the output of the above query is "1,2", Note: the output is not "1,1,2", as we can see it is even removing duplicates from table1

 

UNION ALL Example:

 

 ;WITH TABLE1 AS

 (

 SELECT 1 AS COL1

 UNION ALL

 SELECT 1 AS COL1

 UNION ALL

 SELECT 2 AS COL1

 )

 , TABLE2 AS

 (

 SELECT 2 AS COL2

 )

 SELECT

COL1

FROM TABLE1

 UNION ALL

 SELECT

COL2

FROM TABLE2

 

The output of above script is 1,1,2,2 and it brings all the records from both table1 and table2 and provides as output. 

 

Oracle 

UNION ALL AND UNION functions works same way in both sql server and oracle, the above examples cannot be executed in oracle, to run it in oracle we need to use "from dual" for dummy records and ";" is not needed in front of CTE as Oracle terminates every command with a semicolon

 

So above example to work in oracle it should be

 

WITH TABLE1 AS

 (

 SELECT

1 AS COL1

FROM DUAL

 UNION ALL

 SELECT

1 AS COL1

FROM DUAL

 UNION ALL

 SELECT

2 AS COL1

FROM DUAL

 )

,TABLE2 AS

 (

 SELECT

2 AS COL2

FROM DUAL

 )

 SELECT

COL1

FROM TABLE1

 UNION ALL

 SELECT

COL2

FROM TABLE2;

 

EXCEPT/MINUS: 

Output all the records which exists in first table and does not exist in second table, In SQL Server they call the function as EXCEPT and in oracle it is MINUS.

 

So let’s take the same example as above

 

 ;WITH TABLE1 AS  -- FOR ORACLE REMOVE SEMICOLON

 (

 SELECT 1 AS COL1 --FOR ORACLE ADD FROM DUAL

 UNION ALL

 SELECT 1 AS COL1 --FOR ORACLE ADD FROM DUAL

 UNION ALL

 SELECT 2 AS COL1 --FOR ORACLE ADD FROM DUAL

 )

,TABLE2 AS

 (

 SELECT 2 AS COL2 --FOR ORACLE ADD FROM DUAL

 )

 SELECT

COL1

 FROM TABLE1

 EXCEPT --FOR ORACLE CHANGE IT TO MINUS

 SELECT

COL2

FROM TABLE2;

 

When we run the above script will output 1, Note: even EXCEPT/MINUS eliminates duplicates

 

INTERSECT:

 

Output’s all the records which exists in both the tables, isn't inner join does the same job, yes but if we want achieve the same using inner join we have to join on each and every column.

 

So getting back to our example

 

;WITH TABLE1 AS  -- FOR ORACLE REMOVE SEMICOLON

 (

 SELECT 1 AS COL1 --FOR ORACLE ADD FROM DUAL

 UNION ALL

 SELECT 1 AS COL1 --FOR ORACLE ADD FROM DUAL

 UNION ALL

 SELECT 2 AS COL1 --FOR ORACLE ADD FROM DUAL

 )

,TABLE2 AS

 (

 SELECT 2 AS COL2 --FOR ORACLE ADD FROM DUAL

 )

 SELECT

COL1

FROM TABLE1

 INTERSECT 

 SELECT

COL2

FROM TABLE2;

 

 The output when we run the above script will be 2, Note: even INTERSECT eliminates duplicates.

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.