What is the difference between UNION and UNIONALL?

Answers were Sorted based on User's Feedback



What is the difference between UNION and UNIONALL?..

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

What is the difference between UNION and UNIONALL?..

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

What is the difference between UNION and UNIONALL?..

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

What is the difference between UNION and UNIONALL?..

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

What is the difference between UNION and UNIONALL?..

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

Post New Answer

More SQL Server Interview Questions

your sql server is running out of disk space. You notice that there are several large files with ldf extensions what are these files? : Sql server administration

0 Answers  


Explain different backup plans?

0 Answers  


Mention a few common trace flags used with sql server?

0 Answers  


When is update_statistics command used?

0 Answers  


how to select a field with firstletter as capital and remaining are small letters

11 Answers  






What is Dependency Injection and provide example?

0 Answers  


What is merge statement?

0 Answers  


What is the purpose of update statistics and scope_identity() function?

0 Answers  


How to list all dsn entries on your local machine using odbc_data_source()?

0 Answers  


What are the disadvantages of using the stored procedures?

0 Answers  


How to set database to be single_user in ms sql server?

0 Answers  


Syntax to find the used space and free space of the Data file in a database(SQL Server). Following queries didn't give the exact Used space and Free Space Information sp_spaceused;DBCC showfilestats;exec MyDbName.dbo.sp_spaceused;SP_HELPFILE Can any one tell me the query for how to find the exact used data file space and free space in a Data File?

2 Answers   Cognizant,


Categories