How to use case when expressions and decode functions correctly?

I believe that many partners in the development process are useful to case when expressions and decode functions, then there will be a small partner like me at first have a lot of doubts, when to use case when, when to use decode? What's the difference between the two? Today, the editor will take you to subdivide the difference between the two: case when expression can not only be equivalent to the connection can also be judged in the range; decode function can be equivalent to the connection. You may not understand this, for example, as follows; create a table tmp1, listed as dept to show department information, there are 10, 20, 30, 40 and 50 departments respectively:

Judged by case when expression, if department information is 10, it is A, department information is 20, it is B, and other department information is C: select case when dept = 10 then'A 'when dept = 20 then' else'C 'from tmp1. Or: select case dept when 10 then 'when 20 then' B'else'C'from tmp1; is judged by the decode function. If the department information is 10, it is judged to be A, and if the department information is 20, it is judged to be B, and the other department information is judged to be C: select decode (dept,10,'A',20,'B','C') from tmp1. If you want to judge the department information as 10 or 20:00, it will be judged as'A', when the department information is 30 or 40, it will be judged as'C', and the rest will be judged as'C'. Only the case when expression can be used: select case when dept between 10 and 20 then'A' when dept between 30 and 40 then'B' else'C' end from tmp1 Note: at this time, sql can no longer be written as select case dept when between 10 and 20 then'A' when between 30 and 40 then'B' else'C' end from tmp1; then an error will be reported: ORA-00936: missing expressions this article is over, have you learned? If you have any questions, you can leave a message to the editor and reply when you see it!