SQL Union Query

[ElPresidente408]

I have two tables, one with 110,100 rows, and the other with 89,545 for a combined 199,645.

When I do a UNION query, I only get 199,614 rows.

To try and find the 31 missing rows, I've tried both INTERSECT and joins, but can't turn up any results for the apparently 31 duplicate rows. Shouldn't I get the 31 rows by substituting union with intersect??

[techgnome]

Nope... What you need to do is understand how UNION works... UNION will give you a COMBINED listing from both tables... this means that duplicated rows are eliminated. This is probably where your 31 rows went. If you want to get ALL of the results, change the UNION to a UNION ALL ... this will force all rows to be returned even duped ones. Change to UNION ALL and see if your "missing" 31 rows suddenly appear.

-tg

[ElPresidente408]

I understand that part, but I'm actually trying to pull only the 31 rows. There shouldn't be any duplicates because one table has current sales, and the other has older ones before a certain cutoff date. UNION ALL does pull the full results.

I'm trying to see what the duplicates are.

[wild_bill]

You should be able to join the tables to identify rows that exist in each table.

Select tablea.*
from tablea
inner join tableb on tablea.column1 = tableb.column1 and tablea.column2 = tableb.column2

[techgnome]

WB - that was my first instinct... then I went back an reread the thread.... using smaller numbers, here's the deal:
Two tables - TableA - 100 Rows... TableB 150 rows.

SELECT * FROM TableA
UNION
SELECT * FROM TableB

The expectation was that the query result would be 250 rows... but it's not...it's only 220 rows... Where did the "missing 30" rows go? When UNION is run, it merges the results... this causes a collapsing of the data, removing duplicates... so there were 30 rows in TableA that were also in TableB ... if you use UNION ALL then ALL results will be returned including duplicated rows.

Now... your query will show which ones are duplicated... sometimes that's helpful. I know I've done that.

At any rate, this is something that trips up a lot of people first time they try to use UNION to merge table results.

-tg

[ElPresidente408]

so there were 30 rows in TableA that were also in TableB ... if you use UNION ALL then ALL results will be returned including duplicated rows.

Exactly, and I need those 30 rows so I can manually review them and see if corrective action needs to be taken. There should not be any duplicates whatsoever between the two tables.


wb- I've already tried the following code (which has every column) and this returns 0 results.

select * from tbl_old_sales A INNER JOIN tbl_curr_sales B ON
A.ST_Date=B.ST_Date AND
A.st_customer=B.ST_Customer AND
A.ST_Item=B.ST_Item AND
A.ST_ImportDate=B.ST_ImportDate AND
A.st_oh = B.ST_OH AND
A.ST_Sales = B.ST_Sales AND
A.st_units = B.ST_Units

Is there something else that could be causing this?

[techgnome]

change it to UNION ALL ... sort the results and have a look... well, first, does UNION ALL return the proper number of rows you are expecting?

-tg

[ElPresidente408]

UNION ALL does return the expect number of rows which is the total of the two tables.

I'm cheating right now and waiting for Excel to highlight the differences between the two. Very slow going with nearly 200,000 rows