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 (http://blog.SQLAuthority.com)
Very good for understanding difference between UNION Vs UNION ALL
Union command does 2 things
1) It works like distinct clause ie it removes duplicate rows also it shows the rows which are unique in a particular table.
eg if Table A has rows with values 1,2,3 and Table B has rows with values 4,2,3
the Union table A and table B would give result
1,2,3,4
Union:1,2,3,4…
That is Correct Sham. Union works like Distinct. Union all DOES NOT do distinct.
Hi,
I need some more clarification on this, Table A has 1,2,3,4 and table B has 4,2,3 and it will display 1,2,3,4. ok up to this fine.
My question is:
2,3 are available in both tables , can you please tell me which table’s data will be the 2 and 3 whether it is from table A or from table B.
please explain, I gave all the answers above , again the interviewer twisted with this, kindly give a example
this is really a good article to understand but can u give this example with a example. This will be the good for all the people to understand the concept very clearly.
Thanks………..
If you have two tables.
First table : 1,2,3,4,5
Second table : 1,2,5,6
Union All:
1,2,2,3,4,5,5,6
Union:
1,2,3,4,5,6
I hope that clears a bit. I will re-write the articles soon.
Thanks for your question.
Regards,
Pinal Dave
(http://www.SQLAuthority.com)
Hi Pinal,
If you have two tables.
First table : 1,2,3,4,5
Second table : 1,2,5,6
for Union All:
1,1,2,2,3,4,5,5,6
but you said 1,2,2,3,4,5,5,6
???
@manu
That’s a typo, What you understood is right.
~IM.
Another example, which this article helped me troubleshoot today:
First Table: 1,2,2,3
Second Table: 4,5
Union All:
1,2,2,3,4,5
Union:
1,2,3,4,5
I suppose I understood that union would remove duplicates between the two tables, but I didn’t realize that it would also remove the EXISTING duplicates (which I wanted to keep!).
declare @t1 table(i int)
insert into @t1 select 1 union all select 2 union all select 2 union all select 3
declare @t2 table(i int)
insert into @t2 select 3 union select 1 union select 4 union select 5
select * from @t1 as t
where exists(
select i from @t1
where i=t.i
group by i having count(*)>1
)
union all
(
select * from @t1 as t
where exists(
select i from @t1
where i=t.i
group by i having count(*)=1
)
union
select i from @t2
)
Hello artesvida,
I am glad that it was useful.
Kind Regards,
Pinal Dave (http://www.SQLAuthority.com)
Hello sir,
If i have a 1000 records in a pdf or word or notepad format. All my 1000 records having just 3 fields like eno,ename,esalary.
I also created table with same field in SQL 2005. By using only one single query i should insert all my 1000 records into the SQL.
Please send me the query for that.
always union all have the better performance.
Hi all,
I want to ask if i can use union ineasted of join,please show examples
No. Both have different goals
Hi Pin ,
With the help of your statement about UNION and UNION ALL , I ve learnt interesting matters of difference between them .
Thanks a lot.
Regards,
Wilson Gunanithi . J
Great post…. very easy to understand….keep up the work…
Hi all,
How to eliminate the duplicate rows using union all functions.
Why are you using union all when union does what you need?
Hi,
Really it was help full for me.
Thnx
Its really good article to understand… the diff. between both UNION and UNION ALL..:)
Hi,
Its realy helpful for me.
Thanks
Very good for understanding difference between UNION Vs UNION ALL.
I work as a software Engineer in CFCS. I develop more than 10 web site through ASP and SQL.
Ravi Ranjan
hi pinaldave, this is very useful informaton for us
Good explanation….
I have got one problem… I would appreciate if any one help me out solve the problem…
Here is my requirement. I have to write a store procedure which returns a table like below.
create procedure temp
AS
BEGIN
if(condition1)
BEGIN
sql statement1
END
if(condition2)
BEGIN
sql statement2
END
END
Now i have to combined these two tables and have to return. How can i use UNION or UNION all here in this case???
I hope the requirement is clear…
hi
super explaination i understand very clearly
EXCELLENT THANKS VERY MUCH
Hi Suresh,
For your example of having to effectively union two statements that are nested in different IF statements, you may want to use a temp table / table variable.
if(condition1)
BEGIN
INSERT INTO #MyTable
SELECT * FROM Table1
END
if(condition2)
INSERT INTO #MyTable
SELECT * FROM Table2
END
SELECT * FROM #MyTable
In the above example, you would need to explicitly decide if you wanted to do a simple SELECT (UNION ALL) or SELECT DISTINCT (UNION).
Also, you should carefully choose to use a temp table (#tmp) or table variable (@tmp). There are a number of differences, but basically, variable are better for small resultsets and temp tables better for large. When using a variable table, you lose all parallelism, so inserting into the table can take significantly longer.
Great!!!! Keep It Up!!!
I would like to work with u….
If u can recognize me i am vaibhav pathak…….
I like ur article for difference beteen Union and Union All
You have selected me but the boss of ur company had no faith on me thats why i did not come to ur company on 15th jan 2005………
can u tell me what is ‘sysobject’ in Sql Server 2005 ??
Regards Vaibhav
I would like to work with u…. If u allow !!!!!!!!
If u can recognize me i am vaibhav pathak……. I have acd interview taken by u.
I like ur article for difference beteen Union and Union All
You have selected me but the boss of ur company had no faith on me thats why i did not come to ur company on 15th jan 2008………
dear all,
union all is always best performance
thx its good
Dear Pinal Dave,
Pls send me ms sqldba certification question & answere
Reagrds
Adhi
[...] Hint : This puzzle is based on my previous article SQL SERVER – Union vs. Union All – Which is better for performance? [...]
Nice article. Keep it up Pinal
I read many question from that site.
Every one was good and very near to the target.
So I really love that site and now always go through that.
Hello,
I have a challange here Im hoping someone could help… I got two tables with identical schema that need to be “glued” together, but the duplicate records must be truncated under certain condiction:
For example, if the PK field is identical in both tables, I would want the actDate to be evaluated and the one with higher value to be selected during the UNION process… how can I accomplish this?
Thanks a whole bunch in advance!!
hi Pinal
Good article
i have a doubt
what if if the table contains a Null value
can u give example in this regard
With thanks
sree
NULL will be omitted
If I have use a UNION ALL with two selects why does the order change instead of returning the results in natural order? For example if I have SELECT 0 As SortOrder FROM XXX UNION ALL SELECT 1 As SortOrder FROM AAA the 1 will be the first record instead of the second (some times)? If I add a Sort Order to the entire SQL Statement then my query slows down to much since it is buried in a bunch of other queries.
For versions prior to 2005 this happens
For version 2005 onwards natural order is garuanteed
Hi Pinal,
The article of Union v/s Union ALL was fantastic, it clears the doubts of difference between UNION AND UNION ALL.
My only qus is : Does Union ALL also shows repeated columns (duplicates) where the datatype is common, which is the case in UNION clause.
Hi Pinal,
I am a beginer in SQL and have a query and finding difficult solving this. Can you please help me.
[Northwind] List the CompanyName, ContactName, City, Region, PostalCode, Country, and Phone of all Customers who have ordered product ‘Tofu’ and of the Supplier of that product. Use UNION. Show all output rows.
Thanks
Zainab
Thanks Pinal,
Union and Union All descreption in your words is very straight-forward and easy to understand.
pinaldave,
Is the any difference in UNION and UNION ALL sorting order?
As in case of UNION SQL Server build temporay indexes on all the columns.
Thanks
Muhammad Wasim
correction!!
As in case of UNION SQL Server build temporay index (not indexes) on all the columns.
Nice article to understand the difference b/w UNION and UNION ALL.
Keep it up :)
Very comprehensive stuff. Great.
hi ,
its reakky a good article . well i wana know how can i import data from excel sheet into 2005 database.pls do reply
thanks
amit sharma
Hi amit,
you can import the Excel Sheet data using the Sql Server Import and Export Command. Below is the steps for the same:
1. Right click on the Database Select Tasks-> Import Data…
2. Select the datasource as ‘Microfost Excel’
3. Browse the location of the Excel File.
4. if you want to Import into the already existing table then you can Map the columns otherwise you can import into the new tables too.
Its is very simple wizard.
Please do let me know if you still face any issue.
Rupesh
hi Rupesh,
thanks for urs reply, well rupesh i am still getting error while importing the excel file.
error:
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component “Source – Assessment$” (1).
(SQL Server Import and Export Wizard)
may i know wht is this error and what i have to do for resolving this error
thanks
amit sharma
You need to apply the latest service pack available for your SQL Server edition
nice example
Thanks.
hello Sir,
i have an problem with the union operator
i write a qry1
Select tm.DBID,max(tm.Token) as token,max(tm.Symbol) as symbol,max(tm.Series) as series,max(tm.ExpiryDt)
as expirydt, max(tm.StrikePrice) as StrikePrice,
(tm.timeslot) as PutCall,sum(tc.FillQuantity) as BuyQty,
sum(tc.FillQuantity * tc.FillPrice)/100 as BuyValue, 0 as SellQty,0 as SellValue from Trade_Confirm tc
left join Orders o on tc.ResponseOrderNumber=o.OrderNumber left join tokenmast tm on o.DBID=tm.DBID
Where tc.BuySellIndicator = 1 and o.Userid=1 and o.StrategyCode=10 group by tm.DBID,tm.timeslot
and write a 2nd qry
Select tm.DBID,max(tm.Token) as token,max(tm.Symbol) as symbol,max(tm.Series) as series,max(tm.ExpiryDt) as expirydt,
0 as BuyQty,0 as BuyValue, max(tm.StrikePrice) as StrikePrice,tm.timeslot as PutCall,sum(tc.FillQuantity) as SellQty,
sum(tc.FillQuantity * tc.FillPrice)/100 as SellValue from Trade_Confirm tc
left join Orders o on tc.ResponseOrderNumber=o.OrderNumber left join tokenmast tm on o.DBID=tm.DBID
Where tc.BuySellIndicator = 2 and o.Userid=1 and o.StrategyCode=10 group by tm.DBID,tm.timeslot
i got result by individual run but when i join union then get a error this:
Error:Warning: Null value is eliminated by an aggregate or other SET operation.
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
if you have any solution then please send me in given mail id manishpandey2009@gmail.com
Hi …
Can you please tell me how to eliminate the duplicate rows using union all functions.
Thanks !!!!
Search for Delete duplicate in Google/Bing
Hi,
Can this be done
select col1,col2,col3…
from
{
Qry 1
union
qry 2
union
qry 3
}
order by col2,col5….
Thanks in advance.
select col1,col2,col3…
from
(
Qry 1
union
qry 2
union
qry 3
) as t
order by col2,col5….
Very much helped.Thanks.
The way it helped me is like this :
Since I want to get the stock of a given item , I had to use
UNION between tables like Purchase , Sales , Sales Return..
In case , which happens only rarely , if any 2 rows from different tables have duplicate values , UNION will remove 1 row , that would affect the stock.
In that case UNION ALL is the solution.
Input SQL query: select ’1′ union select null union select 5
Output:
NULL
1
5
Any idea about the data type of ’1′, because if I ‘ll give ‘a’, it throws error.
Also, why NULL comes first? What is the sort order? Even I am not able to put a column alias to imply the sort order.
Dear Arup,
I did not understand your question properly, you did not give complete information as how you want your output to be, good if you would have given an example.
Based on what I understood, this is what I would try,
Query1:
select 1 as Firstcolumn , null Secondcolumn , 5 ThirdColumn
Output:
FirstColumn SecondColumn ThirdColumn
1 NULL 5
Query2:
select 1 as OneColumn union all select null union all select 5
Output:
OneColumn
1
NULL
5
Hope this helps.
Imran.
Hi Imran,
Many thanks for the reply.
Actually in my sql query i.e. select ‘1′ union select null union select 5, the out put is :
NULL
1
5
So the first select returns 1, then the second select returns ‘NULL’ and third select returns 5. Since I have not used any order by clause, hence the output may be:
1
NULL
5
I have seen that u have solved the issue by using a columnname in your Query 2, then how is it happening?
My another question is, in this output column, the data types are different, like ’1′ is char and 5 is int. How is it possible?
1 If you use version 2000, union will automatically sort the result by the column
2 Read about precedence [SQL Server], data types in SQL Server help file
[...] You can read the explanation of why this is answer read my previous article SQL SERVER – Union vs. Union All – Which is better for performance? [...]
i have to table exalple a1 and a2
a1 table return no rows
a2 table return 10 rows
when i union two table it returns 9 row why?
but i union all two table it returns 10 row. i would not understand what functioning going on.
please if u have any idea share with me
It is becuase one of the values returned from the second table has duplicate value
[...] blog reader to re-write my previous articles. I have received few request to rewrite my article SQL SERVER – Union vs. Union All – Which is better for performance? with examples. I request you to read my previous article first to understand what is the concept [...]
Good on you mate…. Its like nibbles:
CREATE VIEW asView (int) AS
SELECT name, age from person
UNION ALL
SELECT name, ageInYear from people
Hello Pinal ,
Can you provide me more difference between them lik 3 or 4 more difference bcoz in one interview i was asked to provide 5 differences between them.
Thanks
Dharm
I want to askabout the diference bettween union and join
and if there an example I will appretiate that
The Best way to understand the functionality of Union.
I came across your site the other day and i found it really useful. I am new to the world of sql and was bit confused about different join concepts. I hope you would post something regarding joins real soon or recommend some useful website.
Thanks!
KS.
Hi,
I beg you pardon, but I’ve got a similar problems on reporting services.
I usually write code using small queries collected into a union structure, such as
Select Something
Union
Select Something Else
Because of the necessity to display data in a proper order.
Now I’m in trouble because I have to use a condition statement like
IF First condition Select Statement 1.0
Else
Select Statement 2.0
Union
IF First condition Select Statement 1.1
Else
Select Statement 2.1
and so on
Each “Nucleo” If Else Select has the same structure.
Do you have some tips ti workaround or go throw this topics.
Finally, “Sono molto sorpreso dalla tua provessionalità”…I’m Very surprise of your professional exprerience and skills.
Ciao
massimiliNO
[...] SQL SERVER – Union vs. Union All – Which is better for performance? SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL [...]
[...] SQL SERVER – Union vs. Union All – Which is better for performance? [...]
[...] 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. (Read More Here) [...]
What is the difference in processing speed of these two queries? Is one actually faster than the other???
————————————————
SELECT COL1, COL2, COL3 FROM TBL1
UNION
SELECT COL1, COL2, COL3 FROM TBL2
- — versus ———-
(notice using DISTINCT with UNION ALL)
SELECT DISTINCT COL1, COL2, COL3 FROM TBL1
UNION ALL
SELECT DISTINCT COL1, COL2, COL3 FROM TBL2
——————————————————
Note: TBL1 and TBL2 are very large and both have quite a few duplicates within each table… but each table TBL1 and TBL2 will not have anything in common. Creating a 3rd table is out of the question.
Thanks for your timely response.
Jim
Regardless of whether the data is actually unique, the DB still needs to check.
I am very Impressed with your example and statement.
I am very thankful to u.
[...] than a year ago I had written article SQL SERVER – Union vs. Union All – Which is better for performance? I have got many request to update this article. It is not fair to update already written article so [...]
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.
I tried using the UNION ALL to combine the result of two separate queries where the columns / fields are of the same values.
The question is : Can I use th UNION all for more than two select statements wherethe columns / fields are of the same values.
Thank you
Yes. You can use
Your answer are satisfactory.
Hi – thanks for this – very useful and concise – I haven’t come across a better comparisson that’s been this easy to follow
Hi- thanks – its very use full for me
Hello!!
I have used union all for two tables and worked very well. Now how I can do it for 5 or more. I am new. Please help me.
@Adres
What is the problem?
SELECT … FROM T1
UNION ALL
SELECT … FROM T2
UNION ALL
SELECT … FROM T3
UNION ALL
SELECT … FROM T4
UNION ALL
SELECT … FROM T5
@Brian Tkatch,
Great!
Oh,… How great these less lines explains..
Really great work…
In the Result Set shown above for the UNION ALL result, the second Sixth will not come.
Hi Pinal,
I have 2 columns Project date and trench. I want to display the date as range if the trench value is same. For eg:
Project date have three values 10-12-2009,11-12-2009,12-12-2009 and all the three have same trench value 1.
Now my requirement is i need to show them as
10-12 Dec 2009.
I am trying with Union so far not successful.
Can someone tell me how to achieve this.
@Ranjani
To get the range, use a GROUP BY with MIN() and MAX()
The date would be interesting, because it may be multiple months or even years. Given the example provided (the three records) this should work:
WITH
Data([Project date], trench)
AS
(
SELECT ’10-12-2009′, 1 UNION ALL
SELECT ’11-12-2009′, 1 UNION ALL
SELECT ’12-12-2009′, 1
)
SELECT
CAST(DAY(MIN([Project date])) AS VARCHAR(2))
+ ‘-’
+ CAST(DAY(MAX([Project date])) AS VARCHAR(2))
+ ‘ ‘
+ LEFT(DATENAME(m, MAX([Project date])), 3)
+ ‘ ‘
+ CAST(YEAR(MAX([Project date])) AS VARCHAR(4))
FROM
Data
GROUP BY
trench;
Note the date format will depend on what it is set to. For the US, all three dates are the 12th, but the month changes. Which is not what you intended.
Hi,
This have help me a lot.
Regards,
Pady.
Hi all,
Is it possible to use if …else condition with union all… its throwing an exception Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword ‘if’.
Sample query
if(@1stHearingdate is not NULL)
BEGIN
Select 1 as ‘Week No’, convert(varchar(100),Hrgdatetime,101) as ‘HearingDate’, count(Hrgid) ‘No of hearing’,'Remarks’
from
Hearing
where convert(varchar(100),Hrgdatetime,101) = convert(varchar(100),@1stHearingdate,101)
group by convert(varchar(100),Hrgdatetime,101)
END
else
Select 1 as ‘Week No’,convert(varchar(100),@1stHearingdate,101),0,’Remarks’
union all
if(@2ndHearingdate is not NULL)
BEGIN
Select 1 as ‘Week No’, convert(varchar(100),Hrgdatetime,101) as ‘HearingDate’, count(Hrgid) ‘No of hearing’,'Remarks’
from
Hearing
where convert(varchar(100),Hrgdatetime,101) = convert(varchar(100),@2ndHearingdate,101)
group by convert(varchar(100),Hrgdatetime,101)
END
else
Select 1 as ‘Week No’,convert(varchar(100),@2ndHearingdate,101),0,’Remarks’
Thanks a lot
If you Can represent it with a nice graph people can understand easily
Hi,
Best answer thanx a lot man.
Regards,
Mr. Rajesh Mhaisne
Software Engineer | Pune
The word “IMPOSSIBLE” Says ‘I M’ “POSSIBLE”.
Hi!
i am using UNION in my program where i want that union shoud not auto arrange the order since its default order is imp for me.
ex table1
area 1.5
table 2
area 1.2
table 3
area 2.3
after union of these result comes like
area 1.2
area 1.5
area 2.3
i want i should come like
area 1.5
area 1.2
area 2.3
what should exact querry.
pls help me.
You should use union all
@Raj Kumar Rai
There is no guaranteed order without an ORDER BY clause in the statement. Though, you should be able to provide an ORDER BY that satisfies your requirements.
There is a behaviour change in this case
In 2000, it does order by whereas from version 2005 onwards it does happen
thanks to sugest me.I had hope for response but it will surly ,you have proved it.
Can we use Where condtion after Union or Union all statements, Like as below. Coz i need to query some thin like below. PLease help me in this.
create table table1
(
id1 int,
name1 varchar(50)
)
go
insert into table1 values(1,’venu’)
insert into table1 values(2,’gopal’)
insert into table1 values(3,’hari’)
insert into table1 values(4,’prasad’)
insert into table1 values(5,’balu’)
go
create table table2
(
id2 int,
name2 varchar(50)
)
go
insert into table1 values(1,’Gopal’)
insert into table1 values(20,’fadfl’)
insert into table1 values(3,’Prasad’)
insert into table1 values(40,’asdfasd’)
insert into table1 values(50,’dfsd’)
create view myview
as
select name1 from table1
union all
select name2 from table2
where table1.id1 = table2.id2
You need to use JOIN instead of UNION
hi!
pls can you show me how to query in two table that are not match with each other?
sampl data:
table 1—–1,2,4,6,7
table 2—–2,3,4,5,8
will return—-1,3,5,6,7,8
pls..pls..pls….
Hi,
Please find solution for your query:
DECLARE @T1 TABLE(i INT)
INSERT INTO @T1 VALUES(1)
INSERT INTO @T1 VALUES(2)
INSERT INTO @T1 VALUES(4)
INSERT INTO @T1 VALUES(6)
INSERT INTO @T1 VALUES(7)
DECLARE @T2 TABLE(j INT)
INSERT INTO @T2 VALUES(2)
INSERT INTO @T2 VALUES(3)
INSERT INTO @T2 VALUES(4)
INSERT INTO @T2 VALUES(5)
INSERT INTO @T2 VALUES(8)
;with CTE AS(
SELECT i
FROM @T1
UNION
SELECT j AS i
FROM @T2
)
SELECT *
FROM Cte
WHERE NOT EXISTS(
SELECT *
FROM @T1 t1
INNER JOIN @T2 t2 ON i=j
WHERE t1.i= CTE.i
)
ORDER BY i
Let me know if you any questions.
Thanks,
Tejas
we can also do this with
select distinct * from @T1 where i not in (select distinct * from @T2 ) union
select distinct * from @T2 where j not in (select distinct * from @T1 ) order by 1 desc
select * from @T1 where i not in (select distinct * from @T2 ) union
select * from @T2 where j not in (select distinct * from @T1 )
Hi Tejas,
thank you…works fine for me!
ID Username ModifiedDate
1 A.BEENA 2009-12-07 04:48:17.980
2 A.BEENA 2009-11-17 06:02:27.443
3 Abhilash 2009-12-07 04:48:17.980
4 abhilash.r 2009-12-07 04:48:17.980
5 AJI 2009-11-17 06:02:27.443
6 haris 2009-12-07 04:48:17.980
7 haris 2009-11-17 06:02:27.443
I want to select details of all distinct users order by ModifiedDate.
I need output like this
A.BEENA 2009-12-07 04:48:17.980
Abhilash 2009-12-07 04:48:17.980
abhilash.r 2009-12-07 04:48:17.980
AJI 2009-11-17 06:02:27.443
haris 2009-12-07 04:48:17.980
Please help me
Thanks,
Aneesh
@Aneesh Daniel
How’s this?
WITH
Data(ID, Username, ModifiedDate)
AS
(
SELECT 1, ‘A.BEENA’, ’2009-12-07 04:48:17.980′ UNION ALL
SELECT 2, ‘A.BEENA’, ’2009-11-17 06:02:27.443′ UNION ALL
SELECT 3, ‘Abhilash’, ’2009-12-07 04:48:17.980′ UNION ALL
SELECT 4, ‘abhilash.r’, ’2009-12-07 04:48:17.980′ UNION ALL
SELECT 5, ‘AJI’, ’2009-11-17 06:02:27.443′ UNION ALL
SELECT 6, ‘haris’, ’2009-12-07 04:48:17.980′ UNION ALL
SELECT 7, ‘haris’, ’2009-11-17 06:02:27.443′
)
SELECT
Username,
MAX(ModifiedDate)
FROM
Data
GROUP BY
Username;
Hellow Aneesh,
Use the following query:
WITH CTE (DuplicateCount,Username,ModifiedDate)
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY Username ORDER BY ModifiedDate DESC)AS DuplicateCount,
Username, ModifiedDate FROM )
SELECT * FROM CTE WHERE DuplicateCount = 1
For more information please visit:
http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/
Regards,
Pinal Dave
The given solution resolved my issue.
Thanks you very much Pinal
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/04/03/union-vs-union-all.aspx
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
I have two table with a lot of record. Now I want get a view
So
Case 1:
Select …
From table1
where….
UNION
Select…
From table2
where…
Case 2:
Select …
From table1
where….
UNION ALL
Select…
From table2
where…
Case3:
declare @tmpTable table(…)
Go
Insert into @tmpTable sSelect … From table1 where….
Go
Insert into @tmpTable sSelect … From table2 where….
Go
select * from @tmpTable
drop @tmpTable
Please help me! Which case is the best performance?
I must say thanks for this article, which confirms how I *thought* the UNION keyword was working. I feel guilty if I just leech knowledge from this page!
100 palkalai 30000
101 raja 5000
102 kumar 4900
103 murali 21000
104 sugumar 13000
105 gugan 8940
106 gobi 56000
107 dhilip 34000
I have records like this in my notepad or word. how can i insert all those records in SQL. send me the query..
Use Bulk insert
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
@palkalaiselvan
use bulk insert to insert text file in sql
CREATE TABLE [dbo].[empdata](
[empid] [int] NOT NULL,
[empname] [nvarchar](50) NULL,
[empsalary] [int] NULL
) ON [PRIMARY]
BULK INSERT empdata FROM ‘G:\notepad.txt’ WITH
(
FIELDTERMINATOR = ‘\t’, –Tab
ROWTERMINATOR =’\n’–New Row
)
select * from empdata
Really good article…Understood the difference between both
use of savepoint
can u explain savepoint in sq1 server 2008
I CAN’T BELIEVE THAT USING “UNION ALL” BOOSTED MY QUERY AT A 500 % FASTER THAN “UNION”
THANK YOU VERY MUCH BROTHER.
[...] The difference between UNION and UNION ALL is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all the tables fitting your query specifics and combines them into a table. (Read more here) [...]
Once again you’ve saved the day for me. Mr. Pinal Dave, you’re columns have taught me more about SQL than ten books on the subject. Thank you!
Good example for Unions.I have one union with 2 select statements,Does SQL consider it as one statement /2 statements?
Krishna
Today i got different scenario where union is working better than union ALL.
One of the point astonished me while using UNION it is returning results in 20 sec where as it is taking more than 10 min
PBillno PurDate Partyname Tax SBillno SalDate Partyname Tax
————————————————————————————————-
1 2011-09-30 Raji 14 1 2011-09-30 Test 0
2 2011-09-30 Testing 55.57 4 2011-10-10 Rajesh 1.60
4 2011-10-10 Kumar 0.00 5 2011-10-10 Guna 29.40
5 2011-10-10 Suji 0.00 6 2011-09-10 Ragu 0.80
—————————————————————————————————
This is My Table …
i Have PurDate Same to SalDate.. If Not Enough Then that SalDate Record Null Row..
How do.. Please Help Me..
You need to post expected result from the above sample data. Also explain the logic
hello everybody..
please assist me how to get only duplicate records from a table1 into a new table2..
thanks in advance sir..please guide me
Shivendra
This will get you to find out duplicates
Select col from table
group by col
having count(*)>!
Thanks dear madhivanan …