UNION vs UNION ALL: Set Operators in Oracle SQL (Basic 1 - 18)

UNION and UNION ALL are set operators used to combine the results of two or more queries into a single result set. However, there is one key difference between the two operators: UNION removes duplicate rows from the result set, while UNION ALL includes all rows, even duplicates.

UNION

The UNION operator combines the results of two or more queries into a single result set, eliminating duplicate rows. The following are some of the key points to consider when using the UNION operator:

• The number and order of the columns in each query must be the same.
• The data types of the corresponding columns in each query must be compatible.

UNION ALL

The UNION ALL operator is similar to the UNION operator, but it does not remove duplicate rows from the result set. This means that all rows from all of the queries in the UNION ALL statement will be included in the result set, including duplicates.

Example

The following example shows the difference between the UNION and UNION ALL operators:

SQL

-- Table 1
CREATE TABLE table_1 (
  id INT,
  name VARCHAR(255)
);

INSERT INTO table_1 VALUES (1, 'John Doe');
INSERT INTO table_1 VALUES (2, 'Jane Doe');
INSERT INTO table_1 VALUES (3, 'Peter Jones');

-- Table 2
CREATE TABLE table_2 (
  id INT,
  name VARCHAR(255)
);

INSERT INTO table_2 VALUES (2, 'Jane Doe');
INSERT INTO table_2 VALUES (3, 'Peter Jones');
INSERT INTO table_2 VALUES (4, 'Susan Williams');

-- UNION
SELECT * FROM table_1 UNION SELECT * FROM table_2;

-- Output:
-- ID | NAME
-- --- | ----
-- 1  | John Doe
-- 2  | Jane Doe
-- 3  | Peter Jones
-- 4  | Susan Williams

-- UNION ALL
SELECT * FROM table_1 UNION ALL SELECT * FROM table_2;

-- Output:
-- ID | NAME
-- --- | ----
-- 1  | John Doe
-- 2  | Jane Doe
-- 2  | Jane Doe
-- 3  | Peter Jones
-- 3  | Peter Jones
-- 4  | Susan Williams

When to use UNION and UNION ALL

The UNION operator should be used when you want to combine the results of two or more queries into a single result set, removing duplicate rows. The UNION ALL operator should be used when you want to combine the results of two or more queries into a single result set, including duplicate rows.

Performance Considerations

The UNION operator is generally slower than the UNION ALL operator, because the database must do additional work to remove duplicate rows from the result set. However, the performance difference is usually negligible for small datasets.

For large datasets, it is recommended to use the UNION ALL operator if you do not need to remove duplicate rows from the result set. This will improve the performance of your query.

Conclusion

The UNION and UNION ALL operators are powerful tools for combining the results of two or more queries into a single result set. By understanding the difference between the two operators, you can choose the right operator for your needs.