Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Union

By Andy Warren,

The union statement probably isn't the most used statement in TSQL, but it is useful. What's more interesting is that it's often used in a way that might not return the results you would expect. If you're a TSQL master none of this will be new to you, but for the rest of us - it's worth a few minutes review!

We'll start with a somewhat contrived example, let's say we want to invite 10 employees with the lastname of Adams and 10  customers with the last name of Smith to our SomethingOrOther.com launch.All things being equal, most people expect this statement to return 20 rows if there are 10 rows that match each separate query - would you agree?

select top 10 firstname, lastname from person.contact where lastname='adams'
union
select top 10 firstname, lastname from dbo.customers where lastname='smith'

When I run each statement separately I get 10 rows each, as follows:

If I run the entire query, I only get back 18 rows:

If you look carefully you'll see that our first query for last name 'Smith' return a duplicate row for Samantha and another for Denise. Union by design removes duplicates from the final result set, even if the duplicates were within a single statement as in our example. It can be very useful behavior, or problematic if you're not expecting it! In this case are the two rows we eliminated truly duplicate people, or more likely they have different ID's and addresses, meaning we managed to exclude two people by accident.

If we change to use UNION ALL we get the expected behavior of returning 20 rows, duplicates and all.

Depending on our needs either could be correct. But it's so important and so often misunderstood that I make it a standard practice to follow up any time I see UNION to see if they know the difference. If they indeed meant UNION I add a comment to make life easier for the next DBA, like thisL

select top 10 firstname, lastname from person.contact where lastname='adams'
union	--8/10/08 verified UNION is correct
select top 10 firstname, lastname from dbo.customers where lastname='smith'

There's also a difference from a performance perspective. UNION ALL requires little additional work besides running the combined queries, but just UNION requires an additional step to remove the duplicates - and that could be expensive, it depends on how many rows you're checking. The top query plan is UNION, the bottom is UNION ALL. We can see that the UNION example includes a SORT operator that changes the plan, but always check Profiler to see the actual difference in cost.

So, there's a little trivia you can test your developers with, and it might save you from an embarrassing mistake someday too! If you're new to UNION you might want to take a look at both EXCEPT and INTERSECT, both were added to SQL 2005.

Visit my blog at http://blogs.sqlservercentral.com/andy_warren/default.aspx

 

 

Total article views: 10495 | Views in the last 30 days: 7
 
Related Articles
ARTICLE

Using the Union Statement - SQL School Video

In this SQL School video, learn how the UNION statement can help you join together results from diff...

FORUM

Multiple UNION ALL queries cause SSMS to hang/freeze up/ slow down?

Several CTEs and UNION ALL statements in one query

FORUM

eliminating duplicates

eliminating duplicates

FORUM

Why INSER INTO ... UNION ALL?

Why INSER INTO ... UNION ALL instead of single insert statements?

FORUM

working with union all or union

union all vs union

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones