SQL 案例与解码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3193692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CASE vs. DECODE
提问by mcha
回答by Cheran Shunmugavel
There is one big difference between DECODEand CASEand it has to do with how NULLsare compared. DECODEwill return "true" if you compare NULLto NULL. CASEwill not. For example:
之间存在一个很大的区别DECODE，并CASE和它与如何做NULLs比较。 DECODE如果NULL与NULL. CASE将不会。例如：
DECODE(NULL, NULL, 1, 0)
will return '1'.
将返回“1”。
CASE NULL
WHEN NULL THEN 1
ELSE 0
END
will return '0'. You would have to write it as:
将返回“0”。你必须把它写成：
CASE
WHEN NULL IS NULL THEN 1
ELSE 0
END
回答by Andy Robinson
As always with Oracle ... AskTom...
一如既往地使用 Oracle ... AskTom...
From this post...
从这篇文章...
Decode is somewhat obscure -- CASE is very very clear. Things that are easy to do in decode are easy to do in CASE, things that are hard or near impossible to do with decode are easy to do in CASE. CASE, logic wise, wins hands down.
解码有点晦涩——CASE 非常非常清楚。在解码中容易做的事情在 CASE 中很容易做，在解码中很难或几乎不可能做的事情在 CASE 中很容易做到。案例，逻辑明智，赢得了胜利。
From a performance point of view seems they are about the same, again above article mentions some speed differences but without benchmarking the particular statements it's hard to say.
从性能的角度来看，它们似乎大致相同，上面的文章再次提到了一些速度差异，但没有对特定语句进行基准测试就很难说。
回答by Brahmareddy K
CASE is a statement and DECODE is a function We can use the CASE in the where clause and can not use the DECODE in the where clause. DECODE can check equality operators only where as CASE can support all relational operators DECODE can be used in sql only where as CASE can be used in SQL AND PL/SQL CASE is better than DECODE.
CASE 是一个语句，DECODE 是一个函数 我们可以在 where 子句中使用 CASE 而不能在 where 子句中使用 DECODE。DECODE 只能检查相等运算符，因为 CASE 可以支持所有关系运算符 DECODE 只能用于 sql 中，因为 CASE 可以用于 SQL AND PL/SQL CASE 优于 DECODE。
You can find more: http://www.oraclegeneration.com/2014/01/sql-query-interview-questions.html
你可以找到更多：http: //www.oraclegeneration.com/2014/01/sql-query-interview-questions.html
回答by Vinoth Karthick
From performance perspective, In Oracle decode and CASE does not make any difference.
从性能的角度来看，In Oracle decode 和CASE 没有任何区别。
But in Exadata , Decode is faster than CASE.
但在 Exadata 中，Decode 比 CASE 快。
The Decode operation is done at storage Server level where the data is present BUT CASE is done at DB Instance level which receives data from DB storage Level.
解码操作在数据存在的存储服务器级别完成，但案例在从数据库存储级别接收数据的数据库实例级别完成。
Though that network transfer of data between Storage and DB server is less (Infiniband connection), that transfer is avoided when you use decode statment
虽然存储和数据库服务器之间的网络传输数据较少（Infiniband 连接），但使用解码语句时可以避免这种传输
回答by Goutam
Both the NULL to NULL Comparison returns the value as 1 instead of DECODE returning the value of 1 and CASE returning as 0.
NULL 到 NULL 比较返回值 1 而不是 DECODE 返回值 1 和 CASE 返回值 0。
回答by sumit
select (DECODE(NULL, NULL, 1, 0)) from dual;
select (CASE
WHEN NULL IS NULL THEN 1
ELSE 0
END
)
from dual;
both return 1
都返回 1
