Wednesday, March 21, 2007

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

2 comments:

Nabeel said...

are you talking about SQL plus? Is it Oracle 10g that you are using? Well I guess the code applies to all ..

shaheel said...

Kolkata Web Design Company
One of the leading web design company of Kolkata, Acesoftech has been actively involved in web design, web development and Search Engine Optimization services. The company provides you affordable web site design, web site development and Search Engine Optimization services. While there is no chance of compromising with the quality, our prices are most competitive.