Postgres UNION-Based Query Returning Unexpected Null Rows

by

When a UNION or UNION ALL query in PostgreSQL returns rows with unexpected NULL values in columns that clearly have data in the underlying tables, the cause is almost always a mismatch between the columns being combined — not a bug in PostgreSQL itself, which handles UNION very literally.

The Problem

You combine results from two (or more) SELECT statements with UNION, expecting a clean combined result set, but some rows come back with columns showing NULL even though the source data for those rows isn’t null at all:

SELECT id, name, email FROM customers
UNION
SELECT id, name, phone FROM leads;

Depending on column order, you might see phone numbers appearing under the “email” column header, emails missing entirely for leads, or vice versa — the values are there, just not where you expected them.

Why It Happens

UNION in PostgreSQL combines result sets based purely on column position, not column name. If the columns in each SELECT aren’t in the same logical order, PostgreSQL still merges them positionally, silently producing a result that looks broken even though the query technically ran without error. The most common causes are:

  • The two queries select columns representing different things in the same position (e.g. email in the third column of one query, phone in the third column of the other) — PostgreSQL merges them as if they were the same field, with no warning
  • One query is missing a column that the other has, and a placeholder NULL was added to make the column counts match, but it ends up misaligned with what you intended
  • Data type mismatches between corresponding columns cause PostgreSQL to implicitly cast to a common type, sometimes producing unexpected NULL results when the cast can’t cleanly represent the original value

The Fix

First, check the column order and types in both queries side by side — this is almost always where the mismatch is hiding:

SELECT id, name, email FROM customers;
SELECT id, name, phone FROM leads;

If the two source tables genuinely have different columns you want to combine meaningfully, explicitly alias and align them so the intent is unambiguous, rather than relying on positional matching to happen to work out:

SELECT id, name, email AS contact_info, 'customer' AS source_type FROM customers
UNION ALL
SELECT id, name, phone AS contact_info, 'lead' AS source_type FROM leads;

Adding a literal column like source_type also makes it much easier to tell, after the fact, which original table each row in the combined result came from — which is often useful for debugging exactly this kind of column-mismatch issue in the first place.

If you specifically need to combine rows with genuinely different structures without losing information, consider whether a FULL OUTER JOIN or separate queries handled in application code might represent the data more accurately than forcing them into a single UNION. UNION is meant for combining rows that represent the same kind of entity, not for merging fundamentally different record shapes.

Also double check you’re using UNION ALL instead of plain UNION if duplicate rows are expected and meaningful — UNION deduplicates the combined result set based on all columns, which can silently drop rows that look identical after the column mismatch is already causing values to shift.

Still Not Working?

If the columns are correctly aligned and typed but you’re still seeing unexpected nulls, check whether one of the underlying SELECT statements includes a LEFT JOIN that’s producing genuine nulls for unmatched rows before the UNION even runs — in that case, the null isn’t coming from the UNION logic at all, but from the join inside one of the individual queries feeding into it.

Leave a Comment