Lesson 15.2
SQL UNION
UNION stacks result sets like UNION ALL, then removes duplicate rows from the final output.
UNION combines two result sets and removes duplicate rows. It is useful when you want one distinct list from multiple sources, such as every state represented by either customers or suppliers.
The duplicate check compares the full selected row. If you add extra columns such as a source label, rows that used to match may become distinct.
Pattern
select value_column
from first_table
union
select value_column
from second_table
order by value_columnUNION is distinct by default
Reach for UNION when duplicates would be noise. UseUNION ALL when duplicates are meaningful or when you are building a row-level audit list.
Schema · Garden ShopTable · suppliers4 columns · 6 rows
Table · suppliers
4 columns · 6 rowsOne row per supplier. Some suppliers have no contact email.
supplier_id intsupplier_name textcontact_email textstate text
Your task
Return one distinct list of state values found in eithercustomers or suppliers, sorted alphabetically.
SQL Workbench
⌘↵ to run·
Expected answer
- Columns: state.
- Rows: each state that appears in customers or suppliers, once.
- UNION removes duplicate rows from the combined result.