Re: [SQL] Substract queries

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql