SQL UNION vs UNION ALL Explained for Beginners
SQL UNION explained for beginners: learn how UNION and UNION ALL combine query results, remove duplicates, and when to use each, with simple examples.
SQL UNION explained for beginners: learn how UNION and UNION ALL combine query results, remove duplicates, and when to use each, with simple examples.
SQL UNION is a command that stacks the results of two SELECT queries into one list. If you searched for "sql union explained for beginners", here is the short answer: UNION takes the rows from query one, adds the rows from query two, and removes any duplicates. Its sibling UNION ALL does the same but keeps every row. In this guide you will learn how both work, with small examples you can copy and run.
You do not need any experience. We will define every term the first time we use it.
SQL (Structured Query Language) is the language used to ask a database for data. A SELECT query reads rows from a table. Sometimes the data you want lives in two different tables, or comes from two different questions. UNION lets you glue those results together, one on top of the other.
Think of two class lists on paper. UNION is like taping the second list under the first and crossing out names that appear twice.
A JOIN puts tables side by side by adding columns. A UNION puts results on top of each other by adding rows. If you have not met JOIN yet, read our guide first (link below), then come back.
| Feature | JOIN | UNION |
|---|---|---|
| Direction | Adds columns (side by side) | Adds rows (stacked) |
| Needs a matching column | Yes, a key to match on | No, but column counts must match |
| Typical use | Combine related data, like orders and customers | Combine similar lists, like two customer tables |
New to combining tables? Read our JOIN guide first, then come back to UNION.
Read: SQL JOIN explainedLet us create two tables of people who signed up for a newsletter: one from our website and one from an event. You can run these in any SQL database.
CREATE TABLE web_signups (name VARCHAR(50), email VARCHAR(100));
CREATE TABLE event_signups (name VARCHAR(50), email VARCHAR(100));
INSERT INTO web_signups VALUES
('Ana', 'ana@example.com'),
('Ben', 'ben@example.com');
INSERT INTO event_signups VALUES
('Ben', 'ben@example.com'),
('Chloe', 'chloe@example.com');The first two lines create the tables. Each has two columns: name and email. The INSERT statements add rows. Notice that Ben appears in both tables. That detail will matter in a moment.
SELECT name, email FROM web_signups
UNION
SELECT name, email FROM event_signups;The first SELECT reads the website list. The word UNION on its own line joins the two queries. The second SELECT reads the event list. The result has three rows: Ana, Ben and Chloe. Ben shows only once, because UNION removes duplicate rows.
SELECT name, email FROM web_signups
UNION ALL
SELECT name, email FROM event_signups;This looks almost the same, but we wrote UNION ALL. Now the result has four rows, and Ben appears twice. UNION ALL does not check for duplicates, so it is also a little faster on big tables.
| Command | Removes duplicates? | Best when |
|---|---|---|
| UNION | Yes | You want a clean list of unique rows |
| UNION ALL | No | You want every row, or you know there are no duplicates |
UNION is strict. Your two queries must follow these rules:
If you break a rule, the database shows an error such as "each UNION query must have the same number of columns". Do not panic. Just check your column lists.
You can sort a UNION result with ORDER BY, but it goes once, at the very end.
SELECT name, email FROM web_signups
UNION
SELECT name, email FROM event_signups
ORDER BY name DESC;The ORDER BY line sorts the whole combined list by name, from Z to A. If you put ORDER BY after the first SELECT, you will get an error, because the database wants to sort only after everything is stacked.
Often the two tables have different column names. That is fine, as long as the count and types match. You can also add a fixed label so you remember where each row came from.
SELECT name, email, 'website' AS source FROM web_signups
UNION ALL
SELECT name, email, 'event' AS source FROM event_signups;Here 'website' and 'event' are fixed pieces of text. The word AS gives the new column a name, source. Now each row tells you where it came from, and we used UNION ALL so that Ben is shown once per source.
UNION combines the results of two or more SELECT queries into one result set by stacking their rows. It also removes duplicate rows. Both queries must return the same number of columns.
UNION removes duplicate rows from the combined result, while UNION ALL keeps every row. UNION ALL is usually faster because it skips the duplicate check.
A JOIN adds columns by matching rows from related tables side by side. A UNION adds rows by stacking one result on top of another. Use JOIN to connect related data and UNION to merge similar lists.
Yes. The column names can differ, but the number of columns and their data types must be compatible. The final result uses the column names from the first SELECT.
Yes. UNION and UNION ALL are part of standard SQL, so they work in PostgreSQL, MySQL, SQL Server and SQLite with the same syntax.
You now know how to combine query results with UNION and UNION ALL. Try the practice ideas above, because typing the queries yourself is the fastest way to learn.
Want help turning your data into a working web app? Talk to the Fepiq team.
Contact FepiqOccasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.
Keep reading
Learn SQL AND, OR and NOT operators with simple examples. See how to combine conditions in WHERE and avoid the common mistakes beginners make.
Learn JavaScript template literals with simple examples: backticks, ${} variables, and multi-line strings. A beginner-friendly guide you can copy and run.
Let's build something
Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.