MySQL UNION and UNION ALL: Combine SELECT Results

MySQL UNION combines the result sets of two or more SELECT queries into one result. UNION removes duplicate result rows, while UNION ALL keeps them.

SELECT id, name, age, mark
FROM section_a
UNION
SELECT id, name, age, mark
FROM section_b;

UNION Requirements Top ↑

Each SELECT in a UNION must return:

• the same number of columns,
• columns in corresponding positions that MySQL can combine into compatible result types, and
• values that represent the same logical fields if the result is meant to be meaningful.

For example, both queries below return four compatible columns in the same logical order:

SELECT id, name, age, mark
FROM section_a
UNION ALL
SELECT id, name, age, mark
FROM section_b;

Sample Tables Top ↑

The examples use two tables. Rows for Greek and Lorn are identical in both tables.

Download section_a and section_b SQL dump

UNION: Remove Duplicate Rows Top ↑

UNION removes duplicate rows from the final combined result. UNION DISTINCT is accepted, but DISTINCT is the default and normally does not need to be written.

SELECT id, name, age, mark
FROM section_a
UNION
SELECT id, name, age, mark
FROM section_b
ORDER BY id, name;

The two rows that are identical across all four selected columns appear only once.

See DISTINCT for duplicate removal within a single SELECT result.

UNION ALL: Keep All Rows Top ↑

UNION ALL keeps every row returned by every SELECT, including exact duplicate result rows:

SELECT id, name, age, mark
FROM section_a
UNION ALL
SELECT id, name, age, mark
FROM section_b
ORDER BY id, name;

The result contains all 12 source rows because no duplicate elimination is performed.

What Counts as a Duplicate? Top ↑

UNION compares the entire selected row, not just one column such as id.

These two rows are not duplicates:

1, 'Alex', 17, 40
1, 'Big',  20, 45

They share the same ID but differ in other selected columns, so both remain in a UNION result.

These rows are duplicates because every selected value is the same:

3, 'Greek', 18, 46
3, 'Greek', 18, 46

Result Column Names Top ↑

The final result column names come from the first SELECT.

SELECT id AS student_id,
       name AS student_name
FROM section_a
UNION ALL
SELECT id,
       name
FROM section_b;

The combined result columns are named student_id and student_name.

Compatible Column Types Top ↑

Each SELECT must return the same number of columns. Corresponding columns should also contain compatible kinds of data.

This is structurally sensible:

SELECT id,
       name
FROM section_a
UNION ALL
SELECT id,
       name
FROM section_b;

This is not logically sensible even if MySQL can convert the values to a common representation:

-- Avoid mismatching unrelated fields by position
SELECT id,
       name
FROM section_a
UNION ALL
SELECT mark,
       age
FROM section_b;

The second column of every SELECT becomes one result column, so column position matters.

ORDER BY on the Combined Result Top ↑

A final ORDER BY sorts the complete UNION result:

SELECT id, name, age, mark
FROM section_a
UNION ALL
SELECT id, name, age, mark
FROM section_b
ORDER BY mark, id, name;

For deterministic output, include tie-breakers when several rows can have the same sort value.

Ordering by an alias Top ↑

SELECT id,
       name,
       mark AS score
FROM section_a
UNION ALL
SELECT id,
       name,
       mark
FROM section_b
ORDER BY score DESC, id;

The alias score comes from the first SELECT and can be used to order the final result.

LIMIT on the Combined Result Top ↑

Place LIMIT after the final ORDER BY to restrict the complete combined result:

SELECT id, name, age, mark
FROM section_a
UNION ALL
SELECT id, name, age, mark
FROM section_b
ORDER BY mark, id, name
LIMIT 5;

This returns the first five rows after the full 12-row UNION ALL result has been sorted.

LIMIT inside Each SELECT Top ↑

You can also restrict each SELECT separately, but each branch should have its own ORDER BY when "first three" has a specific meaning.

(
    SELECT id, name, age, mark
    FROM section_a
    ORDER BY id
    LIMIT 3
)
UNION ALL
(
    SELECT id, name, age, mark
    FROM section_b
    ORDER BY id
    LIMIT 3
)
ORDER BY id, name;

