EVERY in SQL: Asserting a Condition Holds for the Whole Group

EVERY is an aggregate that collapses a group of boolean values into one: it returns true only when the condition holds for every counted row in the group. It is the SQL-standard synonym for BOOL_AND, and it is exactly what you reach for when the question is "do all rows satisfy this rule?"

Basic syntax

EVERY takes a boolean expression evaluated per row. The result is true if every counted row yielded true, and false if at least one yielded false.

SELECT dept, EVERY(salary > 0) AS all_paid
FROM employees
GROUP BY dept;

For each department we assert that every employee has a positive salary. If a single employee has salary <= 0, the aggregate returns false.

EVERY behaves like a logical AND across rows:

• all true -> true
• at least one false -> false
• no known true/false value to count -> NULL

That last line matters: a SQL aggregate does not return true for an empty group. PostgreSQL returns NULL (unknown) when there is nothing to fold, so you must handle that case explicitly rather than assume "vacuously true".

Validation rollups

The most common use is collapsing many detail rows into a single status flag. The classic case: "are all line items shipped?"

SELECT user_id,
       EVERY(status = 'shipped') AS fully_shipped,
       COUNT(*)                  AS line_items
FROM orders
GROUP BY user_id;

A single row with status <> 'shipped' flips fully_shipped to false. This is ideal for dashboards and reports: one row per user instead of recomputing the condition in application code.

EVERY pairs naturally with HAVING when you only want "clean" groups:

SELECT u.country, EVERY(o.amount > 0) AS all_positive
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.country
HAVING EVERY(o.amount > 0);

EVERY vs BOOL_AND

Functionally EVERY(x) and BOOL_AND(x) are identical in PostgreSQL; they are synonyms. The difference is pure readability:

• EVERY is in the SQL standard and reads like plain English: "every order is shipped".
• BOOL_AND makes the boolean nature explicit for readers who think in flags.

-- identical results
SELECT EVERY(amount > 0)    FROM orders;
SELECT BOOL_AND(amount > 0) FROM orders;

Tip: use EVERY in reports and views read by analysts, and BOOL_AND (with its partner BOOL_OR) in plumbing code, so the "all / at least one" intent is obvious at a glance.

NULL handling

This is the main trap. EVERY skips rows where the argument is NULL, just like every other aggregate.

SELECT EVERY(status = 'shipped') AS result
FROM orders
WHERE id IN (1, 2);  -- suppose row 2 has status IS NULL

• If a row has status IS NULL, then status = 'shipped' evaluates to NULL, and that row is not counted.
• If, after dropping NULL rows, no row remains in the group, EVERY returns NULL, not true.

Gotcha: you might assume a NULL status would "fail" the check, but it is silently ignored. If NULL should count as a violation, normalize the expression explicitly:

SELECT EVERY(COALESCE(status, 'pending') = 'shipped') AS strict
FROM orders
GROUP BY user_id;

Wrap the aggregate itself in COALESCE instead if you want a fully empty group to read as a failure rather than NULL.

MySQL and ClickHouse

The standard EVERY is not available everywhere:

• PostgreSQL — full support for both EVERY and BOOL_AND.
• MySQL — has neither EVERY nor BOOL_AND. Emulate with MIN: MIN(condition) equals 1 only when the condition is true everywhere.

-- MySQL: equivalent of EVERY(amount > 0)
SELECT user_id, MIN(amount > 0) = 1 AS all_positive
FROM orders
GROUP BY user_id;

• ClickHouse — offers min plus handy combinators; the idiomatic path is the same min(cond) trick or the minIf aggregate.

Remember the essentials: EVERY means "all counted rows passed the check", it ignores NULL, and it returns NULL (not true) on an empty group. For strict validation, decide the fate of NULL explicitly with COALESCE inside the argument or around the result.