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:
Thanks in advance for your help,
mrotor..
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
Thanks in advance for your help,
mrotor..
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
awking00
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
>> 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.
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.
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.
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.Open in new window