This selects three deterministic rows from each table and then sorts the six-row combined result.

Branch LIMIT plus final LIMIT Top ↑

(
    SELECT id, name, age, mark
    FROM section_a
    ORDER BY id
    LIMIT 3
)
UNION ALL
(
    SELECT id, name, age, mark
    FROM section_b
    ORDER BY id
    LIMIT 3
)
ORDER BY mark, id, name
LIMIT 3;

The inner LIMIT clauses restrict the source rows first; the final LIMIT restricts the combined result.

Identify Which Table a Row Came From Top ↑

Add a literal source label to each SELECT:

SELECT id,
       name,
       mark,
       'Sec_A' AS section
FROM section_a
UNION ALL
SELECT id,
       name,
       mark,
       'Sec_B'
FROM section_b
ORDER BY mark, section, id;

The extra column identifies the source of each row.

WHERE Conditions in UNION Queries Top ↑

Each SELECT can have its own WHERE condition.

SELECT id,
       name,
       mark,
       'Sec_A' AS section
FROM section_a
WHERE mark > 45

UNION ALL

SELECT id,
       name,
       mark,
       'Sec_B'
FROM section_b

ORDER BY mark, section, id;

The mark filter applies only to section_a. All rows from section_b remain eligible.

Apply the same filter to both tables Top ↑

SELECT id, name, mark
FROM section_a
WHERE mark > 45
UNION ALL
SELECT id, name, mark
FROM section_b
WHERE mark > 45
ORDER BY mark, id, name;

Conditions are part of their individual SELECT statements; UNION only combines the resulting rows.

UNION vs JOIN Top ↑

Example use for UNION: combine current and archived records that share the same result structure.

Example use for JOIN: combine student details with a separate fee-payment table using a common student ID.

PHP PDO Example Top ↑

A static UNION query can be executed with PDO query():

<?php
$sql="SELECT id, name, mark, 'Sec_A' AS section
      FROM section_a
      UNION ALL
      SELECT id, name, mark, 'Sec_B'
      FROM section_b
      ORDER BY mark DESC, section, id";

$stmt=$dbo->query($sql);

foreach($stmt as $row){
    echo '<p>'
        .htmlspecialchars(
            $row['name'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .' - '
        .(int)$row['mark']
        .' - '
        .htmlspecialchars(
            $row['section'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .'</p>';
}

This query contains no external input, so query() is appropriate. If a WHERE value comes from a user or request parameter, use a prepared statement and bind the value in every branch where it is used.

Performance Notes Top ↑

• UNION ALL avoids duplicate elimination and is normally the better choice when duplicates do not need to be removed.
• UNION must remove duplicate result rows, which can require additional processing.
• Apply legitimate WHERE filters in each branch so unnecessary rows are not carried into the combined result.
• Use a final ORDER BY only when ordered output is required.
• For large queries, indexes should support the WHERE conditions used by each SELECT branch.
• Do not use UNION as a workaround for tables that should instead have a better normalized structure.
• Use EXPLAIN on important production queries and measure real performance.

Common UNION Mistakes Top ↑

Returning a different number of columns Top ↑

Every SELECT must return the same number of result columns.

Putting unrelated fields in the same column position Top ↑

UNION combines columns by position, not by column name.

Assuming UNION removes rows that share only one value Top ↑

Duplicate removal compares the complete selected row. Two rows with the same ID but different names or marks are both kept.

Using UNION when UNION ALL is intended Top ↑

If all source rows must remain, use UNION ALL explicitly.

Using branch LIMIT without ORDER BY Top ↑

LIMIT alone does not define which source rows are selected. Add ORDER BY when the chosen rows matter.

Expecting each SELECT to control the final order Top ↑

Use one final ORDER BY to sort the complete combined result. Branch-level ORDER BY is mainly useful when paired with branch-level LIMIT.

Adding a source label and still expecting UNION to remove cross-source duplicates Top ↑

'Sec_A' and 'Sec_B' make the result rows different, so they are no longer duplicates across all selected columns.

Related Tutorials and SQL Dump Top ↑

Download SQL dump of section_a and section_b

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: