Grab Deal : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

- SQL Server Blogs -

What is a CASE Statement in the SQL?



Introduction

The control  statement is the heart of the most programming languages as they control the execution of a set of statements. These control statements are available in the SQL as well and generally exploited for query filtration and query optimization through careful selection of tuples that match your requirements. In this blog post, we will explore the SQL CASE Statement and why it is needed by the SQL programmers.

Get yourself dive into deep knowledge of sql server case statement!

What is the SQL Case Statement?

The Case Statement in SQL is the way of handling if/then logic. It evaluates a set of conditions and returns one of the possible result expressions. The Case Statement in SQL can be used in two possible formats as given below:

  • A simple CASE statement compares an expression with a set of simple expressions  and calculates the final output.
  • A searched Case statement evaluates a set of Boolean expressions to calculate the final output.

In both formats, you can use an optional Else statement. The Case statement can be used with any clause or statement that allows a valid expression. For example, the CASE statement can be used in statements such as Update, Select, Insert, Delete, Set, etc. the popular clauses where Case statement is used frequently like Order By, Having, Where, IN, select_list, etc. The simple syntax for a Case Statement in SQL can be written as below.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

-- Syntax for SQL Server and Azure SQL Database  
  
Simple CASE expression:   
CASE input_expression   
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END   
Searched CASE expression:  
CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
CASE  
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

SQL Case Statement

  • Input_expression: It is the expression evaluated when simple Case statement is used. The input_expression here can be any valid expression.
  • WHEN when_expression: It is a simple expression to which input_expression is compared. The when_expression can be any valid expression here. The data type for input expression and when expression should be the same otherwise SQL will show the error.
  • THEN result_expression: It is the expression returns when input_expression and when_expression both are evaluated true. The result_expression is any valid expression here. It evaluates the Boolean-expression too.
  • ELSE else_result_expression: It is the expression returns when input_expression and when_expression both are evaluated false. The else_result_expression is any valid expression here. It evaluates the Boolean-expression too. Keep in mind that data type for result_expression and else_result_expression should be the same or it should be an implicit conversion.
  • WHEN Boolean_expression: It is the Boolean expression evaluated for the searched CASE statement. Boolean_expression is any valid expression here.

Read: SQL Server Developer & Database Administrator Salary Structure

Return values for Simple CASE Expression

The simple CASE statement operates by comparing the first expression by When clause for the equivalency. If both expressions are equal, the value in the THEN clause is returned. Here are the following conditions for simple case expressions.

  • It allows only on equality check
  • For each WHEN clause, it evaluates input_expression and when_expression in the given order.
  • It returns the result expression if input expression and the when expression both are evaluated True.
  • If the input and when expressions are evaluated false, the SQL database return the else_result_expression. In case Else statement is not specified then it may return a NULL value.

Returns Value for Searched CASE expression

  • It evaluates the Boolean_expression for each “When” clause in the given order.
  • It returns the result_expression if Boolean_expression is evaluated TRUE.
  • If Boolean-expression is not evaluated TRUE then it returns the else_result_expression. In case Else statement is not specified then it may return a NULL value.

Read: Learn SQL Union All Query Operators with Examples

Things to Consider for CASE statement in SQL

  • The CASE statement should always be included within the Select clause.
  • The CASE statement should include the WHEN, THEN, and END clause. At the same time, ELSE is always an optional statement with a CASE statement.
  • You can use conditional statements within When and Then clause. It includes stringing together multiple conditional statements using “AND”, “OR” etc.
  • The multiple WHEN statements can be nested together with ELSE statements to deal with tough conditions.
  • SQL server allows a maximum of 10 levels of nesting for the CASE expressions.
  • To control the execution flow of T-SQL statements, we cannot use CASE expressions.
  • For a CASE expression, conditions are always executed in a sequence. If the first condition is satisfied then it does not execute the rest of the statements. It is possible to face errors in these types of expressions. If there is some aggregate function in WHEN arguments then it is evaluated first before executing the CASE expression.

