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
(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
table X with only one value: the
totalpqty, as sum of pqty's that have
Second on the fly table Y:
select sum(sqty) as totalsqty from sale where recyclerid = 102001
table Y with only one value: the
totalsqty, as sum of sqty's that have
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:
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).