Introduction

A SQL UNION works only when both queries return the same number of columns in the same order, with compatible data types. If two tables have different shapes, the solution is not a special kind of union. The solution is to reshape both SELECT statements so they project one shared output schema.

Why the Direct UNION Fails

Suppose you have:

• 'employees(id, name, age)'
• 'customers(user_id, first_name, last_name, email)'

This direct union fails:

SELECT id, name, age FROM employees
UNION
SELECT user_id, first_name, last_name, email FROM customers;

The database rejects it because one side returns three columns and the other side returns four.

Build One Shared Result Shape

The normal fix is to decide what the final result should look like and then make both queries match that shape.

1SELECT
2    id AS entity_id,
3    name AS display_name,
4    age,
5    NULL AS email
6FROM employees
7UNION ALL
8SELECT
9    user_id AS entity_id,
10    CONCAT(first_name, ' ', last_name) AS display_name,
11    NULL AS age,
12    email
13FROM customers;

Now both SELECT statements return four columns in the same order, so the union works.

The important change is not the keyword. It is the explicit output design.

Use NULL, Literals, and Expressions Deliberately

When one source lacks a column that the other source has, use NULL or a fixed value to fill the gap. When the two sources store equivalent information differently, transform one side into a comparable form with an expression.

That is why CONCAT(first_name, ' ', last_name) is useful in the example above. It reshapes the customer row into the same conceptual field as the employee display name.

Prefer UNION ALL Unless You Need Deduplication

UNION removes duplicates. UNION ALL keeps every row. If your real goal is simply to append two differently shaped sources into one result, UNION ALL is often better because it avoids unnecessary deduplication work.

Use plain UNION only when duplicate removal is part of the business requirement.

Make Data Types Compatible

Matching column count is only one rule. Each column position should also have compatible types. If one query returns an integer and the other returns text in the same position, the database may cast automatically or it may fail depending on the engine and types involved.

When needed, cast explicitly:

1SELECT id, CAST(age AS CHAR(10)) AS extra_info
2FROM employees
3UNION ALL
4SELECT user_id, email AS extra_info
5FROM customers;

Explicit casts make the result easier to reason about and reduce engine-dependent surprises.

Add a Source Column When It Helps

When rows from several sources are mixed together, it is often useful to keep track of where each row came from.

1SELECT id, name, 'employee' AS source
2FROM employees
3UNION ALL
4SELECT user_id, first_name, 'customer' AS source
5FROM customers;

That small addition can make downstream reporting and debugging much easier.

Common Pitfalls

Assuming SQL can union tables with different column counts without reshaping the queries first is the root misunderstanding.

Padding columns mechanically with NULL values without deciding what the shared result actually means usually creates confusing output.

Using UNION when UNION ALL would be faster and more faithful to the source data can hide duplicate rows unexpectedly.

Ignoring data-type compatibility can make the query brittle across database engines.

Thinking in terms of "merging tables" instead of "projecting one shared result schema" leads to poor query design.

Summary

• 'UNION requires the same number of columns on both sides.'
• Reshape each SELECT so both queries return one shared output structure.
• Use NULL, literals, expressions, and casts to align missing or different columns.
• Prefer UNION ALL unless duplicate removal is required.
• Design the result around a meaningful shared schema, not just around satisfying SQL syntax.