The ALL conditional expression is a predicate over a subquery that is true if the comparison operation in WHERE clause is true for all values returned by the subquery or the result of the subquery is empty.

The ANY conditional expression is a predicate over a subquery that is true if the comparison operation is true for some value in the result of the subquery. An ANY conditional expression is false if the result of the subquery is empty or if the comparison operation is false for every value in the result of the sub-query, and is unknown if neither true nor false. The keyword SOME is synonymous with ANY.

Example

Entities

@Entity
public class ProductInventory {
  @Id
  @GeneratedValue
  private long id;
  private String productName;
  private int quantity;
  private String inventoryLocation;
    .............
}

@Entity
public class OrderItem {
  @Id
  @GeneratedValue
  private long id;
  private String productName;
  private int quantity;
    .............
}

Using ALL and ANY operators

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEntities();
          executeQuery();
          executeQuery2();
      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEntities() {
      System.out.println("-- persisting entities --");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      getProductInventoryList().forEach(em::persist);
      getOrderItemList().forEach(em::persist);
      em.getTransaction().commit();

      System.out.println("-- entities persisted --");
      em.createQuery("SELECT p FROM ProductInventory p")
        .getResultList()
        .forEach(System.out::println);

      em.createQuery("SELECT o FROM OrderItem o")
        .getResultList()
        .forEach(System.out::println);
      em.close();

  }

  private static void executeQuery() {
      System.out.println("-- finding OrderItem which have qty > ALL inventories qty --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT o FROM OrderItem o"
              + " WHERE o.quantity > ALL (SELECT p.quantity from ProductInventory p"
              + " WHERE p.productName= o.productName)");
      List<OrderItem> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void executeQuery2() {
      System.out.println("-- finding OrderItem which have qty > ANY inventories qty --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT o FROM OrderItem o"
              + " WHERE o.quantity > ANY (SELECT p.quantity from ProductInventory p"
              + " WHERE p.productName= o.productName)");
      List<OrderItem> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static List<ProductInventory> getProductInventoryList() {
      List<ProductInventory> list = new ArrayList<>();
      list.add(ProductInventory.of("Monitor", 10, "Vritville"));
      list.add(ProductInventory.of("Monitor", 50, "Lonpore"));
      list.add(ProductInventory.of("Desktop Computer", 15, "Vritville"));
      list.add(ProductInventory.of("Desktop Computer", 20, "Lonpore"));
      list.add(ProductInventory.of("Laptop", 25, "Vritville"));
      list.add(ProductInventory.of("Laptop", 30, "Lonpore"));
      list.add(ProductInventory.of("SSD", 45, "Vritville"));
      list.add(ProductInventory.of("SSD", 30, "Lonpore"));
      return list;
  }

  private static List<OrderItem> getOrderItemList() {
      List<OrderItem> list = new ArrayList<>();
      list.add(OrderItem.of("Monitor", 60));
      list.add(OrderItem.of("Desktop Computer", 9));
      list.add(OrderItem.of("Laptop", 25));
      list.add(OrderItem.of("SSD", 40));
      return list;
  }
}

-- persisting entities --
-- entities persisted --
ProductInventory{id=1, productName='Monitor', quantity=10, inventoryLocation='Vritville'}
ProductInventory{id=2, productName='Monitor', quantity=50, inventoryLocation='Lonpore'}
ProductInventory{id=3, productName='Desktop Computer', quantity=15, inventoryLocation='Vritville'}
ProductInventory{id=4, productName='Desktop Computer', quantity=20, inventoryLocation='Lonpore'}
ProductInventory{id=5, productName='Laptop', quantity=25, inventoryLocation='Vritville'}
ProductInventory{id=6, productName='Laptop', quantity=30, inventoryLocation='Lonpore'}
ProductInventory{id=7, productName='SSD', quantity=45, inventoryLocation='Vritville'}
ProductInventory{id=8, productName='SSD', quantity=30, inventoryLocation='Lonpore'}
OrderItem{id=9, productName='Monitor', quantity=60}
OrderItem{id=10, productName='Desktop Computer', quantity=9}
OrderItem{id=11, productName='Laptop', quantity=25}
OrderItem{id=12, productName='SSD', quantity=40}
-- finding OrderItem which have qty > ALL inventories qty --
OrderItem{id=9, productName='Monitor', quantity=60}
-- finding OrderItem which have qty > SOME inventories qty --
OrderItem{id=9, productName='Monitor', quantity=60}
OrderItem{id=12, productName='SSD', quantity=40}

Example Project

Dependencies and Technologies Used:

• h2 1.4.197: H2 Database Engine.
• hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
  Implements javax.persistence:javax.persistence-api version 2.1
• JDK 1.8
• Maven 3.3.9