have you any wool?

This is another one that I revisist every so often with some of the guys at work. If you have data in two different tables that you need to select back in one query, chances are you’ve come across the Union statement. But did you know that the Union statement, by itself, forces a Select Distinct when returning values? Now in some cases you may want to exclude duplicates, in others you may not. Union All, however, will not force a Select Distinct.

For an added performance boost to your queries and stored procedures, use the Union All statement if you know that you want to return back everything from your merged tables, regardless if there are duplicates or not.

In the example below, notice John Johnson is in both tables @Team1 and @Team2, however his name is only returned once using the Union and twice using the Union All.

Declare @Team1 Table(
	[ID] int identity(1,1) primary key,
	[FirstName] varchar(50),
	[LastName] varchar(50)
)

Declare @Team2 Table(
	[ID] int identity(1,1) primary key,
	[FirstName] varchar(50),
	[LastName] varchar(50)
)

Insert Into @Team1 values ('John','Johnson')
Insert Into @Team1 values ('Jim','Johnson')
Insert Into @Team1 values ('Joe','Smith')
Insert Into @Team1 values ('Jacob','Clark')

Insert Into @Team2 values ('Mark','Davis')
Insert Into @Team2 values ('Albert','Einstien')
Insert Into @Team2 values ('Brian','Thompson')
Insert Into @Team2 values ('John','Johnson')

Select FirstName, LastName
From @Team1
Union
Select FirstName, LastName
From @Team2

Select FirstName, LastName
From @Team1
Union All
Select FirstName, LastName
From @Team2