Ram Kashyap Ram Kashyap - 1 month ago 4
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?
Please refer to the link for output of select queries.

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;

Answer

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).