Software Engineering: Dimension Alignment Join Explained

In the previous essay, we talked about the alignment of two sub tables to the primary table. Below is the SQL query for doing it:

SELECT Orders.id, Orders.customer,A.x,B.y
FROM Orders
LEFT JOIN (SELECT id,SUM(price) x FROM OrderDetail GROUP BY id) A
    ON Orders.id=A.id
LEFT JOIN (SELECT id,SUM(amount) y FROM OrderPayment GROUP BY id ) B
    ON Orders.id=B.id
WHERE A.x>B.y

    
    

    
    




Obviously, this is a useful JOIN in real-world queries, but under which type it should be classified?
It involves the Orders table and two subqueries – A and B. Each subquery has a GROUP BY id cla...