SQL Server Union query

No.of Views	:	875
Bookmarked	:	0 times
Downloads	:
Votes	:	0

By youngmurukan On 15 Feb 2010 21:02:15

Tag : Sql Servers , T-SQL

Skill Level: Intermediate

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

Many time we require results from different tables or different queries. We try one after the other and list the result accordingly. There is a better way to do this by using UNION command or query. We can list records of two different tables by combining two sql queries to one.
What is the advantage ?

We can as well write the query two times and display one after the other. The point here is we can pick up unique records also. Say we have two tables one is storing name of the football players and other is storing players of baseball team. We want to display a list of players who are member of one of the team at least. Here we can display the name of the players by using UNION command. If John is member of football and baseball team then his name will appear once only. If we want to display all the records then we can use ALL along with the UNION command.

Here are two tables with three records each. Please note that there is one common record . Name Alex is there in both the tables.

Baseball

Football

name	age
Reid	16
Alex	21
Greek	19

name	age
John	20
Alex	21
Robort	23

SELECT name,age FROM football
UNION
SELECT name,age FROM baseball

OUT put is >

name	age
John	20
Alex	21
Robort	23
Reid	16
Greek	19

You can see from the above output that only unique records are displayed so the record Name Alex is displayed once. If the requirement is to display all the records then we have to add ALL command to the UNION query

SELECT name,age FROM football
UNION ALL
SELECT name,age FROM baseball

OUT put is >

name	age
John	20
Alex	21
Robort	23
Reid	16
Alex	21
Greek	19

About Author

youngmurukan

Occupation	-	Not Provided
Company	-	Not Provided
Member Type	-	Senior
Location	-	Not Provided
Joined date	-	12 May 2009
Home Page	-	Not Provided
Blog Page	-	Not Provided


Other popularSectionarticles
Use of Rank() function in SQL Server Use of Rank() function in SQL Server Published Date : 15/Feb/2010 Use of Dense_Rank() function in SQL Server Use of Dense_Rank() function in SQL Server Published Date : 15/Feb/2010 How to Get First,Last and No of Days in Month Using T-Sql I have read few questions in forums, about how to select first, last date month and how many days in a month. In my project had used some query to perform these tasks. I will share those with article. Published Date : 17/Dec/2010 How to select random records in SQl Server How to select random records in SQl Server Published Date : 15/Feb/2010 how to get how many records entered in day by a user in sql server. how to get how many records entered in day by a user in sql server. Published Date : 15/Feb/2010

Comments

By:RRaveen	Date Of Posted:1/1/2011 2:10:46 AM
More about
Hi Swathi, Please give more details about your question.if i'm not wrong your face same problem what i have given solutions here.if not please post your problem in our messageboard. we will try to help. Thak you

By:swathi	Date Of Posted:12/31/2010 3:11:51 AM
Child list for field app cannot be created.
please tell