New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6

EVERY

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The EVERY() aggregate function is the standard SQL version of the BOOL_AND function.

SELECT 
  every(ID < 4),
  every(ID < 5)
FROM BOOK
create.select(
         every(BOOK.ID.lt(4)),
         every(BOOK.ID.lt(5)))
      .from(BOOK)

Producing:

+---------------+---------------+
| every(ID < 4) | every(ID < 5) |
+---------------+---------------+
| false         | true          |  
+---------------+---------------+

Dialect support

This example using jOOQ:

every(BOOK.ID.lt(4))

Translates to the following dialect specific expressions:

-- ACCESS
(min(SWITCH(BOOK.ID < 4, 1, TRUE, 0)) = 1)

-- ASE, DB2, FIREBIRD, ORACLE, SQLDATAWAREHOUSE, SQLSERVER, SYBASE
CASE WHEN min(CASE WHEN BOOK.ID < 4 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(CASE WHEN BOOK.ID < 4 THEN 1 ELSE 0 END) = 1) THEN 0 END

-- AURORA_MYSQL, DERBY, H2, HSQLDB, INFORMIX, MARIADB, MEMSQL, MYSQL, REDSHIFT, SQLITE, TERADATA, VERTICA
(min(CASE WHEN BOOK.ID < 4 THEN 1 ELSE 0 END) = 1)

-- AURORA_POSTGRES, COCKROACHDB, POSTGRES
bool_and((BOOK.ID < 4))

-- CUBRID, HANA, INGRES
CASE WHEN min(CASE WHEN BOOK.ID < 4 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (min(CASE WHEN BOOK.ID < 4 THEN 1 ELSE 0 END) = 1) THEN FALSE END

(These are currently generated with jOOQ 3.15, see #10141)

The jOOQ Logo