asked on

I need assistance using UNION in my SQL Query

Hi Experts,
I am writing a query using UNION to join two tables in SQL Server 2012. One table has 37,550 records, and the other table has 940,400 records, for a combined total of 977,950. When I run my query using UNION, the result set only returns 885,067. Why am I getting less records?

my query syntax:

SELECT [Column 0] AS SSN, [Column 1] AS fee,
[Column 2] AS DATE, 
[Column 4] AS OrderNo
FROM TABLE1
UNION
SELECT [Column 3] AS SSN, [Column 4] AS fee,
[Column 0] AS DATE, 
[Column 2] AS OrderNo
FROM TABLE2
ORDER BY OrderNo

Open in new window

Thanks in advance for your help,
mrotor..

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

UNION ALL should be used when you're absolutely certain there will be no duplicates, and is just a set A + set B as a single return set.

SELECT name FROM whorehouses UNION ALL SELECT name FROM burgerjoints

Open in new window

UNION eliminates duplicates (as chaau stated above), and there is an extra cost to that as the query processor has to match up and remove rows from the final set, therefore should be avoided if possible.

SELECT name from customers UNION SELECT name FROM vendors

Open in new window

Scott Pletcher

>> UNION ALL should be used when you're absolutely certain there will be no duplicates <<

Not true. There's no problem at all with returning duplicate rows using UNION ALL. If you don't care about and/or want duplicates in the result set, use UNION ALL:

SELECT 'A' AS col1 UNION ALL SELECT 'A' UNION ALL SELECT 'A' UNION ALL SELECT 'A' UNION ALL SELECT 'A' --...

Otoh, UNION should be used only when you explicitly want/need to prevent duplicate rows from being returned by the query, because of the extra overhead to SQL of removing any potential duplicate rows.

awking00

Since the difference between the total rows and the result set is less than all of the rows in the other table, there are obviously duplicate values for columns 0, 1, 2, and 4 in the first table by itself as well.