UNION rules: must each SELECT in a UNION return the same number of columns with union-compatible data types?
-
ACorrect
-
BIncorrect
-
COnly required for UNION ALL
-
DOnly required if ORDER BY is used
-
EOnly required for numeric columns
Answer
Correct Answer: Correct
Explanation
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:
Ensure every SELECT lists the same count of columns.Align types in each ordinal position (for example, text with text-like, number with number-like).Use casts if needed to enforce compatibility.Choose UNION or UNION ALL based on whether duplicates should be removed.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