What is the difference between UNION and UNIONALL?
Answer Posted / 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 |
Post New Answer View All Answers
Explain what is it unwise to create wide clustered index keys?
What is the web service used for reporting services?
What is dbcc? Give few examples.
Do you know what are acid properties?
List out a number of the wants to setup a SQL Server failover cluster?
Why use “pivot” in sql server?
What is a trigger in sql server?
what are constraints? Explain different types of constraints? : Sql server database administration
Is there any performance difference between if exists (select null from table) and if exists (select 1 from table)?
How ssrs maintain security?
Describe in brief sql server monitoring ways.
Can we use max in where clause?
What is a transact-sql statement?
What happens when converting big values to integers?
can a database be shrunk to 0 bytes, if not, why? : Sql server administration