Swap Salary | LeetCode | MSSQL

The Problem

In this blog post, we are dealing with a common yet interesting SQL problem: swapping column values in an entire table using a single update statement.

Consider the Salary table with the following structure:

id is the primary key for this table, and sex is an ENUM value of type ('m', 'f'). Our goal is to write a SQL query that swaps all 'f' and 'm' values, i.e., changes all 'f' values to 'm' and vice versa, with a single update statement and no intermediate temporary tables.

For example, consider the following input:

The output would be:

The Solution

In this section, we will compare three different solutions for this problem. Each solution differs in its approach, and we will discuss their main differences, strengths, and weaknesses.

Solution 1: Using CASE Statement

The first solution uses the SQL CASE statement to determine whether to set the sex column to 'm' or 'f'.

UPDATE Salary
SET sex = CASE
        WHEN sex = 'f' THEN 'm'
        WHEN sex = 'm' THEN 'f'
    END

The CASE statement evaluates each condition (in the WHEN clause) from top to bottom and sets the sex value according to the first true condition. This solution is very straightforward and easy to read, but it requires evaluating two conditions, which may result in slightly slower performance. In a LeetCode test, this solution had a runtime of 482ms, beating 61.31% of all submissions.

Solution 2: Using IIF Statement

The second solution takes advantage of the SQL IIF statement, which is a shorthand way of writing a CASE expression. It takes three arguments: the boolean expression to evaluate, the value to return if the expression is true, and the value to return if the expression is false.

UPDATE Salary
SET sex = IIF(sex = 'f', 'm', 'f')

This solution is even more concise than the first one. However, the IIF function is specific to MSSQL and might not be supported in other SQL variants. This solution had a runtime of 480ms, slightly faster than the first one, and beat 61.95% of all submissions.

Solution 3: Using CASE Statement (Simplified)

The third solution also uses a CASE statement but in a different form. It directly compares the sex value with 'm' and, if true, changes it to 'f'; otherwise, it changes it to 'm'.

UPDATE Salary
SET sex = CASE sex
    WHEN 'm' THEN 'f'
    ELSE 'm'
END

This solution is as readable as the first one but slightly more efficient because it evaluates only one condition. However, it is slower in performance, with a runtime of 686ms, beating only 12.56% of all submissions.

Conclusion

All three solutions meet the problem requirements. Solution 2 stands out for its brevity and slightly better performance in LeetCode tests. However, its performance advantage is negligible, and the difference might not be noticeable in a real-world database environment.

The solutions' ranking based on the LeetCode test performance is as follows:

1. Solution 2 (IIF Statement)
2. Solution 1 (CASE Statement)
3. Solution 3 (Simplified CASE Statement)

Keep in mind that LeetCode is a controlled environment, and the performance of these queries might vary in different RDBMS and under different workloads.

You can find the original problem at LeetCode.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai