Ram Kashyap - 1 year ago 55
SQL Question

# How subtraction of two select SQL statements works?

I have two tables: purchase and sale
Select queries for two tables
Below query gives the difference between 'pqty' and 'sqty'

Can someone explain how below query works? What is stored in X,Y?

``````select X.totalpqty - Y.totalsqty

from

(select sum(pqty) as totalpqty from purchase where wasteid = 2)X,

(select sum(sqty) as totalsqty from sale where recyclerid = 102001)Y;
``````

The given query creates 2 tables on the fly:

First on the fly table X:

``````select sum(pqty) as totalpqty from purchase where wasteid = 2
``````

Creates a `table` X with only one value: the `totalpqty`, as sum of pqty's that have `wasteid=2`.

Second on the fly table Y:

``````select sum(sqty) as totalsqty from sale where recyclerid = 102001
``````

Creates a `table` Y with only one value: the `totalsqty`, as sum of sqty's that have `recyclerid=102001`.

These 2 tables (X and Y) are not joined because there is no need to do so, as they only return a single value each. The cartesian product is just a single row with 2 columns:

• the sum of pqty's
• the sum of sqty's

Finally the main query, gets the 2 values that are returned by the 2 subqueries and performs the actual subtraction that is needed (`X.totalpqty - Y.totalsqty`).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download