UNION (ALL), INTERSECT, MINUS Operators
The UNION operator is useful when you want to draw information from two or more tables that all have the same structure.
Take for example two tables AUSTRALIA_CRICKET containing a few players from the Australian Cricket Team and KOLKATA_CRICKET containing a few players from the IPL Kolkata Cricket Team.
SQL> SELECT * FROM AUSTRALIA_CRICKET; FIRST_NAME LAST_NAME -------------------- -------------------- Ricky Ponting Michael Clarke Stuart Clark Brad Haddin Brett Lee 5 rows selected. SQL> SELECT * FROM KOLKATA_CRICKET; FIRST_NAME LAST_NAME -------------------- -------------------- Sourav Ganguly Shoaib Akhtar Ricky Ponting Brendon McCullum Chris Gayle 5 rows selected.
You can see from the results that Ricky Ponting is a player common to both tables.
To return data from both these tables, you could write:
SQL> SELECT * FROM AUSTRALIA_CRICKET 2 UNION 3 SELECT * FROM KOLKATA_CRICKET; FIRST_NAME LAST_NAME ------------------------------ ----------------------- Brad Haddin Brendon McCullum Brett Lee Chris Gayle Michael Clarke Ricky Ponting Shoaib Akhtar Sourav Ganguly Stuart Clark 9 rows selected.
You'll note Ricky Ponting was returned only once using the UNION syntax.
This is what happens if you change the UNION to UNION ALL:
SQL> SELECT * FROM AUSTRALIA_CRICKET 2 UNION ALL 3 SELECT * FROM KOLKATA_CRICKET; FIRST_NAME LAST_NAME -------------------- -------------------- Ricky Ponting Michael Clarke Stuart Clark Brad Haddin Brett Lee Sourav Ganguly Shoaib Akhtar Ricky Ponting Brendon McCullum Chris Gayle 10 rows selected.
This time Ricky Ponting appears twice. Once for each table.
If you want only the rows which appear in all/both tables, you would use the INTERSECT operation:
SQL> SELECT * FROM AUSTRALIA_CRICKET 2 INTERSECT 3 SELECT * FROM KOLKATA_CRICKET; FIRST_NAME LAST_NAME -------------------- -------------------- Ricky Ponting 1 row selected.
To round things off, if you wish to return data from the first table only, excluding data common between the two tables, you would use the MINUS operator:
SQL> SELECT * FROM AUSTRALIA_CRICKET 2 MINUS 3 SELECT * FROM KOLKATA_CRICKET; FIRST_NAME LAST_NAME -------------------- -------------------- Brad Haddin Brett Lee Michael Clarke Stuart Clark 4 rows selected.
Or ... in Reverse:
SQL> SELECT * FROM KOLKATA_CRICKET 2 MINUS 3 SELECT * FROM AUSTRALIA_CRICKET; FIRST_NAME LAST_NAME -------------------- -------------------- Brendon McCullum Chris Gayle Shoaib Akhtar Sourav Ganguly 4 rows selected.
