Comparative analysis of cases when and decode

First, case

Similar to If-else, the statement is as follows:
Case expr when Expr1 then RETURN_EXPR1
[When Expr2 then RETURN_EXPR2
...
When Exprn then RETURN_EXPRN
ELSE else_expr]
END

And there are two methods of judging, case field when value then return value

Else return value end

For example:

Select Bname, price, case at price > =10 and Price <20 then ' Price1 '

When Price > =20 and Price <30 then ' Price2 '
When the price >= and the price <40 then ' Price3 '

When Price > =40 and Price <50 then ' Price4 '

When the price >= and the price <60 then ' Price5 '

Else ' price6 ' End ' Price segment '
from book;

Second, decode (Oracle database Exclusive)

DECODE (Col|expression, Search1, RESULT1
[, SEARCH2, RESULT2,...,]
...
[, Searchn, Resultn,...,]
[, default])

can also be used with the sign function

can also: decode (field, judging condition, return value 1, return value 2)

Select Decode (ARG1-ARG2), -1, Arg1, arg2) from dual;

Note: The sign () function returns 0, 1, 1, depending on whether a value is 0, positive, or negative

Select Price,decode (Price, ' 32.5 ', ' alive ', ' other ') titles from the book;

Third, Comparison

1.DECODE is unique to Oracle;

2.CASE when is Oracle, SQL Server,mysql is available;

3.DECODE can only be used for equal judgment, but may match the sign function for greater than, less than, equal to the judgment; case can be applied to =,>=,<,<=,<>,is null,is not null;

4.DECODE uses it to be concise, while case is complex but more flexible.

Comparative analysis of cases when and decode