DEV Community

loading...

Discussion on: How to add IF-ELSE logic to SQL queries

geraldew profile image
geraldew

You may find that this depends very much on the platform. I feel I can attest from experience that on Teradata the optimisation definitely favours the CASE expression as long as it doesn't get so overly complex as to blow the memory of the distribution element (Teradata has its own internal terminology which I won't quote).

In that vein, while I wouldn't defend it, I've certainly seen CASE expressions with a hundred WHEN clauses implementing the quirky logic combinations that come from bureaucratic classifications. There are times when any attempt to abstract that to a function or a table join would be even more unwieldy and introduce more overheads than the CASE expression.

Thread Thread
zoedreams profile image
☮️✝️☪️🕉☸️✡️☯️

great tools come with great responsibility. :) Thank you for that reply, useful.. :)

Thread Thread
toddtoddowens profile image
Todd M Owens

CASE statements will perform fine on most SQL RDBMS engines. It is much better to have these rules in a view or stored procedure than embedded in client code. Even better, create Rules tables that treat the metadata as data and use JOINs in place of CASE statements.

I have tried to avoid speaking in terms of absolutes, but here is one that comes close:
Almost never embed SQL queries that call dedicated back end database server objects in modules of an imperative language like JAVA.

Thread Thread
geraldew profile image
geraldew

Well, there's an irony there. If we set aside performance issues for a moment, the advantage of moving the conditional evaluation from a "hard coded" CASE expression to "rules tables" (I'd probably call that "control tables" personally) is that you can then conveniently change this by editing the data instead of recoding.

Except, often the editing of that data requires building more infrastructure and arranging access rights, whereas once the CASE expression is in a view or stored procedure, usually whoever had access to write the view or stored procedure can readily replace it - even (gasp) in production.

Personally I'm with you though, my ideal is to write/build analytical systems where the code (SQL) is comprehensive and settled, and all behaviour modification is done by editing control data. Alas I usually seem to be alone in that ambition.