Oracle SQL_CASE Function

admin Oracle , ,

Hello guys,
Hope you are doing well. I am back once again with a detailed blog on Oracle CASE function.
This is my favourite function whenever I need to evaluate multiple conditions. It is same as if-then-else PL/SQL block (we will discuss it in up-coming PL/SQL blog series) but we can use it in SQL statements.
When we use CASE function in any SELECT statement, it adds an additional column to the output and we can name that column whatever we want after the AS keyword. But don’t forget, this column is not added to the table definition. It is added just in the output for this particular SELECT statement.

In order to use, there are two types of CASE functions. We will discuss them one by one.

1. Simple_case_expression:=

CASE expr WHEN comparison_expr THEN return_expr
ELSE else_expr END as “col_name”

In a simple CASE expression, Oracle Database searches for the first WHEN … THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN…THEN pairs meet this condition and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.

In the above syntax, expr is a column of a table and comparison_expr, return_expr and else_expr can be column name of a table or any specific char/number value. Also, we can apply any functions as per the requirement.

For the demonstration, I have created a very simple table COUNTRY_NAME with only single column which is Country_id in Oracle database.
Let’s check that table first.

So let’s add another column COUNTRY using the CASE function in the SELECT query and see how it works.

You can see in the output that we have a country column which has country names for all country_ids but not for NL. It is because in the CASE function, we have not provided comparison_expr as NL so CASE function added else_expr as UNKNOWN.

2. Searched_case_expression:=

CASE WHEN condition THEN return_expr
ELSE else_expr END as “col_name

In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.

Have a look at the below code snippet. SQL statement is little different than the previous one but the Output is same.

Let’s do one more example for better understanding of Search case expression.

Do you remember PRODUCT_INFORMATION table which we created while learning COALESCE Function? If you haven’t read yet, please click here and do read.

So today, we will try to achieve the same output using CASE function.

Please comment below and let me know if there are any differences in the out output.

If you have any questions please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on social media.
For more updates join my facebook group and do like my facebook page.

Thank you,

Sharing is caring!

You May Also Like..


Hello guys, Hope you are doing fantastic being an Oracle professional. I wrote a blog on Oracle TABLESPACE. If you […]

Oracle Virtual Column

Hello guys, Hope you are doing well. Today, we will talk about the Oracle Virtual Column. I will try to […]


