Problem
You have a table with a column where some rows contain NULL values or empty strings. You want to retrieve rows where this column has a value (i.e., not NULL and not an empty string).
Input
product_id | product_name | product_description |
---|---|---|
1 | Laptop | High performance gaming laptop |
2 | Mouse | |
3 | Keyboard | |
4 | Monitor | 27 inch 4K display |
Create Input Table: Gist
Desired Output
product_id | product_name | product_description |
---|---|---|
1 | Laptop | High performance gaming laptop |
4 | Monitor | 27 inch 4K display |
Solution 1:
Using WHERE
SELECT *
FROM Products
WHERE product_description IS NOT NULL AND product_description != '';
Explanation:
product_description
IS NOT NULL filters out the rows where the product_description is NULL.-
product_description
!= ” filters out the rows where the product_description is an empty string.
Combining these conditions with an AND operator ensures that you retrieve rows where product_description has a value.
Solution 2:
Using COALESCE
SELECT *
FROM Products
WHERE COALESCE(product_description, '') <> ''
DESC;
Explanation:
The COALESCE function returns the first non-NULL value in its list of arguments. By using COALESCE(product_description, ”), if product_description is NULL, it will be replaced by an empty string.
The condition <> ” then filters out both NULLs (which have been converted to empty strings by COALESCE) and actual empty strings
.
This method uses a single condition instead of two, which can be considered more concise in some scenario
s.