UNION vs UNION ALL
Part of the Beyond the Basics section of Coddy's SQL journey — lesson 16 of 27.
You met UNION in Fundamentals: it stacks two result sets on top of each other. By default it also removes duplicates, which costs an extra sort.
SELECT name FROM authors
UNION
SELECT name FROM editorsUNION ALL does the same stacking but keeps duplicates. It's faster and almost always what you want when you know the two sides can't produce the same row, or when you actually want to count each occurrence.
SELECT name FROM authors
UNION ALL
SELECT name FROM editorsBoth sides must return the same number of columns, in the same order, with compatible types.
Challenge
EasyAvailable tables and columns:
<strong>online_orders</strong>:<strong>order_no</strong>,<strong>amount</strong><strong>retail_orders</strong>:<strong>ticket_no</strong>,<strong>total</strong>
The two tables track orders from different channels with different column names. Return one combined list of every order with the columns:
channel:'online'or'retail'order_id:order_nofrom online,ticket_nofrom retailamount: the order's total (fromamountortotal)
Keep every row (no deduplication). Order by amount descending.
Cheat sheet
UNION stacks two result sets and removes duplicates (slower). UNION ALL stacks and keeps duplicates (faster):
SELECT name FROM authors
UNION ALL
SELECT name FROM editorsBoth sides must have the same number of columns, in the same order, with compatible types. Column names come from the first SELECT.
Try it yourself
-- two SELECTs aligned to the same 3 columns, joined by UNION ALL
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.