MySQL Union query
SQL querying data SELECT WHERE
We can combine more than one SELECT query by using UNION command to get single result set.
All SELECT queries ( return ) number of column and data type must be same.
Using UNION ALL we can return even duplicate records
Using only UNION will return only distinct records.
For our example we will use two tables ( download SQL dump at end of this tutorial ) one is section_a and other one is section_b
Section A
id name age mark
1 Alex 17 40
2 Rohn 18 44
3 Greek 18 46
4 Lorn 20 44
5 Ravi 20 48
6 Jem 19 43
Section B
id name age mark
1 Big 20 45
2 Remi 19 46
3 Greek 18 46
4 Lorn 20 44
5 Pickn 21 49
6 Tayler 20 41
Distinct records
Unique records are collected, even if we don't use DISTINCT command , the output will be same. ( Id no 3 & 4 of section_b are not there in result )
SELECT ID, name, age, mark from section_a
UNION DISTINCT
SELECT ID, name, age, mark from section_b
ID name age mark
1 Alex 17 40
2 Rohn 18 44
3 Greek 18 46
4 Lorn 20 44
5 Ravi 20 48
6 Jem 19 43
1 Big 20 45
2 Remi 19 46
5 Pickn 21 49
6 Tayler 20 41
UNION ALL
All records are returned ( id = 3 & id =4 are duplicate )
SELECT ID, name, age, mark from section_a
UNION ALL
SELECT ID, name, age, mark from section_b
ID name age mark
1 Alex 17 40
2 Rohn 18 44
3 Greek 18 46
4 Lorn 20 44
5 Ravi 20 48
6 Jem 19 43
1 Big 20 45
2 Remi 19 46
3 Greek 18 46
4 Lorn 20 44
5 Pickn 21 49
6 Tayler 20 41
With Limit
Take 3 records from each table and Join ( Use parentheses )
(SELECT ID, name, age, mark from section_a LIMIT 0,3)
UNION ALL
(SELECT ID, name, age, mark from section_b LIMIT 0,3)
More on SQL LIMIT
ID name age mark
1 Alex 17 40
2 Rohn 18 44
3 Greek 18 46
1 Big 20 45
2 Remi 19 46
3 Greek 18 46
In above query if you remove ALL ( Keep only UNION ) then 3rd record of section_b will be removed as it is a duplicate record.
(SELECT ID, name, age, mark from section_a LIMIT 0,3)
UNION
(SELECT ID, name, age, mark from section_b LIMIT 0,3)
ID name age mark
1 Alex 17 40
2 Rohn 18 44
3 Greek 18 46
1 Big 20 45
2 Remi 19 46
Adding Limit query to all records
(SELECT ID, name, age, mark from section_a LIMIT 0,3)
UNION
(SELECT ID, name, age, mark from section_b LIMIT 0,3) LIMIT 0,3
ID name age mark
1 Alex 17 40
2 Rohn 18 44
3 Greek 18 46
With Order by
(SELECT ID, name, age, mark from section_a )
UNION
(SELECT ID, name, age, mark from section_b )
Order By mark LIMIT 0,5
More on SQL Order By
ID name age mark
1 Alex 17 40
6 Tayler 20 41
6 Jem 19 43
4 Lorn 20 44
2 Rohn 18 44
We can list records in the order of two different columns of two different tables after joining by Union All query.
(SELECT ID, name, age, mark from section_a )
UNION ALL
(SELECT ID, name, age, mark from section_b )
Order By mark LIMIT 0,6
ID name age mark
1 Alex 17 40
6 Tayler 20 41
6 Jem 19 43
4 Lorn 20 44
4 Lorn 20 44
2 Rohn 18 44
Now in the above result records from both the tables are displayed. We can add table names to identify records of different tables
(SELECT ID, name, mark, 'Sec_A' as SEC from section_a )
UNION ALL
(SELECT ID, name, mark, 'Sec_B' as SEC from section_b )
Order By mark LIMIT 0,6
ID name mark SEC
1 Alex 40 Sec_A
6 Tayler 41 Sec_B
6 Jem 43 Sec_A
4 Lorn 44 Sec_A
4 Lorn 44 Sec_B
2 Rohn 44 Sec_A
Using WHERE condition
We will add WHERE condition to one of the table
(SELECT ID, name, mark , 'Sec_A' as SEC from section_a WHERE mark >45)
UNION
(SELECT ID, name, mark,'Sec_B' as SEC from section_b )
Order By mark LIMIT 0,6
ID name mark SEC
6 Tayler 41 Sec_B
4 Lorn 44 Sec_B
1 Big 45 Sec_B
3 Greek 46 Sec_A
2 Remi 46 Sec_B
3 Greek 46 Sec_B
Marks of more than 45 of table section_a is included in above output.
SQL Dump of Section_A and Section_B tables
Order by two columns of two different tables joined by Union All →
← MySQL Inner Join
SQL LEFT Join to link tables →
← Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
plus2net.com
sridhar Kumar 03-09-2012
thanks a lot for ALL addition
LAKHWINDER 02-05-2019
hi,
looking for an answer for question: Using the UNION Operator, list all students majoring in English (ENGL) and Computer Science (COSC), order by major.
thanks
smo1234 02-05-2019
Detail query on using Order by is added.