mysql query for 3 tables, join or union which one is best to calculate balance?

With the following 3 tables, how to calculate the stock balance for million records in a best/faster way ?!

As my experience, using join can take less than 0.1 sec but not the final result,

using union can got the final result but it takes 1x sec...

stocktable
stid    productid   qty
1   1   100
2   2   200

sellstable
sellid  stid    qty
1   1   50
2   1   30

scraptable
scrapid stid    qty
1   1   10
2   1   5
3   2   100

SELECT
    a.stid,
    qty,
    sellid,
    b.qty,
    scrapid,
    c.qty
FROM
    stocktable AS a
LEFT JOIN sellstable AS b ON a.stid = b.stid
LEFT JOIN scraptable AS c ON a.stid = c.stid

1   100 1   50  1   10
1   100 1   50  1   5
1   100 2   30  1   10
1   100 2   30  1   5
2   200 null    null    3   100

this is fast when using join for the inner query, but how to sum up the balance...

added info:

the result should be
1   5
2   100开发者_StackOverflow社区

thanks!

If you literally need all the columns in your query, along with the sum of the three qty fields, then you can do SUM(a.qty+b.qty+c.qty), but it's hard to figure exactly what you are looking for. Is there a particular key in those tables that you want to group on? Do you need sellid and scrapid at all?