Explain UNION and UNION ALL in SQL

What is the difference between `UNION` and `UNION ALL` in SQL? Explain how duplicate rows, compatible columns, ordering, and execution work affect the choice. This is a conceptual SQL question; no application tables or query task are supplied. ### Constraints & Assumptions - Compare complete result rows, not only one chosen key column. - Distinguish preserving duplicates from accidentally introducing duplicate business records. - Do not assume a stable output order without an explicit final `ORDER BY`. ### Clarifying Questions to Ask - Are repeated rows meaningful observations that must be retained? - Should the inputs be disjoint, or can the same result row legitimately appear in both? ```hint Consider one input containing the value 2 twice and another input containing 2 once. How many copies should each operator return? ``` ### What a Strong Answer Covers Define the duplicate behavior, explain union compatibility, and give a small example. Discuss why duplicate elimination may add work without claiming a universal performance ratio, and connect the choice to intended result semantics. ### Follow-up Questions - Does `UNION` remove duplicates that occur within one input as well as across the inputs? - Would selecting an additional unique column change which rows are duplicates? - Where should `ORDER BY` appear to order the combined result?