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

Storing passwords as plain text is dangerous. Instead, you should hash them using a strong, slow hashing algorithm like BCrypt, which includes built-in salting and resistance to brute-force attacks.

Step 1: Install BCrypt NuGet Package

Before using BCrypt, install the BCrypt.Net-Next package:

dotnet add package BCrypt.Net-Next

or via NuGet Package Manager:

Install-Package BCrypt.Net-Next

Step 2: Hash a Password

Use BCrypt.HashPassword() to securely hash a password before storing it:

using BCrypt.Net;

string password = "mySecurePassword123";
string hashedPassword = BCrypt.HashPassword(password);

Console.WriteLine(hashedPassword); // Output: $2a$12$...

Step 3: Verify a Password

To check a user's login attempt, use BCrypt.Verify():

bool isMatch = BCrypt.Verify("mySecurePassword123", hashedPassword);
Console.WriteLine(isMatch); // Output: True

Ensuring proper hashing should be at the top of your list when it comes to building authentication systems.

2
230

Slow initial load times can drive users away from your React application. One powerful technique to improve performance is lazy loading - loading components only when they're needed.

Let's explore how to implement this in React.

The Problem with Eager Loading

By default, React bundles all your components together, forcing users to download everything upfront. This makes navigation much quicker and more streamlined once this initial download is complete.

However, depending on the size of your application, it could also create a long initial load time.

import HeavyComponent from './HeavyComponent';
import AnotherHeavyComponent from './AnotherHeavyComponent';

function App() {
  return (
    <div>
      {/* These components load even if user never sees them */}
      <HeavyComponent />
      <AnotherHeavyComponent />
    </div>
  );
}

React.lazy() to the Rescue

React.lazy() lets you defer loading components until they're actually needed:

import React, { lazy, Suspense } from 'react';

// Components are now loaded only when rendered
const HeavyComponent = lazy(() => import('./HeavyComponent'));
const AnotherHeavyComponent = lazy(() => import('./AnotherHeavyComponent'));

function App() {
  return (
    <div>
      <Suspense fallback={<div>Loading...</div>}>
        <HeavyComponent />
        <AnotherHeavyComponent />
      </Suspense>
    </div>
  );
}

Route-Based Lazy Loading

Combine with React Router for even better performance:

import React, { lazy, Suspense } from 'react';
import { BrowserRouter, Routes, Route } from 'react-router-dom';

const Home = lazy(() => import('./pages/Home'));
const Dashboard = lazy(() => import('./pages/Dashboard'));
const Settings = lazy(() => import('./pages/Settings'));

function App() {
  return (
    <BrowserRouter>
      <Suspense fallback={<div>Loading...</div>}>
        <Routes>
          <Route path="/" element={<Home />} />
          <Route path="/dashboard" element={<Dashboard />} />
          <Route path="/settings" element={<Settings />} />
        </Routes>
      </Suspense>
    </BrowserRouter>
  );
}

Implement these techniques in your React application today and watch your load times improve dramatically!

0
95

The null coalescing assignment operator (??=) introduced in C# 8.0 provides a cleaner way to assign a value to a variable only when it's null. Let's see how and when to use it effectively.

Quick Example

// Instead of writing this:
if (myVariable == null)
    myVariable = defaultValue;

// You can write this:
myVariable ??= defaultValue;

Real-World Examples

Simple Property Initialization

public class UserSettings
{
    private List<string> _preferences;
    
    public List<string> Preferences
    {
        get
        {
            _preferences ??= new List<string>();
            return _preferences;
        }
    }
}

Service Caching

public class ServiceCache
{
    private ApiClient _client;
    
    public ApiClient GetClient()
    {
        _client ??= new ApiClient("https://api.example.com");
        return _client;
    }
}

Lazy Configuration Loading

public class ConfigurationManager
{
    private Dictionary<string, string> _settings;
    
    public string GetSetting(string key)
    {
        _settings ??= LoadSettingsFromFile();
        return _settings.TryGetValue(key, out var value) ? value : null;
    }
    
    private Dictionary<string, string> LoadSettingsFromFile()
    {
        // Load settings logic here
        return new Dictionary<string, string>();
    }
}

Common Gotchas

Reference vs Value Types

The operator works differently with value types - they need to be nullable:

// This won't compile
int count ??= 1;

// This works
int? count ??= 1;

Chaining Operations

// You can chain the operator
string result = first ??= second ??= "default";

// Equivalent to:
if (first == null)
{
    if (second == null)
    {
        second = "default";
    }
    first = second;
}
result = first;

Thread Safety

The operator is not thread-safe by default:

// Not thread-safe
public class SharedCache
{
    private static Dictionary<string, object> _cache;
    
    public object GetItem(string key)
    {
        // Multiple threads could evaluate null simultaneously
        _cache ??= new Dictionary<string, object>();
        return _cache.GetValueOrDefault(key);
    }
}

// Thread-safe version
public class SharedCache
{
    private static Dictionary<string, object> _cache;
    private static readonly object _lock = new object();
    
    public object GetItem(string key)
    {
        lock (_lock)
        {
            _cache ??= new Dictionary<string, object>();
            return _cache.GetValueOrDefault(key);
        }
    }
}

Performance Considerations

The null coalescing assignment operator is compiled to efficient IL code. It generally performs the same as an explicit null check:

// These compile to similar IL
obj ??= new object();

if (obj == null)
    obj = new object();

When to Use It

✅ Good use cases:

  • Lazy initialization of properties
  • Caching values
  • Setting default values for nullable types
  • Simplifying null checks in property getters

❌ Avoid using when:

  • You need thread-safe initialization (use Lazy<T> instead)
  • The right-hand expression has side effects
  • You need more complex null-checking logic

Visual Studio Tips

You can use Quick Actions (Ctrl+.) to convert between traditional null checks and the ??= operator. Look for the suggestion "Use null coalescing assignment" when you have a pattern like:

if (variable == null)
    variable = value;

Version Compatibility

This feature requires:

  • C# 8.0 or later
  • .NET Core 3.0+ or .NET Standard 2.1+
  • Visual Studio 2019+
1
51