A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function

We’ve just added support for the EVERY() aggregate function (#1391) to jOOQ, and would like to take the opportunity of letting you know of this true SQL gem that can come in handy EVERY(now and then) (pun intended).

Let’s assume we have four books in our table:

INSERT INTO book VALUES (1, 1, '1984');
INSERT INTO book VALUES (2, 1, 'Animal Farm');
INSERT INTO book VALUES (3, 2, 'O Alquimista');
INSERT INTO book VALUES (4, 2, 'Brida');

Now the question is:

Is EVERY() ID lower than 10?

We’ll ask:

SELECT EVERY(id < 10)
FROM book

And the answer is:

every
-----
true

Does EVERY() book for each author end with the letter ‘a’?

We’ll ask:

SELECT author_id, EVERY(title LIKE '%a')
FROM book
GROUP BY author_id

And the answer is:

author_id   every
-----------------
1           false
2           true

Wonderful!

As with all aggregate functions, we can even use them as a window function!

SELECT 
  book.*, 
  EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
FROM book

Which will produce

id  author_id   title          every
------------------------------------
1   1           1984           false
2   1           Animal Farm    false
3   2           O Alquimista   true
4   2           Brida          true

Who supports EVERY

Well, the SQL standard has it:

10.9 <aggregate function>

<aggregate function> ::=
    COUNT <left paren> <asterisk> <right paren> [ <filter clause> ]
  | <general set function> [ <filter clause> ]
  | <binary set function> [ <filter clause> ]
  | <ordered set function> [ <filter clause> ]
  | <array aggregate function> [ <filter clause> ]

<general set function> ::=
    <set function type> <left paren> [ <set quantifier> ]
        <value expression> <right paren>

<set function type> ::=
    <computational operation>

<computational operation> ::=
    AVG
  | MAX
  | MIN
  | SUM
  | EVERY <-- yes, here! EVERY!
  | ANY
  | SOME
  | COUNT
  | STDDEV_POP
  | STDDEV_SAMP
  | VAR_SAMP
  | VAR_POP
  | COLLECT
  | FUSION
  | INTERSECTION

And, of course PostgreSQL!

But if your database is not PostgreSQL, don’t worry. EVERY() can be emulated on EVERY() database using SUM() and CASE expressions. Here’s how to emulate the first query:

-- SELECT EVERY(id < 10)
-- FROM book

SELECT MIN(CASE WHEN id < 10 THEN 1 ELSE 0 END) 
FROM book;

Or as window functions

-- SELECT 
--   book.*, 
--   EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
-- FROM book

SELECT 
  book.*,
  MIN(CASE WHEN title LIKE '%a' THEN 1 ELSE 0 END)
    OVER(PARTITION BY author_id)
FROM book;

For a more concise emulation, see Stew Ashton’s comments

And, as always on this blog, we’re happy to conclude that the upcoming jOOQ 3.6 will now handle EVERY(emulation) for you, so you can write:

DSL.using(configuration)
   .select(BOOK.fields())
   .select(every(BOOK.TITLE.like("%a"))
           .over(partitionBy(BOOK.AUTHOR_ID)))
   .from(BOOK)
   .fetch();

Have fun with this new function!