Swap Salary | LeetCode | MSSQL
Tags: beginners, codenewbie, leetcode, mssql
Published: 2023-06-02 15:32:26

## 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:

| Column Name | Type     |
|-------------|----------|
| id          | int      |
| name        | varchar  |
| sex         | ENUM     |
| salary      | int      |

`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:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |

The output would be:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

## 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'. 

```sql
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 1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8r0rtc2gdw3o6z42b4kb.png)

### 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.

```sql
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 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/697pg7ytr0cc8l046i93.png)

### 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'.

```sql
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.

![Solution 3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pl6lsrg9eddt8dz27a6g.png)

## 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](https://leetcode.com/problems/swap-salary/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}