Pl sql decode if null

The above query statement works similar to the logic of following the if-else logic program shown below —. This is because when the comparison of the first two arguments evaluates to false, then as no default value is mentioned, the last parameter, the return value by default for false evaluation, is NULL which is given as output.

The working of the above decode function is internally similar to following if-else condition —. We can also provide more than one search expression for comparison; in that case, our decode function will be behaving the same as that of the if-else if ladder.

Let us consider an example for that scenario as well. Let us try executing the following query statement of decode function —.

We can even try providing the default value when neither of the search expression matches with the specified expression. Consider the following query statement —. Even though in all other cases, when you compare a null value with a null value, they are not treated as equal while using the DECODE function if you try to provide the NULL in first as well as any of the search expressions, the corresponding return value is returned as output which means that the two null values are treated equal.

We can even specify the default value for false evaluation. You may also have a look at the following articles to learn more —. Submit Next Question. By signing up, you agree to our Terms of Use and Privacy Policy. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Stack Gives Back Safety in numbers: crowdsourcing data on nefarious IP addresses.

Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Related If they are not equal, the first parameter value is returned. In our test table the values of COL3 and COL4 are equal in row 4, so we would only expect null returned for that row using the following query. It is used in a where clause to evaluate a condition. If this condition evaluates to false or unknown, it returns true. If the condition evaluates to true, it returns false.

The following table will be used to demonstrate it. It is possible to make this happen using the NVL and DECODE functions, but depending on how you use them this relies on you converting the null value to another value that you hope will never be present in the column or variable.