Mysql SELECT only not null values

Database normalization

By optimizing your overall database structure you can often cut down on the need to filter out NULL values. Correctly normalizing your database ensures fields that shouldn't contain NULLs are set with the appropriate NOT NULL constraints, making your future queries more efficient.

Null-safe equality operator

When treating NULL as a distinct key value, leverage the null-safe equality operator <=>:

SELECT * FROM your_table WHERE your_column <=> 'value'; -- Even NULL says "I'm unique!"


This provides a comparison that respects NULL.

Multicolored non-nulls

When needing to select non-null values from multiple columns, think UNION ALL and subqueries:

SELECT column1 AS result FROM your_table WHERE column1 IS NOT NULL
UNION ALL
SELECT column2 FROM your_table WHERE column2 IS NOT NULL;
-- I love to unionize!


Do keep in mind that UNION ALL can result in multiple table scans, which might be of concern for larger datasets.

Leveraging aliases with HAVING

The HAVING clause can prove itself useful, particularly with aliases:

SELECT your_column, COUNT(*) AS total FROM your_table GROUP BY your_column HAVING your_column IS NOT NULL;
-- I am not aliasing around!


Utilizing cross joins

For more convoluted scenarios, a cross join could prove beneficial when you want to scour non-null values across multiple columns in a single output row:

SELECT t1.column1, t2.column2 FROM 
    (SELECT column1 FROM your_table WHERE column1 IS NOT NULL) t1
CROSS JOIN 
    (SELECT column2 FROM your_table WHERE column2 IS NOT NULL) t2;
-- Nulls? We don't need no stinkin' nulls!


Interaction of comparison operators with NULL

It is essential to understand that comparison operators such as = and <> react differently with NULL. 'value' <> NULL does not equal to true or false but to UNKNOWN, often resulting in unmatched records when you might expect it to.

Fetching the latest non-null values

In scenarios where you're interested in the most recent non-null value, combine an ORDER BY clause with DESC and LIMIT:

SELECT * FROM your_table WHERE your_column IS NOT NULL ORDER BY ID DESC LIMIT 1;
-- Newest non-null kid on the block!


This will get you the newest non-null entry.

Dealing with default values

Stay vigilant about columns with default values. They sometimes require varied handling:

SELECT * FROM your_table WHERE your_column <> '' AND your_column IS NOT NULL;
-- '' and NULL are not the same!


Here we evade empty strings that might be setup as defaults over NULL.

Use of DISTINCT

Easily extract unique non-null elements using SELECT DISTINCT:

SELECT DISTINCT your_column FROM your_table WHERE your_column IS NOT NULL;
-- Uniquely yours, non-null values!


This approach will yield unique entries without null values.

Database structure assessment

If you find yourself filtering out NULL values very often, it's worth reconsidering the design of your database. Regular need for avoiding nulls might hint the need for an overhaul aid in schema normalization to address the source problem.