WHEN Databasename = 'SQLUndercover' THEN 'Match Found'įROM (VALUES(NULL)) AS DerivedList (Databasename)įROM (VALUES('SQLUndercover')) AS DerivedList (Databasename)įunny thing is I stumbled upon this by accident when I was making some code changes the other day and when I tested the code I wondered why the NULL was not being evaluated correctly! Damn those aesthetics ? WHEN Databasename IS NULL THEN 'It''s a NULL' I put together some examples to illustrate the difference when evaluating Null using the two Case expressions, the query returns the column ‘Databasename’ from the derived list values clause, example 1 has a Null value and example 2 has a value of ‘SQLUndercover’ which you will see below: The searched CASE expression evaluates a set of Boolean expressions to determine the result.īoth formats support an optional ELSE argument. The simple CASE expression compares an expression to a set of simple expressions to determine the result. It looks so clean compared to the first example! but it wasn’t until I tested the second method out that I realised that the behaviour of the two CASE expressions are different as outlined on books online It wasn’t until recently that I noticed that there is another syntax that can be used with the CASE expression, the interesting part about this other form is that it is rather aesthetically pleasing when dealing with multiple WHEN clauses (or at least this is what I though at first ? 1 THEN 'Very High' Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at all posts by Rajendra GuptaFor some reason I have always written my Case statements using the following logic: Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 20 at SQLShack.
He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. Before we proceed, create a sample table and insert few records in it.Īs an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. Let us explore a few examples of the Case statement in SQL. In the following example, we want to get Product name for ProductID 4.it does not satisfy Case statement condition therefore, it gave output from Else expression. Similarly, if we change the condition in a Case statement in SQL, it returns appropriate expression. Once a condition is satisfied, its corresponding value is returned. In Case statement, we defined conditions. Look at the following example We declared a variable and specified value 1 for it. You want to get Productname for a particular ProductID. Suppose you have a table that stores the ProductID for all products in a mini-store.
In this article, we would explore the CASE statement and its various use cases.
It can be used in the Insert statement as well. We can use a Case statement in select queries along with Where, Order By, and Group By clause. The case statement in SQL returns a value on a specified condition.