1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

What is the difference between UNION and UNIONALL?

Question Posted / haroon nazir.s

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


Please Help Members By Posting Answers For Below Questions

How to concatenate two binary strings together?

617

is there a column to which a default can't be bound? : Sql server database administration

541

How to delete duplicate rows from table except one?

546

How to use clusters?

555

Define union, union all, minus, intersect?

573




You want to use bids to deploy a report to a different server than the one you chose in the report wizard. How can you change the server url?

110

What is sub query and its properties?

549

what is raid? : Sql server database administration

559

How to select some specific rows from a table in ms sql server?

574

What is unique key constraint?

634

How to create a store procedure with encryption?

527

When would you prefer to have a minimum number of indexes?

509

How to use subqueries in the from clause in ms sql server?

571

How many database files are there in sql server 2000?what are they?

583

What are truncate options available in sql server? : sql server database administration

585