Recyclable and Low Fat Products | LeetCode | MSSQL

The Problem

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

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:

The expected output should be:

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:

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.

Source Code 2

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

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.

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.

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

👉 Check out all the links on my beacons.ai page 👈