SQL tutorial 49: CASE - Simple Case Expression in Oracle Database (1/2)

Key Takeaways

what's up internet welcome back once again I'm Manish from Rebellion rider.com today in this SQL tutorial we are going to learn Oracle case expression using case expression you can achieve the if then else logic in Oracle SQL that to without invoking any kind of SQL procedures case expression can do all the works of decode expression several books recommend using case over decode because case expression are easier to read in comparison of decode expression and also case expression is UNC compilant and forms part of the sql92 standard in Oracle there are two types of cases expression first is simple case expression second is search case expression let's try to understand these two case expression with the help of some queries but if you want to make some notes and read more about this topic then refer to the blog on my website which I have done just for you please have a look link is in the description box okay let's move ahead first let me connect to my database using my HR user here I am let me show you the user and as you can see the user is a CH okay the first case expression is simple case expression as the name just it is the simplest version of case expression in the other word we can say it is a flexible and the easiest version of the decode expression the syntax of simple case expression is simple case expression uses search expression to determine the return value means simple case expression evaluates all the input expression against the search expression Once A match is found the corresponding result is returned otherwise else result is displayed furthermore the else statement is optional so in case you omited then null is displayed when the simple case expression cannot find a match let's dig deeper at this concept using some queries for the demonstration I have created a table by the name of product let me show you as you can see this table has two columns product ID and company name but there is no column saying anything about product manufactured by these company so let's see how we can display the most manufactured product by this company by using simple case expression okay let me close this script output okay let's write the query okay let's format this query first if you want you can enclose this case block inside the parent syis like this okay here in the case block product ID column of the product table is our search expression and all the data of product ID column is our input expression the Oracle engine will try to evaluate all these input Expressions against the data from the product ID column if there is any match then it will return the corresponding result value for example when there is a value P1 in product ID column then Oracle engine will return cameras as the result for a better understanding let's execute this query and we got an error let me check oh sorry I forgot to put the comma here now let's execute once again and here is our result okay as you can see the column name of our case block is looking really ugly for that what we can do we can provide a column name to our case block okay let's execute once again okay now everything is okay as you can see the company corresponding to product ID P1 is Canon which produces cameras and similarly company corresponding to product ID P2 is luxara which produces I and so on but if you look at the last row of the result where product ID is P6 there we have the result WR from lse statement because there is no match for product ID P6 as there is no when and then pair for product ID P6 okay let's move ahead and write our second query let me close this query result whenever you specify a column name of a table as search expression as we did in our query one the Oracle engine treats all the data from this column as an array and matches all the input expression with every element of this array and if a match is found it Returns the corresponding result values in case you specify a string instead of a column name as a search expression then Oracle will find the first best fit match and return the corresponding result value so what is a first best fit match to answer this question let's write a query and see what does that mean okay let me first make some space here okay let's write our second query okay let me again format this query first let me again enclose this case block inside the parenthesis and here in the simple case the search expression is dog along with the four when then pairs the input expression of second and fourth pair is string dog which is a perfect match for our search expression but on executing this query Oracle engine will return the result value from the first best match which in our case is second pair let's execute as you can see the result is from our second pair let's take a look at the else statement of simple case as I have already told you that when there is no match for search expression then Oracle engine will Returns the AL statement for the demonstration let's change the input expression of second and fourth pair from dog to monkey and frog respectively let's close this query result okay now let's execute as you can see our result is sorry which is our else statement but else statement is optional and if you omit this then Oracle engine will return null let me first close this query result so let's delete this else statement and then execute the query again and as you can see see this time result is null there are few things which you must take care of while writing a case expression first the data type of all the input expression from 1 to n should match with that of search expression as well as with each other second similarly the data type of all the result from 1 to n along with the lse results should be the same for more example and in-depth knowledge of this concept go ahead and read my blog link is in the description box that's it for today's hope you learned something please hit the like button and don't forget to subscribe we'll see you soon with the second video video on this topic till then take care this is Manish