Lesson 15.1

SQL UNION ALL

UNION ALL stacks compatible result sets and keeps every row from each SELECT.

UNION ALL combines rows from one SELECT with rows from another. Think of it as stacking two result grids with the same column shape.

Each SELECT must return the same number of columns, and the matching columns should have compatible types. Column names come from the first SELECT.

Pattern

select 'source one' as source_name, value_column
from first_table
union all
select 'source two' as source_name, value_column
from second_table

Use UNION ALL when duplicates matter

UNION ALL doesn't remove duplicates. That makes it faster and more literal than UNION: every row produced by either input is kept in the combined result.

When the repeated rows are an accident rather than real activity, see why UNION ALL keeps duplicate rows.

Schema · Garden Shop Tables · customers, suppliers 2 tables

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Table · suppliers

One row per supplier. Some suppliers have no contact email.

4 columns · 6 rows

supplier_id int supplier_name text contact_email text state text

Syntax reference

Need syntax details? See the SQL set operations reference

Your task

Build one Oregon contact list from customers and suppliers. Return contact_type, contact_name, and state, using customer and supplier as the type labels. Sort by contact type, then contact name.