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.