Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    56

    Unanswered: How does a Joined-Subquery Work After ALL Operator?

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's simple -- for each order, the subquery returns as many rows as there are order lines

    here are some examples --
    Code:
    REQUEST__T  PRODUCT__T  QTY_NEEDED  QTY_SUPPLIED
       101         aaaa          4           4
       101         bbbb          1           1
       202         xxxx          3           2
       202         yyyy          1           1
       202         zzzz          5           5
    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 --
    Code:
    select a.*
      from ORDER_HEADER a
      where NOT EXISTS
            ( select 1
                from ORDER_DETAILS 
               where REQUEST__T = a.REQUEST__T
                 and where QTY_NEEDED = QTY_SUPPLIED )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    56
    Quote Originally Posted by r937
    it's simple -- for each order, the subquery returns as many rows as there are order lines

    here are some examples --
    Code:
    REQUEST__T  PRODUCT__T  QTY_NEEDED  QTY_SUPPLIED
       101         aaaa          4           4
       101         bbbb          1           1
       202         xxxx          3           2
       202         yyyy          1           1
       202         zzzz          5           5
    so the subquery returns two rows for order 101 and three rows for order 202
    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?

    Quote Originally Posted by r937
    [A]nother way you could write your query is like this --
    Code:
    select a.*
      from ORDER_HEADER a
      where NOT EXISTS
            ( select 1
                from ORDER_DETAILS 
               where REQUEST__T = a.REQUEST__T
                 and where QTY_NEEDED = QTY_SUPPLIED )
    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.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.) )
    Last edited by Peter.Vanroose; 03-28-06 at 01:47.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •