SQLite的CASE语句贵吗？

I'm wondering if using a CASE statement in SQLite (or other SQL engines) to replace data is not advised. For example lets say I have a query.

我想知道是不是建议在SQLite（或其他SQL引擎）中使用CASE语句来替换数据。例如，假设我有一个查询。

SELECT Users, 
                CASE WHEN Active = 0 THEN 'Inactive'
                        WHEN Active = 1 THEN 'Active'
                        WHEN Active = 2 THEN 'Processing'
                        ELSE 'ERROR' END AS Active
FROM UsersTable;

When is it better to create a reference table and perform a JOIN. In this case I would create a Table 'ActiveStatesTable' with ActiveID, ActiveDescription and perform the JOIN.

何时更好地创建引用表并执行JOIN。在这种情况下，我将使用ActiveID，ActiveDescription创建一个表'ActiveStatesTable'并执行JOIN。

3 个解决方案

#1

The CASE statement is preferred syntax:

CASE语句是首选语法：

It's ANSI (92?), so it's supported on MySQL, Oracle, SQL Server, Postgres... unlike database vendor specific IF syntax
它是ANSI（92？），所以它支持MySQL，Oracle，SQL Server，Postgres ...不像数据库供应商特定的IF语法
It supports short ciruiting - the rest of the evaluation is not performed once criteria matches
它支持短周期 - 一旦标准匹配，其余的评估不会执行

#2

Doing the separate table and JOIN is definitely the cleaner way to write this code. What happens, for example, if you want to write another query with the same mappings? You'd have to copy the CASE statement into the new query, and copy duplication is bad. What happens if you need to add a new Active state?

单独执行表和JOIN绝对是编写此代码的更简洁方法。例如，如果要使用相同的映射编写另一个查询，会发生什么？您必须将CASE语句复制到新查询中，并且复制复制很糟糕。如果您需要添加新的活动状态会发生什么？

Performance-wise, both the JOIN and the CASE should be fairly cheap. CASE might be slightly more performant because of short-circuiting of evaluation and the few cases, but JOIN is, in my opinion, the cleaner and more flexible and more SQL-ey solution.

在性能方面，JOIN和CASE都应该相当便宜。由于评估的短路和少数情况，CASE可能会稍微提高性能，但在我看来，JOIN更清晰，更灵活，更具SQL-ey解决方案。

#3

CASE should be much cheaper as it should not involve any I/O, but for small tables JOINs are not that expensive either (but do test it).

CASE应该便宜得多，因为它不应该涉及任何I / O，但对于小型表，JOIN也不是那么昂贵（但要测试它）。

The question is if you will need to maintain this CASE in several queries and will you need to establish any referential integrity on it.

问题是，您是否需要在多个查询中维护此CASE，并且需要在其上建立任何参照完整性。

#1