MySQL/MySQLi带UNION的SELECT：如何排除{$column_7}NULL值并排序？

Hey there! Let's tackle your UNION query issues step by step—first getting rid of those unwanted NULL records, then sorting the combined results correctly.

Why Your NULL Filter Might Not Be Working #

The most common pitfall with UNION queries and NULL filtering is not applying the check consistently across all subqueries, or misusing JOIN types. Here's what might be happening:

1. You used LEFT JOIN without explicit filtering: A LEFT JOIN keeps all records from your main table, even if the associated table's {$column_7} is NULL. If you want to exclude any main table records where the linked {$column_7} is NULL, you either need to switch to INNER JOIN (which only returns matching records where both tables have data) or add a WHERE clause to each subquery to exclude NULLs.
2. You added the filter only to one subquery: UNION combines results from all subqueries—if you only filter NULLs in one part, the other subquery will still include them.
3. You tried filtering after the UNION: While this can work, it's less efficient and can fail if your subqueries have inconsistent column names/aliases. It's better to filter early in each subquery.

Correct Examples to Exclude NULLs #

Option 1: Use INNER JOIN (Simplest for Excluding Unmatched Records)

This automatically excludes any main table records that don't have a matching, non-NULL {$column_7} in the associated table:

SELECT a.*, b.{$column_7}
FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id
WHERE b.{$column_7} IS NOT NULL -- Extra safety in case the join matches but the field is NULL

UNION ALL -- Use UNION ALL instead of UNION if you don't need duplicate removal (faster!)

SELECT c.*, d.{$column_7}
FROM table_c c
INNER JOIN table_d d ON c.id = d.c_id
WHERE d.{$column_7} IS NOT NULL

Option 2: Keep LEFT JOIN but Filter NULLs Explicitly

If you need to retain some main table records but only those where {$column_7} is not NULL, add the WHERE clause to each subquery:

SELECT a.*, b.{$column_7}
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
WHERE b.{$column_7} IS NOT NULL -- This filters out any rows where b.{$column_7} is NULL

UNION

SELECT c.*, d.{$column_7}
FROM table_c c
LEFT JOIN table_d d ON c.id = d.c_id
WHERE d.{$column_7} IS NOT NULL

How to Sort UNION Results Correctly #

Sorting a UNION query has one golden rule: never add ORDER BY to individual subqueries (unless you're using a subquery to limit results first). Instead, add a single ORDER BY clause at the very end of the entire UNION statement. This sorts the combined result set uniformly.

Example of Proper Sorting #

Give your sort column a consistent alias across subqueries to avoid confusion:

(SELECT a.id, a.title, b.{$column_7} AS priority
 FROM table_a a
 INNER JOIN table_b b ON a.id = b.a_id
 WHERE b.{$column_7} IS NOT NULL)

UNION ALL

(SELECT c.id, c.title, d.{$column_7} AS priority
 FROM table_c c
 INNER JOIN table_d d ON c.id = d.c_id
 WHERE d.{$column_7} IS NOT NULL)

ORDER BY priority DESC, title ASC; -- Sorts first by priority (highest first), then by title alphabetically

• The parentheses around each subquery are optional but make the query easier to read.
• Use UNION ALL instead of UNION if you don't need to remove duplicate rows—it's significantly faster.

Quick Troubleshooting Tip #

If your filter still isn't working, run each subquery individually to check if NULLs are being returned. This will help you isolate which part of the UNION is still including unwanted records.

内容的提问来源于stack exchange，提问作者Simon