How Can I Exclude NULL Values from MySQL SELECT Statements?

Filtering Out NULLs in MySQL SELECT Queries

Question: How can I retrieve only non-null values using MySQL SELECT statements?

Standard MySQL SELECT * statements include all columns, even those with NULL values. To retrieve only non-null data, use the IS NOT NULL condition:

<code class="language-sql">SELECT * 
FROM your_table
WHERE YourColumn IS NOT NULL;</code>

This query returns rows where YourColumn doesn't contain NULL. MySQL also supports the negation of the null-safe equality operator, although this isn't standard SQL:

<code class="language-sql">SELECT *
FROM your_table
WHERE NOT (YourColumn <=> NULL);</code>

Handling multiple columns requires a different strategy. One method uses multiple SELECT statements and UNION ALL:

<code class="language-sql">SELECT val1 AS val
FROM your_table
WHERE val1 IS NOT NULL
UNION ALL
SELECT val2
FROM your_table
WHERE val2 IS NOT NULL;</code>

This approach individually checks each column and combines the results. However, it can be inefficient due to multiple table scans.

To potentially avoid multiple scans, consider a CASE statement with a cross join:

<code class="language-sql">SELECT CASE idx
         WHEN 1 THEN val1
         WHEN 2 THEN val2
       END AS val
FROM your_table
CROSS JOIN (SELECT 1 AS idx UNION ALL SELECT 2) t
HAVING val IS NOT NULL;</code>

This selects the first non-null value from val1 and val2 for each row and assigns it to the val column.

The above is the detailed content of How Can I Exclude NULL Values from MySQL SELECT Statements?. For more information, please follow other related articles on the PHP Chinese website!