dbForge SQL Complete

The CASE Statement fundamentals

What is CASE in SQL?

SQL CASE statement evaluates a condition and returns a result that meets that condition. If none of the conditions is evaluated to TRUE it returns a value from the ELSE block. In simple words, the CASE expression is the way to build the IF - THEN logic into SQL.

A quick review of CASE rules

CASE must be followed by at least one WHEN... THEN expression
Every CASE statement must end with the END keyword
The ELSE argument is optional
CASE can be used in any statement or clause that allows a valid expression
Only 10 levels of nesting are allowed in SQL Server

Syntax example

The syntax for the SELECT statement with a simple CASE expression is as follows:

SELECT CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultEND

Enjoying CASE expression with

SQL Complete

Being a declarative language, SQL provides little to no control over how inputs are dealt with. CASE expressions add much-needed flexibility to SQL queries and are crucial to master and use correctly.

That's where SQL Complete hits the stage. Its advanced autocompletion capabilities allow writing the most complex CASE expressions quickly. In-built code snippets for CASE statements save your energy, highlighting pairs of matching keywords and quick navigation between them help minimize coding time as SQL CASE expressions can be long enough and combing through them is not an easy task.

SQL Complete also boasts a robust formatter that allows making code with CASE statements more readable and can help enforce common coding standards to a team or organization.

As part of our SQL CASE expression tutorial, we'll have a look at different MSSQL CASE statement examples with the help of the SQL Complete tool.

Enjoy even the most complex CASE expressions with SQL Complete

Download

SQL CASE statement types

There are two types of SQL CASE statements: a simple CASE statement and a searched CASE statement.

Simple CASE statements determine the result value evaluating an expression against multiple values.

Searched CASE statements determine the result value evaluating a set of Boolean expressions.

SQL CASE statement with multiple conditions

In case you need the result to satisfy multiple conditions, you can add those conditions to the CASE statement and combine them with the AND operator:

CASE expression WHEN condition1 AND condition2 THEN result1 ELSE result2 END

You can also define a number of result values in the CASE statement by including as many WHEN/THEN statements as you want:

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN condition3 THEN result3 ... ELSE resultNEND

Using CASE statements with the ORDER BY clause

CASE statements in the ORDER BY clause are used to change the output order and instruct the query to sort the results based on a certain requirement.

SELECT * FROM table_nameORDER BY CASE expression WHEN condition1 THEN result1, WHEN condition2 THEN result2 ... ELSE resultN END

Using CASE with the GROUP BY clause

To apply aggregate functions, you can use the CASE statement in conjunction with the GROUP BY clause.

Grouping with the CASE expression is a simple yet elegant method to arrange the query output in the required way.

SELECT CASE WHEN condition1 THEN result1 ELSE result2 END AS condition, COUNT(1) AS count FROM table_name GROUP BY CASE WHEN condition1 THEN result1 ELSE result2 END

Insert statement with CASE in SQL

You can use the CASE expression to insert data into a SQL Server table. The INSERT statement with CASE will scan for the required values and if found, insert values from THEN expression.

INSERT INTO column_nameVALUES (CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3 END);

UPDATE statement with SQL CASE

In SQL, you can update a table with the CASE statement.

The CASE expression used in the UPDATE statement allows selecting or setting a new value based on the input conditions.

UPDATE table_name SET column_value = CASE WHEN condition1 THEN result1, WHEN condition2 THEN result2 ELSE result3 END

Use CASE WHEN with SUM()
in SQL

The CASE WHEN expression is quite often used with the SUM() function for more complex reporting.

How does it work? The CASE expression assigns values according to the specified conditions and then the SUM function sums all those values.

SELECT COUNT(*) AS TotalCount, SUM(CASE WHEN condition1 THEN result1 ELSE result3 END) AS column_name1, SUM(CASE WHEN condition1 THEN result2 ELSE result3 END) AS column_name2FROM table_name

SQL CASE statement best practices

Multiple conditions in the CASE statements are processed in a sequential model. It stops checking conditions with the first successful condition.
It is better to use the ELSE block in CASE statements so that if none of the conditions is not satisfied, the default value will be returned.
CASE statement can not be used for checking NULL values in a table.
It is recommended to avoid conflicting conditions.

With SQL Complete, you GET

Code snippets for CASE expressions
Context-based prompts for table and column names
Navigation between the CASE and END keywords

Highlighting of matching keywords pairs
Quick info about database objects
Instant code formatter with built-in formatting profiles

SQL Complete

Advanced solution for SQL database development, management, and administration

Download for free

Try dbForge SQL Complete and double the speed of your SQL coding! Try now

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.

dbForge SQL Complete - Powerful T-SQL Formatting Tool (2024)

The CASE Statement fundamentals

Enjoying CASE expression with