---
title: "Why Oracle UNION ALL not returning full set?"  
description: "Why Oracle UNION ALL not returning full set?"  
author: "Steilla Mitchel"  
published: 2023-07-30  
updated: 2023-07-31  
canonical: https://www.mindstick.com/forum/159371/why-oracle-union-all-not-returning-full-set  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 2 minutes  

---

# Why Oracle UNION ALL not returning full set?

Why is [Oracle](https://www.mindstick.com/articles/13016/alter-table-statement-or-command-in-oracle) [UNION ALL](https://www.mindstick.com/blog/11268/union-and-union-all-operator-in-sql) not [returning](https://www.mindstick.com/news/2477/returning-ceo-bob-iger-dismisses-the-disney-apple-sale-as-pure-speculation) the [full](https://www.mindstick.com/articles/12893/experience-the-full-power-of-suitecrm) set?

## Replies

### Reply by Aryan Kumar

There are a few reasons why Oracle [UNION](https://www.mindstick.com/articles/1510/union-example-in-sql-server) ALL might not be returning the full set of rows. Here are some of the most common reasons:

- The `UNION ALL` operator does not eliminate duplicate rows. If there are duplicate rows in the two tables that are being `UNION ALL`ed, then the `UNION ALL` operator will return all of the rows, including the duplicates.
- The `WHERE` clause in the `UNION ALL` statement might be filtering out some of the rows. If the `WHERE` clause is too restrictive, then it might be filtering out all of the rows from one of the tables.
- The `ORDER BY` clause in the `UNION ALL` statement might be sorting the rows in a way that is not returning all of the rows. If the `ORDER BY` clause is sorting the rows by a column that does not have any unique values, then it might be possible for the `UNION ALL` statement to return duplicate rows.

If you are not sure why the `UNION ALL` operator is not returning the full set of rows, you can use the `EXPLAIN PLAN` statement to see the execution plan for the query. The execution plan will show you how the query is being executed and it will help you to identify any potential problems.

Here are some additional tips for troubleshooting `UNION ALL` queries:

- Use the `DISTINCT` keyword to eliminate duplicate rows.
- Use the `WHERE` clause to filter out rows.
- Use the `ORDER BY` clause to sort the rows in a way that will not return duplicate rows.
- Use the `EXPLAIN PLAN` statement to see the execution plan for the query.


---

Original Source: https://www.mindstick.com/forum/159371/why-oracle-union-all-not-returning-full-set

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.