Sorting a UNION in SQL

A UNION's ORDER BY belongs to the whole result, not to the last branch.

SELECT
    'customer' AS kind,
    name,
    email
FROM customers
UNION ALL
SELECT
    'signup' AS kind,
    NULL,
    email
FROM newsletter_signups
ORDER BY kind, email;

How it works

1. One ORDER BY at the end sorts the combined rows.
2. A literal column tags which branch a row came from.
3. Branches must agree on column count and line up by position.

Keywords and builtins used here

• ALL
• AS
• BY
• FROM
• NULL
• ORDER
• SELECT
• UNION

The run, in numbers

Lines

12

Characters to type

146

Tokens

28

Three-star pace

90 tpm

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

Type this snippet

Step 2 of 3 in Set operations, step 19 of 22 in Joins & aggregation.

← Previous Next →