What is Case statement in SQL and Is it same as IF/ELSE statement?

Yes, it kind of is same as IF/ELSE statement but instead of writing IF/ELSE, we use WHEN, THEN and ELSE. I know it sounds different but trust me it pretty much has the same function and it all comes under the SQL CASE Statement.

What is Case?

CASE returns records with your conditions by allowing you to include if/then statements in your query.

In simple words what this statement does is, it goes through conditions i.e. cases from top to bottom and which ever condition first met, it'll return the value.

It is just like the if-then-else statement. Now important thing to keep in mind is that, if there is no ELSE block and all the conditions are false, it will return a NULL value.

This expression can be used in statements like SELECT, DELETE and UPDATE or in clauses like SELECT, ORDER BY, and HAVING.

This gives a lot power in your hand because now you can control the execution / deletion of different sets of statements which meets the requirement.

SYNTAX:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Now let's take a closer look at this with the help of an example.

EXAMPLE:

Note: I'm using Google BigQuery.

Here I've a sample Inventory data and the schema is as follows:

Here is the preview of the data which is inside our table :

We are going to write a small query which will return the StoreName, neighborhood and QuantityAvailable from our Inventory table.

• To keep it simple, I'm using Case statement with SELECT and to remove any further confusion I'm writing first a Syntax structure of my query so people who are less familiar with SQL can understand :

SELECT
columnName1, columnName2, columnName3,
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result
END
FROM
`tableName`;

Now begins the real Query :

SELECT
StoreName, neighborhood, QuantityAvailable,
CASE
 WHEN QuantityAvailable >= 10 THEN "The Quantity is above 10"
 WHEN QuantityAvailable < 10 THEN "The Quantity is less 10"
 ELSE "Out of Stock"
END 
FROM
`forward-glass-347505.Sales.inventory`;

Here are the result of our query:

This query shows Store names and the neighborhood in which they are in and how much Stock they have of some imaginary stuff.

In the first condition: if the quantity is above or equal to 10, it will print out "The Quantity is above 10" and in the second case, if the quantity is less than 10 it will print out "The quantity is less than 10".

If the above condition do not match i.e. if the quantity is zero or there is no data available, the else part of the query will execute and the result would be "Out of Stock".

Now what if we change the conditions a little, like :

SELECT
StoreName, neighborhood, QuantityAvailable,
CASE
  WHEN QuantityAvailable < 0 THEN "The Quantity is above 10"
  WHEN QuantityAvailable >20 THEN "The Quantity is less 10"
  ELSE "Out of Stock"
END 
FROM
`forward-glass-347505.Sales.inventory`;

The output would be "Out of Stock" because all the conditions are false thus Query printed the ELSE part like this :

Let's remove the ELSE part and see what would happen:

SELECT
StoreName, neighborhood, QuantityAvailable,
CASE
  WHEN QuantityAvailable < 0 THEN "The Quantity is above 10"
  WHEN QuantityAvailable >20 THEN "The Quantity is less 10"
END 
FROM
`forward-glass-347505.Sales.inventory`;

Here's the output:

As I mentioned above: if there is no ELSE part and the above conditions are false, the query will return null values.

That's pretty much it about the CASE statement. If you're more curious, you can also check out SQL IF statement or wait for my next article. Have any query? Comment it down.