UNION and UNION ALL in SQL

UNION stacks two result sets that share a column shape.

SELECT
    'order' AS kind,
    placed_at AS happened_at,
    total AS amount
FROM orders
UNION ALL
SELECT
    'refund' AS kind,
    issued_at AS happened_at,
    -amount AS amount
FROM refunds
ORDER BY happened_at DESC
LIMIT 50;

How it works

UNION removes duplicates, UNION ALL keeps them and is faster.
Both sides need the same number and order of columns.
A single ORDER BY at the end sorts the combined result.

Keywords and builtins used here

ALL
AS
BY
DESC
FROM
LIMIT
ORDER
SELECT
UNION

The run, in numbers

Lines: 13
Characters to type: 205
Tokens: 38
Three-star pace: 90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 25 seconds.

Type this snippet

Step 1 of 3 in Set operations, step 18 of 22 in Joins & aggregation.

← Previous Next →