How to return results only if value exists in SQL?
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).
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_descriptionIS 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.