SQLite: Something like Oracle Decode or MS "If"

Question

In the past, I've used "decode" in Oracle, and "iif" in Microsoft, in the SELECT stmt of an SQL query.

I've been considering SQLite for a project, but as far as I can tell, conditionals in the SELECT stmt are difficult. Googling brings up the use of CASE.. but intuitively, that seems that the performance would not be very good, not to mention more difficult to code. Or maybe I don't fully understand it.

Thoughts or advice from anyone? I'd really like to use SQLite for this project.

Solution

I think case is just as easy to use. It's familiarity with syntax that may be buggin you but this works just as well.

CASE Gender WHEN 'F' then 'FEMALE'
            WHEN 'M' then 'MALE'
            ELSE 'UNDEFINED'
            END as GenderName

or

CASE WHEN GENDER = 'F' THEN 'FEMALE'
     WHEN GENDER = 'M' THEN 'MALE'
     ELSE 'UNDEFINED' 
     END as GenderName

It should be noted that CASE is pretty much db agnostic: Decode, iif, if, and other variations are not. So if your query needs to work on several different databases; stick to case as it's more likely to work across multiple database platforms.

With regards to performance & Why have both: SQL Server IIF vs CASE

• I've read articles stating the generated execution plan is the same for both.

• I've read articles stating the difference between the two is nano-seconds.

• I've also read articles stating case is slightly faster because the engine basically switches the iif to a case; so iif has overhead.

• I've read articles that state iif is faster because it's a binary check and is only ever going to return true or false (or null) so it has less overhead.

• I've seen where you can only nest up to 10 for each type. I've also seen where someone indicated you can have 12 nested values...

• I've seen some state if they need true/false result and it's a just a single comparison to only use IIF, if multiple, case.

• To know which is better: testing is required for specific RDBMS/Version.

• From a personal standpoint... I prefer CASE due to the multiple checks it supports and I personally find it easier to read. But if you just work in MSFT environment: you may find the inverse to be true.

Comments

[xQbert]

Appears dup of: stackoverflow.com/questions/6518389/…

[dan04]

Also, stackoverflow.com/questions/4874285/…

[felixgaal]

Look at stackoverflow.com/questions/6518389/…

[Ken White]

Please do at least a basic search here. A search on [sqlite] decode turned up xQbert's question, and a search on [sqlite] iif turned up dan04's. Searching to see if others have already asked the same question reduces duplicates and noise, and helps keep SO a useful resource. The search functionality is pretty straightforward, and the documentation is pretty good as well. Thanks. :)

[Paulb]

Not to be argumentative.. I worded my initial question poorly, I'll take the heat for that. I'm also interested in performance issues. I'm working a db with 2 million rows. So far, I like xQbert's reply as it explains CASE better than other examples I have read. Will it be slower than DECODE or IIF?

[xQbert]

no silver bullet here but it should be on par to performance of the other two. Now, if you're dealing with null values and want to evaluate them and use first non-null value... then look at coelesce...

[Bob Jarvis - Слава Україні]

FWIW I've used both DECODE and CASE with Oracle and have noticed no obvious performance differences, although I haven't conducted any lies^H^H^H^Hbenchmarks. From a readability standpoint CASE wins hands down, IMO.

[Paulb]

Thank you. Actually, your explanation is clearer than others others I have read.

[Paulb]

Do you think this will run as efficient as DECODE or IIF.. and I realize this is a difficult question to answer without lots of specifics. Generalities are OK..

[xQbert]

In general, performance should be on par to Decode and IIF. CASE is similar to IIF and decode. "run as efficient" well as to that all I can say is try it. There's so many variables to consider that I'd not fathom a more robust answer. You're talking different database engines, different compliers, different builds... FOOD FOR THOUGHT: When putting up a picture does it matter if the hammer is made by Stanley or Craftsman? just so long as you don't use a monkey wrench. Your only other option I know if is to build a custom function for SQLite. (links above show that option)

[Paulb]

Thank you. Your reply kind of meets what I thought might be.. but it was important for me to hear someone else say it.

[xQbert]

Not at all; SQLite docs indicate Case is the iif/decode equilivants