Recyclable and Low Fat Products | LeetCode | MSSQL
Tags: mssql, database, leetcode, tutorial
Published: 2023-07-23 12:55:04

## The Problem

The given problem involves the 'Products' table, structured as follows:


| Column Name | Type    |
|-------------|---------|
| product_id  | int     |
| low_fats    | enum    |
| recyclable  | enum    |


Here, `product_id` is the primary key. `low_fats` and `recyclable` are ENUM types, both denoting whether the product is low fat ('Y' or 'N') and whether it is recyclable ('Y' or 'N'), respectively. The task is to find the ids of the products that are both low fat and recyclable.

## Explanation

For example, given the 'Products' table:


| product_id  | low_fats | recyclable |
|-------------|----------|------------|
| 0           | Y        | N          |
| 1           | Y        | Y          |
| 2           | N        | Y          |
| 3           | Y        | Y          |
| 4           | N        | N          |


The expected output should be:


| product_id  |
|-------------|
| 1           |
| 3           |


The output lists the `product_id` values for products that are both low fat (i.e., `low_fats = 'Y'`) and recyclable (i.e., `recyclable = 'Y'`).

## The Solution

We'll look at two different SQL queries and discuss their approaches, performance, and characteristics.

### Source Code 1

The first SQL query uses a straightforward `WHERE` clause to filter out products that are both low fat and recyclable:

```sql
SELECT product_id
FROM Products
WHERE low_fats = 'Y'
    AND recyclable = 'Y'
```

This query directly utilizes the equality operator for filtering the desired records. Its runtime is 929ms, which surpasses 51.43% of the other submissions on LeetCode.

![s1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lnfr2cp0vrqyrk3c2zpj.png)

### Source Code 2

The second SQL query differs in that it uses a `CASE` statement in the `WHERE` clause:

```sql
SELECT product_id
FROM Products
WHERE 
  CASE 
    WHEN low_fats = 'Y' AND recyclable = 'Y' THEN 1
    ELSE 0
  END = 1
```

In this approach, we're using a `CASE` statement to encapsulate the condition within a switch case structure. If the product is both low fat and recyclable, it returns 1; otherwise, 0. The `WHERE` clause filters out the records with a return value of 1. Its runtime is 1316ms, beating 13.20% of LeetCode submissions.

![s2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7kn0mbigexyzzxa3jalx.png)

## Conclusion

These two solutions offer two distinct approaches to the same problem: the first uses a simple boolean logic in the `WHERE` clause, while the second employs a more complex conditional (`CASE`) statement within the `WHERE` clause. 

While the first approach provides a more readable and faster solution, the second approach might come in handy when dealing with more complex scenarios. However, it is worth noting that the `CASE` statement introduces additional computation, which may explain the slower runtime. 

In terms of LeetCode performance, the solutions rank as follows:

1. Source Code 1
2. Source Code 2

Nonetheless, real-world RDBMS performance may vary based on factors such as indexing, data distribution, and database optimization techniques.

You can find the original problem at [LeetCode](https://leetcode.com/problems/recyclable-and-low-fat-products/description/).

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

{% cta https://beacons.ai/ranggakd %} 👉 Check out all the links on my beacons.ai page 👈 {% endcta %}