Hello guys, Hope you are doing well. Today, we will talk about Oracle Tablespace and I will try to cover […]


  1. Wow, amazing blog layout! How long have you been blogging for?
    you made blogging look easy. The overall look of your web site is excellent, as well as the content!

  2. hello!,I really like your writing so a lot! share we keep up a correspondence extra about your article on AOL?
    I require an expert in this house to resolve my problem.
    Maybe that’s you! Taking a look ahead to look you.

  3. Nice post. I was checking constantly this blog and I am inspired!
    Very helpful info specially the ultimate part 🙂 I handle
    such info a lot. I was seeking this certain info for a very
    lengthy time. Thanks and good luck.

  4. Your back up plan can also work as a safety net such a predicament.
    Also, do write in a way is going to also help both, the person and the recipient.

    There are about five different forms of online
    dating services.

  5. Its like you read my mind! You seem to know so much about this,
    like you wrote the book in it or something. I think
    that you could do with a few pics to drive the message home
    a bit, but other than that, this is great blog.
    A great read. I’ll definitely be back.

  6. It is perfect time to make some plans for the long run and it’s
    time to be happy. I’ve learn this put up and if I
    could I want to recommend you some attention-grabbing things
    or advice. Maybe you could write subsequent articles
    regarding this article. I desire to learn even more issues about it!

  7. {I’ve been browsing online more than 4 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. Personally, if all website owners and bloggers made good content as you did, the net will be much more useful than ever before.|

  8. Thanks for sharing excellent informations. Your site is so cool. I’m impressed by the details that you have on this web site. It reveals how nicely you understand this subject. Bookmarked this web page, will come back for more articles. You, my pal, ROCK! I found just the info I already searched everywhere and simply couldn’t come across. What an ideal website.

  9. What i don’t understood is if truth be told how you are no longer actually much more neatly-appreciated than you may be now. You’re very intelligent. You already know thus considerably in terms of this matter, made me for my part consider it from numerous numerous angles. Its like women and men aren’t involved until it is one thing to do with Girl gaga! Your own stuffs outstanding. Always handle it up!

  10. Hello! I could have sworn I’ve been to this blog before but after reading through some of the post I realized it’s new to me. Anyhow, I’m definitely delighted I found it and I’ll be book-marking and checking back often!|

  11. What i do not understood is if truth be told how you are now not actually much more well-liked than you may be now. You are so intelligent. You recognize therefore considerably with regards to this subject, made me in my view imagine it from so many various angles. Its like men and women aren’t involved until it’s something to accomplish with Woman gaga! Your personal stuffs outstanding. All the time handle it up!

  12. Exceptional post however I was wanting to know if you could write a litte more on this topic?
    I’d be very thankful if you could elaborate a little bit further.
    Many thanks!

  13. What i do not understood is in reality how you are not actually a lot more well-appreciated than you may be right now. You are very intelligent. You realize thus significantly in terms of this topic, made me for my part consider it from a lot of various angles. Its like women and men are not involved unless it’s one thing to accomplish with Girl gaga! Your own stuffs great. At all times maintain it up!

  14. Thanks on your marvelous posting! I genuinely enjoyed reading it, you’re a great author.I will make certain to bookmark your blog and will eventually come back from now on. I want to encourage you to continue your great posts, have a nice weekend!

  15. What i don’t realize is in reality how you’re not really a lot more neatly-appreciated than you might be right now. You’re very intelligent. You understand therefore considerably in relation to this subject, made me personally believe it from so many varied angles. Its like women and men aren’t fascinated except it’s something to do with Girl gaga! Your own stuffs excellent. At all times care for it up!

  16. I’m impressed, I have to say. Actually not often do I encounter a weblog that’s each educative and entertaining, and let me inform you, you could have hit the nail on the head. Your idea is excellent; the difficulty is one thing that not enough persons are speaking intelligently about. I am very happy that I stumbled throughout this in my search for one thing regarding this.

  17. I am curious to find out what blog platform you happen to be utilizing? I’m experiencing some small security issues with my latest site and I’d like to find something more secure. Do you have any suggestions?|

  18. Hey would yoou mind stating which blog platform you’re using?
    I’m goiong to start my own bloog in the near future but I’m having a tough
    time deciding between BlogEngine/Wordpress/B2evolution aand Drupal.
    The reason I ask is because your design and style seems different then most blogs and I’m looking
    for something completely unique. P.S Apologies for being off-topic but I had to ask!

  19. I’m impressed, I must say. Really hardly ever do I encounter a weblog that’s each educative and entertaining, and let me tell you, you might have hit the nail on the head. Your concept is excellent; the problem is one thing that not sufficient individuals are speaking intelligently about. I’m very completely satisfied that I stumbled across this in my search for one thing regarding this.

  20. We are a bunch of volunteers and opening a brand new scheme in our community.
    Your web site provided us with valuable information to work on.
    You’ve done a formidable task and our whole group will be thankful to you.

  21. Etimesgut nakliyat firmalarıyla çalışmaya başladığımız ilk günden beri platformumuz birçok değişikliği devreye almıştır. Etimesgut nakliye veya Etimesgut evden eve nakliyat için en kapsamlı iş planlamasıyla firmalarımızın yola çıkmalarını yani işlerini yürütmelerini sağladık. Burdaki amacımıza kısa sürede kolayca ulaştık.

  22. Hey There. I found your blog using msn. This is an extremely well written article.
    I’ll make sure to bookmark it and return to read more of your useful information. Thanks
    for the post. I will definitely return.

  23. Nice post. I learn something more difficult on different blogs everyday. It’s going to at all times be stimulating to read content from different writers and follow a bit of something from their store. I’d prefer to make use of some with the content on my blog whether or not you don’t mind. Natually I’ll provide you with a link in your web blog. Thanks for sharing.

  24. Can I just say what a reduction to find someone who truly knows what theyre talking about on the internet. You definitely know how you can carry a problem to gentle and make it important. More people must read this and perceive this aspect of the story. I cant consider youre no more standard since you positively have the gift.

  25. fantastic post, very informative. I wonder why the other specialists of this sector do not notice this. You must continue your writing. I’m sure, you’ve a great readers’ base already!

  26. obviously like youur web-site but you need to check the speling on quite
    a few oof your posts. Many oof them are rife with spelling
    issues and I inn finhding it very bothersome to
    inform the truth nevertheless I will surely come badk again.

  27. I just like the valuable information you provide to your articles.
    I will bookmasrk your weblog and test once more here frequently.
    I’m fairdly certain I’ll bee informed many new stuf proper here!
    Best of luck for the next!

Leave a Reply

Your email address will not be published. Required fields are marked *