What is the difference between UNION and UNIONALL?
Answers were Sorted based on User's Feedback
Answer / valarmathi
union is used to retreive the data with out duplication
union all is used to retreive the data with duplication
Is This Answer Correct ? | 18 Yes | 0 No |
Answer / suresh
UNION statement effectively does a SELECT DISTINCT on the
results set. If you know that all the records returned are
unique from your union, use UNION ALL instead, it gives
faster results.
Is This Answer Correct ? | 13 Yes | 1 No |
Answer / haroon nazir.s
UNION
The UNION command is used to select related information from
two tables, much like the JOIN command. However, when using
the UNION command all selected columns need to be of the
same data type. With UNION, only distinct values are selected.
UNION ALL
The UNION ALL command is equal to the UNION command, except
that UNION ALL selects all values.
The difference between Union and Union all is that Union all
will not eliminate duplicate rows, instead it just pulls all
rows from all tables fitting your query specifics and
combines them into a table.
A UNION statement effectively does a SELECT DISTINCT on the
results set. If you know that all the records returned are
unique from your union, use UNION ALL instead, it gives
faster results.
Example:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth
Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will
remove duplicate values)
UNION ALL:
First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth
(This will repeat values)
Is This Answer Correct ? | 5 Yes | 1 No |
Answer / subramanya gv
Union is used to combining the data with out duplication and
effectively it is faster.
Union all is used to combining the data with duplication so
comparatively it is slower.
Is This Answer Correct ? | 5 Yes | 3 No |
Answer / amit dixit
Just Above is OK except UNION ALL result modified..
Example:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth
Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will
remove duplicate values)
UNION ALL:
First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth
(This will repeat values)
Is This Answer Correct ? | 2 Yes | 0 No |
What is the purpose of forms?
How to execute stored procedure and set temp table in sql server?
What is default port number for sql server 2000?
Difference between server.transfer and server.execute method?
How to set the current database in ms sql server?
According to you what goes into making the best database administrator? : sql server database administration
Can you explain what is the use of custom fields in report?
how can you attach more than 20 ldf files in sql server
Define outer join?
What is the guest user account in sql server? What login is it mapped to it? : sql server security
Explain couple pf features of SQL server
After recovering the database by using tail log backup.How to verify the data has been sucessfully restored or not.?