Could anyone advise how the SQL below retrieves what I want to retrieve?

There are tables ORDER_HEADER and ORDER_DETAILS for my Inventory System. The former records all the instances of orders that my company receives, whereas the latter order lines. Database-wise, the tables are designed as follows (* represents primary keys):

ORDER_HEADER: REQUEST__T*, ORDER_DATE, CUSTOMER__T
ORDER_DETAILS: REQUEST__T*, NO*, PRODUCT__T, QTY_NEEDED, QTY_SUPPLIED

Orders are recorded by the date and the issuer of the order (certain company made an order on so and so date). The primary keys for ORDER_DETAILS are REQUEST__T and NO. Each record in the ORDER_DETAILS table represents an order line. Each line in an order represents the product and the quantity ordered. Thus, a record in this table should express that the Nth line (NO) of the order (REQUEST__T) requires certain number (QTY_NEEDED) of products (PRODUCT__T) and, so far, certain number of them (QTY_SUPPLIED) have been supplied.

Orders are shipped to the customers only when each order lines has been provied with the products to the quantity needed. In order to retrieve such orders in ORDER_HEADER table, I created the following SQL and believe me it works. But I don't know why it works as I want it....

select *
from ORDER_HEADER a
where 0 = all
(select (QTY_NEEDED - QTY_SUPPLIED)
from ORDER_DETAILS b
where a.REQUEST__T = b.REQUEST__T)

The key for the SQL to work seems to me the join condition in the subquery: where a.REQUEST__T = b.REQUEST__T. Without it, the SQL retrieves no records since the subquery after ALL operator usually retrieves order lines where not all the quantity needed is supplied yet. The condition 0 = all (SUBQUERY) returning truth value FALSE, the main SELECT statement retrieves no data.

Yet, with the join condition, everything changes: the query works. The join condition seems to be working like "group by" for the ALL operator. Why does it work like this? I consulted several references but could not find how a Joined-Subquery Works After ALL Operator.

it's simple -- for each order, the subquery returns as many rows as there are order lines

here are some examples --so the subquery returns two rows for order 101 and three rows for order 202

however, the condition where 0 = all (subquery) is true only for order 101

reason? one of the rows returned by the subquery for order 202 doesn't meet the WHERE condition

another way you could write your query is like this --

Yes, but how does the WHERE clause where a.REQUEST__T = b.REQUEST__T0 make the subquery operate on units of REQUEST__T, as opposed to the one without it operating on the list of the results as a whole?

Maybe "and not QTY_NEEDED = QTY_SUPPLIED )"? I quite understand that ALL and ANY operators are equivalent to EXISTS, NOT EXISTS or either with a change in the subquery operator. Actually that is how the statements are run according to my observations by running Oracle Optimiser. Whether it is NOT EXISTS or ALL, the truth value operators are very difficult for me to understand.

You could interpret the subquery as being run several times, once for every value of ORDER_HEADER.REQUEST__T
In each run, only the lines of ORDER_DETAILS with that value in REQUEST_T are looked at. And the "0 = ALL ..." condition only returns true if for all those lines QTY_NEEDED equals QTY_SUPPLIED.
Thus, only for those, the corresponding line in ORDER_HEADER is shown.

Thus, an ALL condition refers multiple lines at a time, and the condition a.REQUEST__T = b.REQUEST__T limits its scope to only those entries currently being considered in the outer query.

(To complicate things: "ALL" will also return true when its scope is empty, i.e.: lines from ORDER_HEADER with no corresponding lines in ORDER_DETAILS will also be shown.
The "NOT EXISTS" solution does of course the same thing. (And indeed, the last conditions must be inverted in that case.) )