This article is completely re-written with better example SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. I suggest all of my readers to go here for update article.
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)
Reference : Pinal Dave (https://blog.sqlauthority.com)
190 Comments. Leave new
I got two tables with different number of fields.. is there any way I can ‘append’ the second table to the first table
for example:
table1 has the following records:
abc def ghi
jkl mn op
table2 has
1 2
3 4
I want my new table to look like this:
abc def ghi
jkl mn op
1 2
3 4
please help!
thanks
Hi Mike,
I have tricky solution. You can do this by this way:
DECLARE @Table1 TABLE(A VARCHAR(10), B VARCHAR(10), C VARCHAR(10))
INSERT INTO @Table1(A, B, C)
SELECT ‘abc’, ‘def’, ‘ghi’
UNION
SELECT ‘jkl’, ‘mn’, ‘op’
DECLARE @Table2 TABLE(A INT, B INT)
INSERT INTO @Table2(A, B)
SELECT 1, 2
UNION
SELECT 3, 4
SELECT A,
B,
C
FROM @Table1
UNION ALL
SELECT CAST(A AS VARCHAR),
CAST(B AS VARCHAR),
” AS C
FROM @Table2
What I did is, Added blank column on Table2, so I can use UNION ALL.
Let us know if it helps you.
Thanks,
Tejas
SQLYoga.com
Hello,
Thanks for the reply. Is there any way I can do it dynamic. Meaning, the number of columns in both tables can differ.
So there are 3 possbilities:
1. TableA has the same number of columns as tableB
2. TableA has more columns than tableb
3. TableA has less columns as tableB
Thanks in advance
Good atricle
I have tried this example.
But the result of Union All is showing 9 records while here it showing 10 records
straight and simple good article……
Hi everybody,
My question is : Is there any alternate for UNION ALL..
i.e.. I want to fetch a record from one table and one more record from other table without using the UNION ALL.
I hope you got my query..
Plzz help me out..
@praveen goud
UNION ALL is probably best. However, if both TABLEs will only return one record, and if you want that record on the same line, you can just join them:
WITH
A(A) AS (SELECT 1 UNION ALL SELECT 2),
B(B) AS (SELECT 1 UNION ALL SELECT 2)
SELECT A.A, B.B FROM A, B WHERE A.A = 1 AND B.B = 2;
Note, this only works when both will return only one record.
Hi..
Thanx for the reply..
I need to fetch only one record from the first table and more than a record from the second one..
Is there any other way to acheive this without using UNION ALL ..
kindly suggest ..
Hi Pinal Dave
I am trying use UNION ALL in Stored Procedure but i am not getting result what is the problem please explain. My code is
create procedure Usp_UserCompanyPersonalAddresses
(
@UserAddressId nvarchar(255),
@UserCompanyAddressId nvarchar(255)
)
as
begin
select u_address_name as AddressName,
(u_first_name+u_last_name) as [Name],
u_address_line1 as Address1,
u_address_line2 as Address2,
u_city as City,
u_region_code as [State],
u_country_name as Country,
u_postal_code as PostalCode,
u_tel_number as TelephoneNumber,
u_tel_extension as TeleExtention
from addresses where u_address_id in(@UserAddressId)
union all
select u_Companyaddress_name as AddressName,
u_Companyaddress_name as [Name],
u_address_line1 as Address1,
u_address_line2 as Address2,
u_city as City,
u_region_name as [State],
u_country_name as Country,
u_postal_code as PostalCode,
u_tel_number as TelephoneNumber,
u_tel_extension as TeleExtention
from companyaddresses
where u_Companyaddress_id in(@UserCompanyAddressId)
end
@Thirmal Reddy
Is there an error? Does the query work when you run it directly?
Are you passing comma seperated values to the parameters?
Ya i am passing with comma separated value
Hi Madhivanan
Ya i am passing with comma separated value
My Variable is :
string strValue=”‘{xxx}’,'{yyy}’,'{zzz}'”;
sqlCmd.paramerers.Add(“@ UserCompanyAddressId”,SqlDataType.NvarChar,255).value=strValue
Like This i am passing
Ya i am passing comma separated values
No it is not giving Error but I am getting records
This is also one problem i am passing the Parameter from Front end(.Net). i am not getting records. is There any restriction on WHERE IN While using it in Stored Procedure?
if I write the Inline Query it is working fine
CREATE procedure Usp_UserPersonalAddresses
(
@UserAddressId nvarchar(max)
)
as
begin
select
u_address_id as AddressId,
u_address_name as AddressName,
u_first_name as [Name],
u_address_line1 as Address1,
u_address_line2 as Address2,
u_city as City,
u_region_code as [State],
u_country_name as Country,
u_postal_code as PostalCode,
u_tel_number as TelephoneNumber,
u_tel_extension as TeleExtention
from addresses where u_address_id in(@UserAddressId)
end
How are you passing values from .NET?
Hi Madhivanan
Ya i am passing with comma separated value
My Variable is :
string strValue=”‘{xxx}’,'{yyy}’,'{zzz}’”;
sqlCmd.paramerers.Add(“@ UserCompanyAddressId”,SqlDataType.NvarChar,255).value=strValue
Like This i am passing
One more difference between union and union all if the datatype of column is text
while using union and unoin all
wht are the major requirement on both the table
if column have different data types then , is it possible to use union and union all or in case of join will it work
plz let me clear it
thanx
Dear Pinal,
This to subscribe
Hi Pinal
it is very useful websit on MS SQL server,
i need a clarification from you, while I am going through the properties of relational table you mentioned a word ATOMIC,what is this world specified can you eloberate please
Thanks for a good knowledge
Hi All,
I need a help that can i join two table which dont have reference between both table.
Suppose: table 1 having column:
ID
Name
Table 2 having column:
Phone
Address
How can we join these table so that data can show in below form:
Table:
ID NAme Phone Address
Regrds,
Sanjay
you can do by
select a.*, b.* from table1 a, table2 b
but its full join and it will return you the result as
total number of records in to table1*total numebr of records in table2
as there is nothing to match
how i can delete duplicate records from a table…….
Can anyone explain why when I did a UNION ALL the duplicates were deleted, but when I did UNION the duplicates were still there. I have seen it explained both ways, but more often that UNION ALL retains the duplicates.
this is really good soln