How to Use the CASE Statement in SQL for Conditional Logic

The CASE statement in SQL allows you to implement conditional logic within queries, making it a powerful tool for handling complex data transformations and classifications.

Basic Syntax

The CASE statement works like an IF-ELSE structure, evaluating conditions and returning corresponding values:

SELECT column_name,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE default_result
       END AS alias_name
FROM table_name;

Example: Categorizing Data

Assume we have an Employees table with an Age column, and we want to categorize employees based on their age groups:

SELECT Name, Age,
       CASE
           WHEN Age < 25 THEN 'Young'
           WHEN Age BETWEEN 25 AND 40 THEN 'Mid-Age'
           ELSE 'Senior'
       END AS AgeCategory
FROM Employees;

Output Example:

Name Age AgeCategory
Alice 22 Young
Bob 30 Mid-Age
Charlie 50 Senior

Using CASE in Aggregations

CASE is often used in aggregate functions to perform conditional counting or summing:

SELECT
    SUM(CASE WHEN Age < 25 THEN 1 ELSE 0 END) AS YoungCount,
    SUM(CASE WHEN Age BETWEEN 25 AND 40 THEN 1 ELSE 0 END) AS MidAgeCount,
    SUM(CASE WHEN Age > 40 THEN 1 ELSE 0 END) AS SeniorCount
FROM Employees;

Using CASE in ORDER BY

You can use CASE to customize sorting order dynamically:

SELECT Name, Age
FROM Employees
ORDER BY
    CASE
        WHEN Age < 25 THEN 1
        WHEN Age BETWEEN 25 AND 40 THEN 2
        ELSE 3
    END;

Conclusion

The CASE statement is a versatile tool in SQL for implementing conditional logic in SELECT, WHERE, ORDER BY, and aggregate functions. It enhances query flexibility, making data classification and transformation more efficient.

0
33

Related

In C#, you can format an integer with commas (thousands separator) using ToString with a format specifier.

int number = 1234567;
string formattedNumber = number.ToString("N0"); // "1,234,567"
Console.WriteLine(formattedNumber);

Explanation:

"N0": The "N" format specifier stands for Number, and "0" means no decimal places. The output depends on the culture settings, so in regions where , is the decimal separator, you might get 1.234.567.

Alternative:

You can also specify culture explicitly if you need a specific format:

using System.Globalization;

int number = 1234567;
string formattedNumber = number.ToString("N0", CultureInfo.InvariantCulture);
Console.WriteLine(formattedNumber); // "1,234,567"
3
388

Reading a file line by line is useful when handling large files without loading everything into memory at once.

✅ Best Practice: Use File.ReadLines() which is more memory efficient.

Example

foreach (string line in File.ReadLines("file.txt"))
{
    Console.WriteLine(line);
}

Why use ReadLines()?

Reads one line at a time, reducing overall memory usage. Ideal for large files (e.g., logs, CSVs).

Alternative: Use StreamReader (More Control)

For scenarios where you need custom processing while reading the contents of the file:

using (StreamReader reader = new StreamReader("file.txt"))
{
    string? line;
    while ((line = reader.ReadLine()) != null)
    {
        Console.WriteLine(line);
    }
}

Why use StreamReader?

Lets you handle exceptions, encoding, and buffering. Supports custom processing (e.g., search for a keyword while reading).

When to Use ReadAllLines()? If you need all lines at once, use:

string[] lines = File.ReadAllLines("file.txt");

Caution: Loads the entire file into memory—avoid for large files!

3
283

String interpolation, introduced in C# 6.0, provides a more readable and concise way to format strings compared to traditional concatenation (+) or string.Format(). Instead of manually inserting variables or placeholders, you can use the $ symbol before a string to directly embed expressions inside brackets.

string name = "Walt";
string job = 'Software Engineer';

string message = $"Hello, my name is {name} and I am a {job}";
Console.WriteLine(message);

This would produce the final output of:

Hello, my name is Walt and I am a Software Engineer

String interpolation can also be chained together into a multiline string (@) for even cleaner more concise results:

string name = "Walt";
string html = $@"
    <div>
        <h1>Welcome, {name}!</h1>
    </div>";
36
142