UNION in SQL

Level 5180 keystrokes

UNION ALL combines the active customers and the active suppliers into one result, labelled by type and sorted by name; unlike UNION, it keeps duplicate rows and skips the duplicate check.

SQL code

-- First query: active customers
1SELECT
2 name,
3 email,
-- A constant column labels where each row comes from
4 'customer' AS type
5FROM
6 customers
-- Keeps only rows whose status is 'active'
7WHERE
8 status = 'active'
-- UNION ALL appends all rows of the next query and keeps duplicates
-- It is faster than UNION because it skips the duplicate check
9UNION ALL
-- Second query: active suppliers, with the same columns in the same order
10SELECT
11 name,
12 email,
-- These rows are labelled 'supplier'
13 'supplier' AS type
14FROM
15 suppliers
-- Again only active rows
16WHERE
17 status = 'active'
-- ORDER BY sorts the combined result: Acme, Alice, Bob
18ORDER BY
19 name;

Output

+-------+-------------------+----------+
| name  | email             | type     |
+-------+-------------------+----------+
| Acme  | acme@example.com  | supplier |
| Alice | alice@example.com | customer |
| Bob   | bob@example.com   | customer |
+-------+-------------------+----------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • '× 8
  • ,× 4
  • =× 2
  • ;× 1

More SQL examples at level 5

← SQL typing practice