The difference between union and union all in oracle

The difference between UNION and UNION ALL in Oracle is the way duplicate rows are handled. UNION removes duplicate rows and returns only distinct rows; UNION ALL retains all rows, including duplicates.

The difference between UNION and UNION ALL in Oracle

Essential difference:

UNION and UNION ALL are query operations used in Oracle to merge rows from two or more tables. The main difference is how they handle duplicate rows.

Duplicate rows:

• UNION: Remove duplicate rows and return only distinct rows.
• UNION ALL: Keep all rows, including duplicate rows.

Syntax:

<code class="sql">SELECT ...
FROM table1
UNION
SELECT ...
FROM table2;

SELECT ...
FROM table1
UNION ALL
SELECT ...
FROM table2;</code>

Use example:

UNION:

• When you need to merge unique data in different tables.
• Prevent duplicate data from appearing in the merged results.

UNION ALL:

• When all data needs to be merged, including duplicate rows.
• Keep all result rows, including duplicate rows.

Performance:

UNION generally performs worse than UNION ALL because it requires additional steps to remove duplicate rows. However, if you need to ensure that there are no duplicate rows in the result, UNION is a better choice.

Example:

Suppose there are two tables: customers and orders.

If we merge these two tables using UNION, duplicate rows will be removed:

<code class="sql">SELECT *
FROM customers
UNION
SELECT *
FROM orders;</code>

whereas UNION ALL will keep all rows, including duplicate rows:

<code class="sql">SELECT *
FROM customers
UNION ALL
SELECT *
FROM orders;</code>

The above is the detailed content of The difference between union and union all in oracle. For more information, please follow other related articles on the PHP Chinese website!