Read: What Is The Difference Between Having And Where Clause In SQL?

Using CASE with other Functions or Statement

Using CASE with other Functions or Statement

1). Aggregate functions

The CASE statement is slightly more complicated and substantially more useful functionality that comes from pairing it with aggregate functions. For example, you want to count rows that satisfy a certain condition, you must use the CASE statement here to calculate the output and it will give NULL and non-null based on the condition. If you wanted to calculate rows on the basis of multiple conditions then use the “Group by” clause, in that case, to make the query simple as shown below.

SELECT CASE WHEN year = 'FR' THEN 'FR'
            ELSE 'Not FR' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
 GROUP BY CASE WHEN year = 'FR' THEN 'FR'
               ELSE 'Not FR' END

If you will not use aggregate functions here then the query will be lengthy and more prone to errors. Using a Case statement with the aggregate function may be complicated at first glance but little practice will make you an expert in using the CASE statement. If you still struggle in writing a CASE statement then practice problems online or join some online training program to become an SQL pro.

Read: What Is Average Salary Of Database Admin In Philippine?

2). Data representation

It is possible to display data either horizontally or vertically. in the above example, data is represented vertically. When you wanted to represent data horizontally using the CASE statement, it is called the pivoting or a pivot table in Excel. Rearranging data from horizontal to vertical orientation or vice versa can be quite difficult. So, it is necessary to learn the concept in-depth and practice as many problems as possible.here is a quick example, how to display data horizontally using the CASE statement in SQL.

Read: SQL Server Views - Everything You Should Know

SELECT COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count,
       COUNT(CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count,
       COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) AS jr_count,
       COUNT(CASE WHEN year = 'SR' THEN 1 ELSE NULL END) AS sr_count
  FROM benn.college_football_players

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

3). Order By Clause

The next common clause that is used frequently with the CASE statement is Order by clause. It is used to arrange rows in a specific order as required. For example, if there is one salary column where you want to arrange the employee column on the basis of their salary from top to bottom then you should simply use Order By clause here. It is a common clause for the SQL database, so necessary to practice by SQL developers to manage huge data files within a database. The career path of a SQL Server DBA

SELECT BusinessEntityID, SalariedFlag  
FROM HumanResources.Employee  
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC  
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;  
GO
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL  
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName  
         ELSE CountryRegionName END;

Let’s get started with SQL Server online training

Read: Windows SQL Function-All you Need to Know

4). Update Statement

Further, the CASE statement can also be used with the Update statement. For example, if an employee takes 10 days’ leaves, his salary would be deducted from the accordingly and updated in the database. In the same way, we can club multiple expressions, statements, and clauses in SQL according to requirements . The only thing is that you should know how to use them perfectly.

USE AdventureWorks2012;  
GO  
UPDATE HumanResources.Employee  
SET VacationHours =   
    ( CASE  
         WHEN ((VacationHours - 10.00) 

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Final Words

Moreover, when we learn SQL statements, theoretical knowledge is not much help but you should have practical experience in managing databases. If you start as a beginner then join some training program first to learn the basics. Once you are completed with SQL basics, you can move to the advanced stage too. After completion of training, attempt certification exam and get certified. Salary Structure of a SQL Server Developer and Admin

Based on research, certified SQL developers usually get jobs quickly with attractive salary packages. They are given more preference over non-certified candidates because of their practical knowledge and hands-on experience on related tools. SQL is one of the popular evolving IT fields with massive career opportunities and salary options. If you are also planning to start a career in SQL then join Oracle certification course at JanBask Training and take your career to new heights.

Read: How to Prevent SQL Injection Attacks?

SQL Tutorial Overview

fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

3 days 22 Mar 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

2 days 21 Mar 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

3 days 22 Mar 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

3 days 22 Mar 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

3 days 22 Mar 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

10 days 29 Mar 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

4 days 23 Mar 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

10 days 29 Mar 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

4 days 23 Mar 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

18 days 06 Apr 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

31 days 19 Apr 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

10 days 29 Mar 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews