Subtract Values from Two Different Tables

Question

Consider table X:

A
-
1
2
3
3
6

Consider table Y:

A
-
0
4
2
1
9

How do you write a query that takes the difference between these two tables, to compute the following table (say table Z):

A
-
1
-2
1
2
-3

Answer 1

It's not clear what you want. Could it be this?

SELECT  (SELECT SUM(A) FROM X) - 
        (SELECT SUM(A) FROM Y)
        AS MyValue

Answer 2

Marcelo is 100% right - in a true relational database the order of a result set is never guaranteed. that said, there are some databases that do always return sets in an order.

So if you are willing to risk it, here is one solution. Make two tables with autoincrement keys like this:

CREATE TABLE Sets (
  id integer identity(1,1)
, val decimal
) 

CREATE TABLE SetY (
  id integer identity(1,1)
, val decimal
) 

Then fill them with the X and Y values:

INSERT INTO Sets (val) (SELECT * FROM X)
INSERT INTO SetY (val) (SELECT * FROM Y)

Then you can do this to get your answer:

SELECT X.ID, X.Val, Y.Val, X.val-Y.val as Difference
FROM Sets X
LEFT OUTER JOIN SetY Y
  ON Y.id = X.ID

I would cross my fingers first though! If there is any way you can get a proper key in your table, please do so.

Cheers,

Daniel