UNION rules: must each SELECT in a UNION return the same number of columns with union-compatible data types?

Introduction / Context: The UNION family combines multiple query results into a single result set. To merge rows correctly, SQL needs the same column structure across all branches. This question checks whether each SELECT in a UNION must match in column count and have union-compatible data types.

Given Data / Assumptions:

• UNION and UNION ALL stack rows vertically.
• Corresponding positions (1st column, 2nd column, etc.) must align.
• Type compatibility means values can be coerced to a common supertype.

Concept / Approach: SQL aligns columns by ordinal position, not by name. Therefore, each SELECT must project the same number of columns. Their data types must be compatible so the DBMS can determine a common type for the final column. UNION removes duplicates; UNION ALL preserves all rows, but both require structural compatibility.

Step-by-Step Solution:

Verification / Alternative check: Try a UNION where one SELECT has an extra column; the DBMS raises an error. Try incompatible types without casting; the DBMS rejects or implicitly converts within its rules.

Why Other Options Are Wrong:

• Incorrect: Contradicts the core UNION rule.
• Only for UNION ALL / ORDER BY / numeric columns: The requirement applies to UNION and UNION ALL alike, regardless of ORDER BY or specific data types.

Common Pitfalls: Assuming column names matter for alignment; forgetting explicit CAST to harmonize types; mismatched column counts.

Final Answer: Correct