# Thread: Difference between union and union all

1. ## Difference between union and union all

What is the difference between UNION and UNION ALL? Which one is preferred and why ?

2. ## Re: Difference between union and union all

Hi RSP,

UNION is used to select distinct values from two tables.

UNION ALL is used to select all values (including duplicates) from the tables.

Regards,
Karthi

3. ## Re: Difference between union and union all

Originally Posted by rsp2709
What is the difference between UNION and UNION ALL? Which one is preferred and why ?
Union selects only unique row (data) from all queries.
Union all selects all rows from all queries.

Example.

table1 has row (1,'Test')
table2 has row (1,'Test')

Union gives output
1,'Test'

Union all gives output
1,'Test'
1,'Test'

4. ## Re: Difference between union and union all

UNION removes duplicate rows from multiple queries.

UNION ALL shows all the rows from multiple queries.

5. ## Re: Difference between union and union all

As UNION removes duplicate rows, there is a sort involved, adding a
potentially big overhead to the process.

6. ## Re: Difference between union and union all

Hi RSP,

As in the above discussions

UNION is used to fetch the data from the two select queries with no duplication of data, where as UNION ALL fetches the data including the duplication.

7. ## Re: Difference between union and union all

UNION and UNION ALL are SET operators that are used for two different queries.

UNION will fetch the DISTINCT set of records returned by both the queries

UNION ALL will fetch all the records returned by both the queries.

The concept of UNION is similar to mathematics UNION Venn Diagrams (A U B)

Eg:

Code:
```select * from emp
UNION
select * from emp1
/

select * from emp
UNION ALL
select * from emp1

In the above queries the number of columns and its datatypes should be same```

8. ## Re: Difference between union and union all

union all is faster than union, as union involve sorting operation to find distinct records.

9. ## Re: Difference between union and union all

Very informative thread, this is one of popular interview question on SQL.

10.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•