CASE in SELECT for computed columns in SQL - Time & Space Complexity

Time Complexity: CASE in SELECT for computed columns

O(n)

Understanding Time Complexity

We want to understand how the time to run a SQL query changes when using CASE statements in SELECT for computed columns.

Specifically, how does adding conditional logic affect the work the database does?

Scenario Under Consideration

Analyze the time complexity of the following SQL query.

SELECT
  employee_id,
  salary,
  CASE
    WHEN salary > 70000 THEN 'High'
    WHEN salary > 40000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_level
FROM employees;
    

This query classifies each employee's salary into categories using CASE in the SELECT clause.

Identify Repeating Operations

Look for repeated work done by the query.

• Primary operation: The database reads each row from the employees table.
• How many times: Once per row, it evaluates the CASE conditions to assign a category.

How Execution Grows With Input

As the number of employees grows, the database must check each row's salary once.

Input Size (n)	Approx. Operations
10	10 salary checks
100	100 salary checks
1000	1000 salary checks

Pattern observation: The work grows directly with the number of rows; doubling rows doubles the checks.

Final Time Complexity

Time Complexity: O(n)

This means the query time grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "Using CASE makes the query much slower because it adds complex logic."

[OK] Correct: The CASE statement just checks conditions once per row, so it adds a small, fixed amount of work per row, not extra loops or scans.

Interview Connect

Understanding how conditional logic affects query time helps you explain your choices clearly and shows you know how databases handle row-by-row operations.

Self-Check

What if we added a JOIN to another table inside the query? How would the time complexity change?

Practice

1. What does the CASE statement do in a SQL SELECT query?

easy

A. Creates a new column with values based on conditions

B. Deletes rows that do not meet a condition

C. Joins two tables together

D. Sorts the results in ascending order

Solution

1. Step 1: Understand the purpose of CASE in SELECT

   The CASE statement is used to create new columns by checking conditions and assigning values accordingly.

2. Step 2: Compare with other SQL operations

   Deleting rows is done with DELETE, joining tables with JOIN, and sorting with ORDER BY, not CASE.

3. Final Answer:

   Creates a new column with values based on conditions -> Option A

4. Quick Check:

   CASE = computed column based on conditions [OK]

Hint: CASE creates conditional columns in SELECT queries [OK]

Common Mistakes:

• Thinking CASE deletes rows
• Confusing CASE with JOIN
• Assuming CASE sorts data

2. Which of the following is the correct syntax to use CASE in a SELECT statement?

easy

A. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END result FROM students

B. SELECT CASE score > 50 THEN 'Pass' ELSE 'Fail' END AS result FROM students;

C. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' AS result FROM students;

D. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END AS result FROM students;

Solution

1. Step 1: Review correct CASE syntax

   The correct syntax requires WHEN conditions, THEN results, ELSE for default, and END to close, with AS to name the column.

2. Step 2: Identify errors in other options

   A omits AS before result (END result); B misses WHEN after CASE; C misses END before AS result.

3. Final Answer:

   SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END AS result FROM students; -> Option D

4. Quick Check:

   CASE syntax = WHEN...THEN...ELSE...END AS alias [OK]

Hint: Always use WHEN, THEN, ELSE, END, and AS alias [OK]

Common Mistakes:

• Omitting WHEN keyword
• Forgetting END keyword
• Not using AS for alias

3. Given the table employees with column salary, what will this query return?

SELECT name, CASE WHEN salary >= 5000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;

medium

A. An error because CASE cannot be used in SELECT

B. Only employees with salary ≥ 5000

C. A list of employee names with salary_level showing 'High' if salary ≥ 5000, else 'Low'

D. A list of employee names with salary values doubled

Solution

1. Step 1: Understand the CASE condition

   The CASE checks if salary is 5000 or more, then assigns 'High', otherwise 'Low'.

2. Step 2: Analyze the SELECT output

   The query returns all employees with their name and a new column salary_level showing 'High' or 'Low' accordingly.

3. Final Answer:

   A list of employee names with salary_level showing 'High' if salary ≥ 5000, else 'Low' -> Option C

4. Quick Check:

   CASE creates conditional labels per row [OK]

Hint: CASE labels rows based on condition, does not filter [OK]

Common Mistakes:

• Thinking CASE filters rows
• Expecting numeric salary changes
• Assuming syntax error

4. Identify the error in this SQL query:

SELECT name, CASE salary > 10000 THEN 'Rich' ELSE 'Average' END AS status FROM users;

medium

A. Missing WHEN keyword after CASE

B. Missing END keyword

C. Missing ELSE keyword

D. Missing AS keyword for alias

Solution

1. Step 1: Check CASE syntax

   The CASE statement must have WHEN before the condition. Here, WHEN is missing.

2. Step 2: Verify other parts

   END and ELSE are present, AS is used correctly, so no errors there.

3. Final Answer:

   Missing WHEN keyword after CASE -> Option A

4. Quick Check:

   CASE requires WHEN before condition [OK]

Hint: Always write WHEN after CASE before condition [OK]

Common Mistakes:

• Omitting WHEN keyword
• Confusing ELSE with END
• Forgetting AS alias

5. You want to classify products by price into 'Cheap', 'Moderate', and 'Expensive' using CASE in SELECT. Which query correctly implements this?

hard

A. SELECT product_name, CASE price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END price_category FROM products;

B. SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products;

C. SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' ELSE WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products;

D. SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price > 100 THEN 'Expensive' ELSE 'Expensive' END AS price_category FROM products;

Solution

1. Step 1: Check correct CASE syntax with multiple WHEN

   SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products; correctly uses multiple WHEN conditions with THEN, ELSE, and END, and aliases the column.

2. Step 2: Analyze logic correctness

   SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products; classifies prices below 50 as 'Cheap', between 50 and 100 as 'Moderate', else 'Expensive', which matches the requirement.

3. Final Answer:

   SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products; -> Option B

4. Quick Check:

   Multiple WHEN conditions with ELSE classify ranges [OK]

Hint: Use multiple WHEN for ranges, ELSE for default [OK]

Common Mistakes:

• Omitting WHEN keywords
• Misplacing ELSE before WHEN
• Not using AS for alias