Re: Substract queries

[Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>]

Nacef LABIDI wrote:
> Hi all,
>
> I was wondering how can I substract result between select queries. I mean I
> want to issue a query that does this :
> (select * from mytable where condition1) - (select * from mytable where
> condition2)

If the subqueries return single (scalar) results, you can just subtract 
them directly:

SELECT (SELECT COUNT(id) FROM table1) - (SELECT COUNT(id) FROM table2)


However, I'm guessing you REALLY want to match the records up in two 
tables and compare them.

In that case what you need to do is read this:

http://www.postgresql.org/docs/8.3/static/tutorial-join.html

and this:

http://www.postgresql.org/docs/8.3/static/queries.html

including this:

http://www.postgresql.org/docs/8.3/static/queries-table-expressions.html#QUERIES-FROM

then use a JOIN to combine both tables, matching up corresponding 
records in each by (eg) an id field, then subtracting the fields.

Say I have

tablea
----------
ida   numa
----------
1       11
2       48
3       82
5       14


tableb
----------
idb   numb
5     20
2     30
3     40
1     50


then if I execute:

SELECT ida, numa, numb, numa - numb AS sub
FROM tablea, tableb
WHERE tablea.ida = tableb.idb';

I'll get a result like:

ida     numa    numb    sub
---------------------------
2       48      30      18
5       14      20      -6
3       82      40      42
1       11      50      -39

which is what I suspect you want. Note that the results do not appear in 
any particular order.



If what you really want is a query that returns all records in the first 
query EXCEPT those returned by the second query, then see:

http://www.postgresql.org/docs/8.3/static/queries-union.html

--
Craig Ringer