SQL tutorial 50: CASE - Searched Case Expression In Oracle (2/2)

Key Takeaways

what's up internet as we discussed in the previous tutorial that there are two types of case expression in Oracle database first is simple case expression and the second is search case expression we have discussed and leared the concept of simple case expression in our previous tutorial now we are left with search case expression which we are going to learn in today's tutorial search case expression is one level higher than simple case expression in term of complexity but if we talk about flexibility then search case expression is very flexible in comparison of simple case expression and decode expression so let let's move ahead and take a quick look at the syntax of search case expression syntax of search case expression is very similar to the syntax of simple case expression but unlike simple case expression we don't have any search expression followed by case keyword also here we have conditions instead of expression followed by when keyword the conditions here are equivalent to the test that you want to perform on the data in order to test the data you can either use SQL comparison operators or logical operators you can also compare values of multiple Columns of a table by using B condition the output result of search case expression completely depends on the evaluation of condition if a condition gets true then its corresponding results get return let's try to better understand the concept of search case expression through the help of queries but before that let me connect to my database using my local HR user and here we are let me quickly show you the user as you can see the user is HR okay let's close this script output and also delete this query for the demonstration I have already created a table by the name of superheroes let me show you as you can see this table has two columns first name and last name and I have also populated this table but this time with the real name of these superheroes as you can see here now the task is to reveal the true identity of these Heroes using search case expression for example if the first name is Clark and the last name is Kent then he's a Superman and so on let's write the query but before that let me close this query result and this script output also for okay let's format this query to format this query simply press control and function F7 or F7 key and here we go let me also enclose this case block inside the parenthesis and I also want to give the name to the column of this case block okay everything is perfect in this query we have four V cases or see V and then pairs in each V case we used both The Columns of our table superheroes along with the and SQL logical operator on executing this query Oracle engine will scan all the when cases or when and then pairs from left to right and search for the true condition as we have used and logical operator does it means that the condition on both side of and operator needs to be true in order to return the result from then Clause otherwise result from else Clause will be returned let's execute this query and here is our result identity of all these Heroes has been revealed similarly you can use other logical operators hope this query has made this text and purpose of search case expression clear to you okay let's move on to our second query since the first query was very simple therefore let's make a query which will be a bit complicated in this query we will see how to write case using single column of a table and how to use comparison operators along with operators such as between and in also we will see how we can mix all these things in a single case expression okay let's write the query but before that let me again close this query result and also let me make some space okay I think everything is perfect now so let's start writing our second query okay let's again format this query just press CR F7 okay let's move it slightly up and again enclose this case inside this parenthesis and give it a name okay in the first V case we use single column of the table with less than equal to comparison operator and in second when case we use two different Columns of the table employees with equal to and is comparison operator along with or logical operator finally in the last two when cases I Tred to demonstrate to you how you can use between and in operator insert case expression and alog together everything in a single case block the purpose of doing this query is to demonstrate that how you can use different operators in making when condition and make the concept of search case expression clear to you guys let's execute this query and here here is our result hope you find this tutorial useful like always I did a Blog explaining all the aspect of this concept you can read that blog link is in the description box that's it for today hope you enjoyed watching please hit the like button that makes me really happy and keeps me motivated and yes don't forget to subscribe we'll see you soon with another short and crispy squl tutorial till then take care this is Manish