Bird
Raised Fist0
MySQLquery~5 mins

UNION and UNION ALL in MySQL

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction
UNION and UNION ALL combine results from two or more queries into one list. They help you see data from different places together.
You want to list all customers from two different regions in one list.
You need to combine product names from two tables to see everything available.
You want to merge sales records from two months into one report.
You want to get all email addresses from two contact lists, including duplicates.
You want to combine unique job titles from two departments.
Syntax
MySQL
SELECT column1, column2 FROM table1
UNION [ALL]
SELECT column1, column2 FROM table2;
UNION removes duplicate rows by default.
UNION ALL keeps all rows, including duplicates.
Examples
Combines customer names from north and south regions, removing duplicates.
MySQL
SELECT name FROM customers_north
UNION
SELECT name FROM customers_south;
Lists all products from both stores, including duplicates.
MySQL
SELECT product FROM store_a
UNION ALL
SELECT product FROM store_b;
Shows unique cities where offices are located in Europe and Asia.
MySQL
SELECT city FROM offices_europe
UNION
SELECT city FROM offices_asia;
Sample Program
This example creates two fruit tables and combines their names using UNION to show unique fruits.
MySQL
CREATE TABLE fruits1 (name VARCHAR(20));
CREATE TABLE fruits2 (name VARCHAR(20));

INSERT INTO fruits1 VALUES ('Apple'), ('Banana'), ('Cherry');
INSERT INTO fruits2 VALUES ('Banana'), ('Date'), ('Apple');

SELECT name FROM fruits1
UNION
SELECT name FROM fruits2;
OutputSuccess
Important Notes
All SELECT statements must have the same number of columns and compatible data types.
UNION sorts the result by default, which can be slower on large data sets.
Use UNION ALL if you want faster results and don't mind duplicates.
Summary
UNION combines results and removes duplicates.
UNION ALL combines results and keeps duplicates.
Use UNION when you want unique rows; use UNION ALL to keep all rows.

Practice

(1/5)
1. What is the main difference between UNION and UNION ALL in MySQL?
easy
A. UNION sorts the result, UNION ALL does not.
B. UNION keeps all rows including duplicates, UNION ALL removes duplicates.
C. UNION only works with numeric columns, UNION ALL works with all data types.
D. UNION removes duplicate rows, UNION ALL keeps all rows including duplicates.

Solution

  1. Step 1: Understand UNION behavior

    UNION combines results from two queries and removes duplicate rows to return only unique rows.

  2. Step 2: Understand UNION ALL behavior

    UNION ALL combines results but keeps all rows, including duplicates, without filtering.

  3. Final Answer:

    UNION removes duplicate rows, UNION ALL keeps all rows including duplicates. -> Option D

  4. Quick Check:

    Duplicates removed = UNION, duplicates kept = UNION ALL [OK]
Hint: Remember: UNION = unique rows, UNION ALL = all rows [OK]
Common Mistakes:
  • Thinking UNION ALL removes duplicates
  • Confusing sorting behavior with UNION
  • Assuming UNION only works on numeric data
2. Which of the following is the correct syntax to combine two SELECT queries using UNION ALL in MySQL?
easy
A. SELECT * FROM table1 UNION ALL SELECT * FROM table2;
B. SELECT * FROM table1 UNIONALL SELECT * FROM table2;
C. SELECT * FROM table1 UNION, ALL SELECT * FROM table2;
D. SELECT * FROM table1 UNION; ALL SELECT * FROM table2;

Solution

  1. Step 1: Check correct keyword spacing

    The correct keyword is UNION ALL with a space between UNION and ALL.

  2. Step 2: Validate SQL syntax

    The syntax must be: SELECT ... UNION ALL SELECT ...; without commas or semicolons between the two SELECTs.

  3. Final Answer:

    SELECT * FROM table1 UNION ALL SELECT * FROM table2; -> Option A

  4. Quick Check:

    Correct keyword and syntax = SELECT * FROM table1 UNION ALL SELECT * FROM table2; [OK]
Hint: Use space between UNION and ALL, no commas [OK]
Common Mistakes:
  • Writing UNIONALL as one word
  • Adding commas between SELECT statements
  • Using semicolons between SELECTs
3. Given two tables:
Table A:
id | name
1 | Alice
2 | Bob

Table B:
id | name
2 | Bob
3 | Carol

What will be the result of this query?
SELECT id, name FROM A UNION SELECT id, name FROM B;
medium
A. 2 | Bob
3 | Carol
B. 1 | Alice
2 | Bob
2 | Bob
3 | Carol
C. 1 | Alice
2 | Bob
3 | Carol
D. 1 | Alice
3 | Carol

Solution

  1. Step 1: Combine rows from both tables

    Table A has (1, Alice) and (2, Bob). Table B has (2, Bob) and (3, Carol).

  2. Step 2: Apply UNION behavior

    UNION removes duplicates, so (2, Bob) appears only once in the result.

  3. Final Answer:

    1 | Alice
    2 | Bob
    3 | Carol     -> Option C

  4. Quick Check:

    Duplicates removed, unique rows shown [OK]
Hint: UNION removes duplicates, so repeated rows appear once [OK]
Common Mistakes:
  • Including duplicate rows with UNION
  • Confusing UNION with UNION ALL output
  • Ignoring duplicate row removal
4. Consider this query:
SELECT id FROM table1
UNION ALL
SELECT id FROM table2 ORDER BY id;

What is wrong with this query?
medium
A. ORDER BY must be applied only once at the end of the UNION ALL query.
B. UNION ALL cannot be used with ORDER BY.
C. Each SELECT must have its own ORDER BY clause.
D. ORDER BY should come before UNION ALL.

Solution

  1. Step 1: Understand ORDER BY with UNION ALL

    ORDER BY applies to the entire combined result, not to individual SELECTs.

  2. Step 2: Identify correct placement

    ORDER BY must be placed after the last SELECT and UNION ALL, not inside one SELECT.

  3. Final Answer:

    ORDER BY must be applied only once at the end of the UNION ALL query. -> Option A

  4. Quick Check:

    ORDER BY after UNION ALL, not inside SELECT [OK]
Hint: Place ORDER BY after all UNION ALL parts [OK]
Common Mistakes:
  • Putting ORDER BY inside one SELECT only
  • Trying ORDER BY before UNION ALL
  • Assuming UNION ALL disallows ORDER BY
5. You have two tables:
Employees:
id | name
1 | John
2 | Jane
3 | Mike

Contractors:
id | name
2 | Jane
4 | Anna

You want to list all people from both tables, including duplicates, and sort by name ascending.
Which query achieves this correctly?
hard
A. SELECT id, name FROM Employees UNION SELECT id, name FROM Contractors ORDER BY name ASC;
B. SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY name ASC;
C. SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY id ASC;
D. SELECT id, name FROM Employees UNION ALL ORDER BY name ASC SELECT id, name FROM Contractors;

Solution

  1. Step 1: Choose UNION ALL to keep duplicates

    Since duplicates like Jane (id 2) should appear twice, use UNION ALL.

  2. Step 2: Apply ORDER BY after UNION ALL

    ORDER BY must come after the full UNION ALL to sort combined results by name ascending.

  3. Final Answer:

    SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY name ASC; -> Option B

  4. Quick Check:

    UNION ALL + ORDER BY at end = SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY name ASC; [OK]
Hint: Use UNION ALL for duplicates, ORDER BY after all SELECTs [OK]
Common Mistakes:
  • Using UNION instead of UNION ALL (removes duplicates)
  • Placing ORDER BY inside one SELECT
  • Incorrect syntax with ORDER BY placement
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard