Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL CASE Statement
The CASE statement in SQL is a powerful tool for implementing conditional logic directly within queries. It allows you to perform different actions based on different conditions and is often used to create calculated fields, perform conditional aggregations, and transform data based on certain criteria.
Purpose
- Conditional Logic: Provides a way to introduce conditional logic into SQL queries, enabling complex transformations and computations.
- Data Transformation: Useful for creating computed columns, performing conditional aggregations, or substituting values based on specific conditions.
Simple CASE Syntax:
SELECT column_name,
CASE column_name
WHEN value1 THEN result1
WHEN value2 THEN result2
…
ELSE default_result
END AS new_column_name
FROM table_name;
Searched CASE Syntax:
SELECT column_name,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
ELSE default_result
END AS new_column_name
FROM table_name;
Example:
1. Categorize Employee Salaries
Categorize employee salaries into different salary bands.
SELECT employee_id, first_name, salary,
CASE
WHEN salary < 30000 THEN ‘Low’
WHEN salary BETWEEN 30000 AND 60000 THEN ‘Medium’
ELSE ‘High’
END AS salary_band
FROM employees;
Output:
employee_id | first_name | salary | salary_band |
1 | Rajesh | 55000 | Medium |
2 | Priya | 25000 | Low |
3 | Ravi | 70000 | High |
2. Determine Discount Based on Order Amount
Apply a discount based on the order amount.
SELECT order_id, order_amount,
CASE
WHEN order_amount < 1000 THEN order_amount * 0.05
WHEN order_amount BETWEEN 1000 AND 5000 THEN order_amount * 0.10
ELSE order_amount * 0.15
END AS discount
FROM orders;
Output:
order_id | order_amount | discount |
101 | 500 | 25 |
102 | 2000 | 200 |
103 | 6000 | 900 |
3. Assign Status Based on Order Date
Assign a status based on the order date relative to the current date.
SELECT order_id, order_date,
CASE
WHEN order_date < DATEADD(day, -30, GETDATE()) THEN ‘Old’
WHEN order_date BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE() THEN ‘Recent’
ELSE ‘New’
END AS order_status
FROM orders;
Output:
order_id | order_date | order_status |
101 | 2024-07-15 | Old |
102 | 2024-08-10 | Recent |
103 | 2024-08-20 | New |
4. Categorize Students Based on Marks
Assign grades to students based on their marks.
SELECT student_id, student_name, marks,
CASE
WHEN marks >= 90 THEN ‘A’
WHEN marks >= 80 THEN ‘B’
WHEN marks >= 70 THEN ‘C’
ELSE ‘D’
END AS grade
FROM students;
Output:
student_id | student_name | marks | grade |
1 | Ananya | 92 | A |
2 | Arjun | 85 | B |
3 | Sneha | 72 | C |
Tips to Remember
- Order of Conditions: Conditions in the CASE statement are evaluated in the order they are listed. The first true condition’s result is returned.
- ELSE Clause: The ELSE clause is optional. If omitted, NULL is returned when none of the conditions are met.
- Performance Considerations: Complex CASE statements may impact query performance. Optimize conditions and use indexing where appropriate.
- Data Types: Ensure that all THEN and ELSE results are of the same data type or are implicitly convertible to the same type.
Power of CASE Statement
- Custom Calculations: Allows for custom calculations and transformations within a query.
- Data Categorization: Facilitates categorizing and summarizing data based on multiple conditions.
- Enhanced Reporting: Improves the clarity and usefulness of reports by enabling conditional data presentation.
Frequently Asked Questions
Q1: Can CASE statements be used in the ORDER BY clause?
A1: Yes, CASE statements can be used in the ORDER BY clause to sort results based on complex conditions.
Example:
SELECT employee_id, first_name, salary
FROM employees
ORDER BY
CASE
WHEN salary < 30000 THEN 1
WHEN salary BETWEEN 30000 AND 60000 THEN 2
ELSE 3
END;
Q2: Can I use CASE statements in the WHERE clause?
A2: Yes, CASE statements can be used in the WHERE clause to apply conditional logic for filtering.
Example:
SELECT employee_id, first_name, salary
FROM employees
WHERE
CASE
WHEN salary < 30000 THEN 1
ELSE 0
END = 1;
Q3: How does CASE handle NULL values?
A3: CASE treats NULL values in comparisons as unknown. Use IS NULL or IS NOT NULL in conditions to handle NULL values explicitly.
Example:
SELECT employee_id, first_name, salary,
CASE
WHEN salary IS NULL THEN ‘No Salary Info’
ELSE ‘Salary Available’
END AS salary_status
FROM employees;
Q4: Can I nest CASE statements?
A4: Yes, CASE statements can be nested within each other for more complex logic.
Example:
SELECT employee_id, first_name, salary,
CASE
WHEN salary < 30000 THEN
CASE
WHEN salary IS NULL THEN ‘No Salary Info’
ELSE ‘Low’
END
ELSE ‘Not Low’
END AS salary_band
FROM employees;
Q5: Can CASE be used in the HAVING clause?
A5: Yes, CASE can be used in the HAVING clause to filter results based on aggregated values.
Example:
SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id
HAVING
CASE
WHEN COUNT(employee_id) > 10 THEN 1
ELSE 0
END = 1;
