Oracle's Venerable SQL operators

We are gonna discuss the UNION,UNION ALL, INTERSECT and MINUS operators in SQL of Oracle.

To exemplify these operators, create two tables, 'A' and 'B',

create table A (C1 NUMBER(12),C2 VARCHAR2(50),C3 NUMBER(12));

INSERT INTO A VALUES (1,'AAA',100);
INSERT INTO A VALUES (1,'AAB',100);
INSERT INTO A VALUES (1,'AAC',100);


create table B (C1 NUMBER(12),C2 VARCHAR2(50),C3 NUMBER(12));

INSERT INTO B VALUES (1,'AAE',100);
INSERT INTO B VALUES (1,'AAB',100);
INSERT INTO B VALUES (1,'AAD',100);


Union:

If we apply 'UNION' operator on two tables, then it returns all rows from both tables but show the duplicate records only once.

SQL> SELECT * FROM A
2 UNION
3 SELECT * FROM B;

C1 C2 C3
---------- ---- ----------
1 AAA 100
1 AAB 100
1 AAC 100
1 AAD 100
1 AAE 100

5 rows selected.

Union All:

If we apply 'UNION ALL' operator on two tables, then it returns all rows from both tables including the duplicate rows.

SQL> SELECT * FROM A
2 UNION ALL
3 SELECT * FROM B;

C1 C2 C3
---------- ---- ----------
1 AAB 100
1 AAA 100
1 AAC 100
1 AAE 100
1 AAB 100
1 AAD 100

6 rows selected.


Intersect:

If we apply 'INTERSECT' operator on two tables, then it returns only the
same rows in both tables.


SQL> SELECT * FROM A
2 INTERSECT
3 SELECT * FROM B;

C1 C2 C3
---------- ---- ----------
1 AAB 100


1 row selected.

Minus:

If we apply 'MINUS' operator on two tables, then it returns rows which are present in first table but not in the second table.

SQL> SELECT * FROM A
2 MINUS
3 SELECT * FROM B;

C1 C2 C3
---------- ---- ----------
1 AAA 100
1 AAC 100