WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

MySQL UNION

Previous Next

MySQL UNION


The UNION operator in MySQL is used to combine results from multiple SELECT queries into a single result set.

  • It removes duplicate rows by default.
  • The number of columns and their data types must be the same in all SELECT statements.


UNION Syntax


SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;


UNION ALL Syntax


If you want to keep duplicate records, use UNION ALL:


SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Note

The column names in the result-set are usually equal to the column names in the first SELECT statement.

SQL UNION Example


We have two tables:


Students_BranchA Table:

student_id

name

course

score

1

Alice

Math

85

2

Bob

Science

70

Students_BranchB Table:

student_id

name

course

score

3

Charlie

Math

90

4

David

English

60

The following SQL statement Combining Students from Two Branches(without duplicates)

Example
SELECT student_id, name, course FROM students_brancha
UNION
SELECT student_id, name, course FROM students_branchb;
Try it yourself

Output:

student_id

name

course

1

Alice

Math

2

Bob

Science

3

Charlie

Math

4

David

English

SQL UNION ALL Example


The following SQL statement Combining Students from Two Branches(with duplicates)

Example
SELECT course FROM students_brancha
UNION ALL
SELECT course FROM students_branchb;
Try it yourself

Output:

course

Math

Science

Math

English
  • Math appears twice because UNION ALLdoes not remove duplicates.

SQL UNION With WHERE


The following SQL statement combine students with score above 80 from both tables.

Example
SELECT student_id, name, course, score
FROM students_brancha
WHERE score > 80
UNION
SELECT student_id, name, course, score
FROM students_branchb
WHERE score > 80;
Try it yourself

Output:


student_id

name

course

score

1

Alice

Math

85

1

Charlie

Math

90

Explanation:

  • Alice from BranchA and Charlie from BranchB have scores above 80.
  • The result is combined using UNION, removing duplicates.


When to Use UNION?

  • Merging Data → Combine results from multiple tables.
  • Avoiding Duplicates → UNION removes duplicates automatically.
  • Keeping Duplicates → Use UNION ALL if duplicates should be retained.
  • Combining Different Conditions → Use it to fetch data with different WHERE conditions.

Previous Next