-
-
Back to Main menu
Shopping Cart
Your cart is currently empty
Add product
-
-
Sign in
- Contact Sales
Contact Sales
Product finder
Achieve the next productivity level
Free to try
dbForge SQL Complete
An advanced IntelliSense-style code completion add-in for SSMS and VS
Download
Buy now
SQL Complete
DownloadBuy Now
Overview
Get Started
Features
Editions
Support
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.
dbForge SQL Complete
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.