UNION and UNION ALL Operators in Oracle

UNION and UNION ALL operators are used to retrieve matching of non-matching records from two queries.

To see the difference between both let's create two tables as shown below:-

CREATE TABLE EMP
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(20)
);


CREATE TABLE EMP_2
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(20)
);

INSERT INTO EMP
VALUES (1,'CHANCHAL');

INSERT INTO EMP
VALUES (2,'WANKHADE');

INSERT INTO EMP_2
VALUES (1,'CHANCHAL');

INSERT INTO EMP_2
VALUES (2,'WANKHADE');

COMMIT;

1)  UNION operator is used to fetch only unique records from both the queries. UNION operator worries        about duplicate and hence slower than UNION ALL operator.

Syntax is:-

FIRST_SELECT_STATEMENT

UNION

SECOND_SELECT_STATEMENT;

Example is:-

SELECT * FROM EMP

UNION

SELECT * FROM EMP_2;

2) UNION ALL operator is used to fetch all the matching and non-matching records from both the queries.       UNION ALL operator does not worry about duplicate records and hence much faster than UNION           operator.

Syntax is:-

FIRST_SELECT_STATEMENT

UNION ALL

SECOND_SELECT_STATEMENT;

Example is:-

SELECT * FROM EMP

UNION ALL

SELECT * FROM EMP_2;

Here are some frequently asked questions about the UNION and UNION ALL operators in Oracle, along with their answers:

1. What is the purpose of the UNION and UNION ALL operators in Oracle?

   - Both the UNION and UNION ALL operators in Oracle are used to combine the result sets of two or more SELECT statements into a single result set. They are particularly useful when you want to combine data from multiple tables or queries.

2. What is the difference between UNION and UNION ALL in Oracle?

   - The main difference between UNION and UNION ALL is that UNION removes duplicate rows from the result set, while UNION ALL includes all rows, including duplicates. As a result, UNION ALL is typically faster than UNION because it doesn't need to perform the extra step of eliminating duplicates.

3. What is the syntax of the UNION operator in Oracle?

   - The syntax of the UNION operator in Oracle is as follows:

     

     SELECT column1, column2, ...

     FROM table1

     UNION

     SELECT column1, column2, ...

     FROM table2;

     

     This query combines the result sets of two SELECT statements from table1 and table2, removing duplicate rows.

4. What is the syntax of the UNION ALL operator in Oracle?

   - The syntax of the UNION ALL operator in Oracle is similar to UNION:

     

     SELECT column1, column2, ...

     FROM table1

     UNION ALL

     SELECT column1, column2, ...

     FROM table2;

     

     This query combines the result sets of two SELECT statements from table1 and table2, including all rows, including duplicates.

5. When should I use UNION and when should I use UNION ALL in Oracle?

   - Use UNION when you want to eliminate duplicate rows from the combined result set, and you are willing to pay the performance cost for doing so. Use UNION ALL when you want to include all rows, including duplicates, and you want better performance because it doesn't need to eliminate duplicates.

These questions and answers should provide a good understanding of the UNION and UNION ALL operators in Oracle.