---
title: "UNION and UNION ALL"
description: "UNION combines the result sets of two or more SELECT queries into one, removing duplicate rows, while UNION ALL does the same but keeps all duplicates."
canonical: "https://motherduck.com/glossary/union-and-union-all/"
related:
  - title: "Union.ai | MotherDuck Docs"
    url: "https://motherduck.com/docs/integrations/orchestration/union-ai/"
  - title: "What's New in DuckDB 1.5! | MotherDuck"
    url: "https://motherduck.com/videos/whats-new-duckdb-15/"
  - title: "Running dual execution (or hybrid) queries | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/running-hybrid-queries/"
---

# UNION and UNION ALL

> UNION combines the result sets of two or more SELECT queries into one, removing duplicate rows, while UNION ALL does the same but keeps all duplicates.

## Overview
`UNION` and `UNION ALL` are set operations that stack the results of two or more `SELECT` queries on top of each other, producing a single combined result set. Both queries must return the same number of columns with compatible data types; column names in the output come from the first query.

```sql
SELECT product_id, 'online' AS channel FROM online_sales
UNION ALL
SELECT product_id, 'retail' AS channel FROM retail_sales;
```

<glossary-callout guide="duckdb-cheatsheet-full" />

## UNION vs UNION ALL
`UNION` performs duplicate elimination across the combined rows -- every row in the output is unique, similar to wrapping the result in `SELECT DISTINCT`. `UNION ALL` keeps every row from both inputs, including duplicates, and is the better default when you know the inputs don't overlap or you actually want to preserve duplicate counts (e.g., summing revenue across sources).

Because deduplication requires comparing every output row, `UNION ALL` is almost always faster than `UNION` and should be preferred unless you specifically need duplicates removed.

## DuckDB's UNION BY NAME
Standard `UNION` matches columns positionally -- the first column of the first query lines up with the first column of the second, regardless of name. DuckDB adds `UNION BY NAME` (and `UNION ALL BY NAME`), which instead matches columns by name, filling in `NULL` for columns missing from one side:

```sql
SELECT id, name FROM table_a
UNION BY NAME
SELECT name, id, created_at FROM table_b;
```

This is especially useful when combining schemas that have evolved over time or come from different sources with columns in a different order.

## Related set operations
`UNION`/`UNION ALL` sit alongside `INTERSECT` (rows common to both queries) and `EXCEPT` (rows in the first query but not the second) as SQL's three core set operations.