How to return results only if value exists in SQL?

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

Create Input Table: Gist

Desired Output

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.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to concatenate multiple rows into one field in MySQL?
2. How to efficiently convert rows to columns in SQL?
3. How to transpose columns to rows in SQL?
4. How to select first row in each GROUP BY group?