{"id":1497,"date":"2019-03-03T22:36:32","date_gmt":"2019-03-03T15:36:32","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1497"},"modified":"2020-04-11T20:12:36","modified_gmt":"2020-04-11T13:12:36","slug":"sql-server-case","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-case\/","title":{"rendered":"SQL Server CASE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server CASE<\/code> expression to add if-else logic to SQL queries.<\/p>\n\n\n\n

SQL Server CASE<\/code> expression evaluates a list of conditions and returns one of the multiple specified results. The CASE<\/code> expression has two formats: simple CASE<\/code> expression and searched CASE<\/code> expression. Both of CASE<\/code> expression formats support an optional ELSE<\/code> statement.<\/p>\n\n\n\n

Because CASE is an expression, you can use it in any clause that accepts an expression such as SELECT<\/a><\/code>, WHERE<\/a><\/code>, GROUP BY<\/a><\/code>, and HAVING<\/a><\/code>.<\/p>\n\n\n\n

SQL Server simple CASE expression #<\/a><\/h2>\n\n\n\n

The following shows the syntax of the simple CASE<\/code> expression:<\/p>\n\n\n

CASE input   \n    WHEN e1 THEN r1\n    WHEN e2 THEN r2\n    ...\n    WHEN en THEN rn\n    [ ELSE re ]   \nEND<\/span>  \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The simple CASE<\/code> expression compares the input expression (input<\/code>) to an expression (ei<\/code>) in each WHEN<\/code> clause for equality. If the input expression equals an expression (ei<\/code>) in the WHEN<\/code> clause, the result (ri<\/code>) in the corresponding THEN<\/code> clause is returned.<\/p>\n\n\n\n
If the input expression does not equal to any expression and the ELSE<\/code> clause is available, the CASE<\/code> expression will return the result in the ELSE<\/code> clause (re<\/code>).<\/p>\n\n\n\n
In case the ELSE<\/code> clause is omitted and the input expression does not equal to any expression in the WHEN<\/code> clause, the CASE<\/code> expression will return NULL.<\/p>\n\n\n\n
A) Using simple CASE expression in the SELECT clause example #<\/a><\/h3>\n\n\n\n
See the following sales.orders<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
This example uses the COUNT()<\/a><\/code> function with the GROUP BY<\/a><\/code> clause to return the number orders for each order’s status:<\/p>\n\n\n
SELECT<\/span>    \n    order_status, \n    COUNT<\/span>(order_id) order_count\nFROM<\/span>    \n    sales.orders\nWHERE<\/span> \n    YEAR<\/span>(order_date) = 2018<\/span>\nGROUP<\/span> BY<\/span> \n    order_status;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The values in the order_status<\/code> column are numbers, which is not meaningful in this case. To make the output more understandable, you can use the simple CASE<\/code> expression as shown in the following query:<\/p>\n\n\n
SELECT<\/span>    \n    CASE<\/span> order_status\n        WHEN<\/span> 1<\/span> THEN<\/span> 'Pending'<\/span>\n        WHEN<\/span> 2<\/span> THEN<\/span> 'Processing'<\/span>\n        WHEN<\/span> 3<\/span> THEN<\/span> 'Rejected'<\/span>\n        WHEN<\/span> 4<\/span> THEN<\/span> 'Completed'<\/span>\n    END<\/span> AS<\/span> order_status, \n    COUNT<\/span>(order_id) order_count\nFROM<\/span>    \n    sales.orders\nWHERE<\/span> \n    YEAR<\/span>(order_date) = 2018<\/span>\nGROUP<\/span> BY<\/span> \n    order_status;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
B) Using simple CASE expression in aggregate function example #<\/a><\/h3>\n\n\n\n
See the following query:<\/p>\n\n\n
SELECT<\/span>    \n    SUM<\/span>(CASE<\/span>\n            WHEN<\/span> order_status = 1<\/span>\n            THEN<\/span> 1<\/span>\n            ELSE<\/span> 0<\/span>\n        END<\/span>) AS<\/span> 'Pending'<\/span>, \n    SUM<\/span>(CASE<\/span>\n            WHEN<\/span> order_status = 2<\/span>\n            THEN<\/span> 1<\/span>\n            ELSE<\/span> 0<\/span>\n        END<\/span>) AS<\/span> 'Processing'<\/span>, \n    SUM<\/span>(CASE<\/span>\n            WHEN<\/span> order_status = 3<\/span>\n            THEN<\/span> 1<\/span>\n            ELSE<\/span> 0<\/span>\n        END<\/span>) AS<\/span> 'Rejected'<\/span>, \n    SUM<\/span>(CASE<\/span>\n            WHEN<\/span> order_status = 4<\/span>\n            THEN<\/span> 1<\/span>\n            ELSE<\/span> 0<\/span>\n        END<\/span>) AS<\/span> 'Completed'<\/span>, \n    COUNT<\/span>(*) AS<\/span> Total\nFROM<\/span>    \n    sales.orders\nWHERE<\/span> \n    YEAR<\/span>(order_date) = 2018<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n