Why we are missing the ANY keyword in SQL


Rethinking SQL: SELECT ANY, JOIN ANY, UPDATE ANY, DELETE ANY

A small semantic shift that makes your intent explicit: do you expect data — or not?

TL;DR

Most SQL statements hide a key assumption:
Do you expect the data to exist?

SELECT ANY, JOIN ANY, UPDATE ANY, and DELETE ANY make that expectation explicit.

ANY means: absence is acceptable.
If you expect data, say so. If you don’t, say that too.

Three small keywords. Infinite clarity.

The missing expectation

SQL silently blurs the line between “no data” and “wrong data”.

A SELECT that returns zero rows isn’t technically an error — but sometimes, it should be.
If you query configuration, or a required user profile, and nothing comes back, that’s not “empty” — that’s a logic failure.

Right now, SQL can’t express that difference.
You have to handle it in your code, check row counts, throw exceptions — boilerplate everywhere.

It’s time SQL itself said what you mean.

SELECT ANY – the tolerant query

A plain SELECT should be strict by default:
If nothing matches, that’s an error — because you expected data to exist.

SELECT * FROM settings WHERE key = 'default_currency';
-- ❌ Error: expected at least one row

When absence is acceptable, you say so explicitly:

SELECT ANY * FROM settings WHERE key = 'default_currency';
-- ✅ Returns 0 rows without error

This single distinction captures the developer’s true intent.
“ANY” means: I’m okay if this is missing.

Examples

  • Optional lookup: SELECT ANY * FROM comments WHERE post_id = :id;

  • Non-critical metadata: SELECT ANY * FROM audit_log WHERE user_id = :id;

  • Cache population: “Fetch it if it’s there, skip if not.”

SQL should express intent, not assumption

When you write SQL, you’re expressing a hypothesis:

“I believe this data exists.”

If it doesn’t — what happens?

Currently, SQL acts as if every query is tolerant. It never complains about empty results.
That’s lenient, but dangerous. Silent absence hides logic bugs.

By making SELECT strict and SELECT ANY tolerant, we bring honesty back:

  • SELECT → “Expect data. Error if none.”

  • SELECT ANY → “Maybe data. That’s fine.”

JOIN ANY – the natural outer join

Once you adopt this pattern, joins fall right into place.

SELECT ANY o.id, c.name
FROM orders o
JOIN ANY customers c ON c.id = o.customer_id;

This is today’s LEFT OUTER JOIN, expressed as intent, not machinery.

“Join customers if they exist. Missing rows are acceptable.”

Meanwhile, a plain JOIN remains strict:

“Only include matches. Absence means exclusion.”

It’s the same rule, consistently applied.

UPDATE ANY and DELETE ANY – idempotent operations

The same expectation logic applies to modifications.

By default, UPDATE and DELETE should fail if no rows are affected —
because it means you targeted something that didn’t exist.

DELETE users WHERE id = 1234;
-- ❌ Error: no matching user

But sometimes, that’s fine — maintenance scripts, cleanup jobs, cache resets.

DELETE ANY users WHERE id = 1234;
-- ✅ 0 rows affected, no error

ANY makes it intentional:
You tried. Nothing happened. No problem.

Same with updates:

UPDATE ANY users SET active = 0 WHERE last_login < '2024-01-01';

Still transactional, still safe — just explicitly tolerant.

The unified philosophy

CommandStrict defaultANY variant
SELECTError if 0 rowsAccept empty result
JOINInner join (expect matches)Outer join (accept missing)
UPDATEError if 0 rows updatedSkip silently
DELETEError if 0 rows deletedSkip silently

In every case, ANY means absence is acceptable.

This creates a consistent mental model:

  • “If I say nothing, I expect data.”

  • “If I say ANY, I accept none.”

Why it matters

Modern SQL hides intent behind silence.
You never know whether a query’s emptiness was expected or not.

With this model:

  • Code becomes self-documenting.

  • Bugs surface immediately when expectations are violated.

  • Optional behavior is clearly marked, not implied.

It’s not just syntax — it’s semantics made visible.

Backward compatibility

No existing queries break.
We’re just adding new, optional intent markers:

  • SELECT ANY is today’s tolerant select (the default behavior, made explicit).

  • Plain SELECT becomes strict.

  • JOIN ANY maps to LEFT OUTER JOIN.

  • UPDATE ANY and DELETE ANY simply suppress “no rows” warnings.

  • DELETE table (without FROM) is pure syntactic cleanup.

Zero surprises. Total clarity.

In one sentence

“State your expectation — not your workaround.”

That’s what SQL should have been doing all along.


Kommentarer

Send en kommentar