Stop writing business logic in SQL joins

Put your hand up if you have a database query in your company that is business critical, but incredibly complex and opaque.

Keep your hand up if once a quarter, some ambitious member of the team tries, and fails, to understand or refactor it (I imagine you’re looking a bit silly with your arm up right now).

I imagine the query you’re thinking about is terrifying. It touches more tables than anyone remembers, and the joins have to be in a particular order or the result changes. There is a comment somewhere near the top saying “do not refactor this”, and overall, no one has any idea how it works.

Seniors copy it when they need something similar, then adjust it carefully, hoping they have not broken anything subtle, while new hires are told to flat out avoid it at all costs. Over time it starts being treated as sacred within the company.

In analytics, that tradeoff might be worth it, but in operational systems where correctness matters, treating business logic as an emergent property of JOINs is a design failure.

When a query becomes the most authoritative description of how your system behaves, something has gone wrong somewhere, and it’s hard to figure out quite why it happened.

When queries stop being retrieval

Consider a supply chain system as an example. You want to answer a question that sounds straightforward on the surface:

Which suppliers can provide high-risk components for Project Alpha?

Nothing about that question is exotic, and it is the kind of thing product managers ask in meetings without realising what they are asking for.

In a relational database model, “high risk” is typically a conclusion which is drawn from a number of variables rather than an innate system fact.

To get there, your query has to piece the world back together:

• find Project Alpha
• join to its bill of materials
• join to the components
• join to the suppliers
• check which components are critical
• check which suppliers have expired certifications
• check whether the supplier region is compatible with the project region
• exclude anything deprecated, archived, or partially onboarded

None of that logic exists in one place, because each has different structures and concepts, so they’re normally a selection of different, but somehow interconnected tables. The logic only exists because you assembled it, in that order, inside a SELECT statement.

This is the core problem: meaning is being reconstructed, and when meaning lives in JOINs instead of structure, truth is a coincidence.

Of course, many teams abstract this logic into views, functions, or stored procedures. These help – especially early on- but over time you end up with dozens or hundreds of scattered abstractions, each encoding slightly different assumptions. They often become just as hard to manage as the queries they replaced, and the result is that the complexity has moved, rather than gone away.

It seems like the database is telling you which suppliers are high risk. It isn’t really doing that though. Instead, you are working it out manually, every time you ask.

If another engineer writes a similar query tomorrow and forgets one of those checks, they will get a different answer. Both queries will be technically valid, and both will return rows. Only one of them will reflect reality (well, hopefully. I’ve had times where several engineers have written different queries, all with different results, and none of us quite sure which is correct, if any)

At that point, the “truth” of the system has gone from an immutable fact, to a theoretical concept, which is hopefully identifiable. It’s easy to see why this creates expensive, and time-consuming challenges.

Declarative syntax, procedural meaning

We like to say that SQL is declarative. You describe what you want, rather than how to compute it. That is true at the level of execution plans, but it’s not true at the level of domain meaning.

When you write:

JOIN components ON components.id = bom.component_id

You are specifying how two concepts are related in this query. If there is another way they can be related, or another constraint that applies, it is your responsibility to remember it and encode it.

As systems grow and evolve, those responsibilities pile up.

• A supplier might be a company or an individual.
• A region might be a city, a state, or a country.
• A component might only be considered high risk in a specific project context.

Each of those wrinkles turns into more joins, more filters, more conditions that must be present for the answer to be correct.

The query still looks like data access, but in reality, it is performing interpretation.

Correctness by human memory

One of the highest costs of this approach is in the cognitive load. You can make the individual queries performant, but holding and maintaining the context to assemble them becomes almost exponentially difficult.

When behaviour lives in queries, correctness depends on engineers remembering how the system works, and being able to share that knowledge across teams, and to new hires.

• Did you remember to exclude soft-deleted records?
• Did you remember that contractors do not have a salary, only a rate?
• Did you remember that Project Alpha uses the newer classification scheme?

If you forget, the query will still work, so you won’t even know that it isn’t giving you the correct answer unless you take the time to be sure.

This is why experienced backend engineers are cautious around reporting queries and entitlement checks. They know that the logic is encoded indirectly, scattered across joins and predicates. Changing one part can invalidate assumptions somewhere else.

At a certain scale, nobody trusts the result unless they have personally inspected the SQL.

Why “better SQL” does not fix this

When teams notice this pattern, they usually try to improve the queries They take meticulous time to refactor. They add fixed, replicable views, and above all, they add comments. So many comments. Sometimes they wrap the logic in a function and call it from multiple places. Then they hope that the data model stays fixed..

All of that helps a little, but it doesn’t solve the root cause.

The root cause is that the database does not know the structure of the domain. It knows tables and keys. It does not know suppliers, components, projects, or risk in any meaningful sense, so every query has to rebuild that structure from scratch.

Putting structure where it belongs

What works better, in practice, is moving meaning out of the query and into the foundational structure of the model.

• If “Supplier” is a concept with variants, that should be reflected directly instead of patched with LEFT JOINs and COALESCE calls.
• If “High Risk” is a property of a component in a particular context, that relationship should exist explicitly, not be inferred from a chain of filters.
• If certain combinations are invalid, they should be impossible to represent, not filtered out after the fact.

When the structure of the database and the model is right and robust, the query becomes boring and simple again. This doesn’t mean your system has no complexity. It means that the complexity is expressed as structure instead of process.

Instead of spelling out every step, you can just ask your question and trust the schema has your back. You can explore the schema and understand how concepts relate together, and see the inherent architecture.

A smell worth paying attention to

I’m not saying that long queries are always bad. Sometimes the domain really is complex, and it takes more language to define what you need.

But if you regularly find yourself writing large, fragile SELECT statements to answer questions that sound simple, it is worth pausing before optimising the SQL.

Very often, you are not fighting a database; You are compensating for a database model that does not encode enough meaning.

The complexity moves to the place where it belongs. Into the model, where it is defined once and enforced consistently.

Some teams try to formalise this by introducing a “semantic layer”, a separate abstraction that describes domain logic on top of the raw data. That can work, but only if the underlying model is expressive enough to support it. And even when it does work, the added abstraction can introduce its own maintenance overhead, requiring engineers to reason about yet another translation layer. Otherwise, it becomes just another place to duplicate logic and hope nothing drifts out of sync.

Ideally, the model should encode its own meaning. Queries shouldn’t have to reconstruct it every time they run.

And as we’ll explore next, when structure leaves the database entirely and ends up in your application code, the costs multiply fast.