The ALL keyword

Use the keyword ALL preceding a subquery to determine whether a comparison is true for every value returned. If the subquery returns no values, the search condition is true. (If it returns no values, the condition is true of all the zero values.)

The following query lists the following information for all orders that contain an item for which the total price is less than the total price on every item in order number 1023.

Figure 1. Query

SELECT order_num, stock_num, manu_code, total_price
   FROM items
   WHERE total_price < ALL
      (SELECT total_price FROM items
         WHERE order_num = 1023);

Figure 2. Query result

  order_num stock_num manu_code total_price

       1003         9 ANZ            $20.00
       1005         6 SMT            $36.00
       1006         6 SMT            $36.00
       1010         6 SMT            $36.00
       1013         5 ANZ            $19.80
       1013         6 SMT            $36.00
       1018       302 KAR            $15.00