MySQL Union query

  • 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

idnameagemark
1Alex1740
2Rohn1844
3Greek1846
4Lorn2044
5Ravi2048
6Jem1943

Section B

idnameagemark
1Big2045
2Remi1946
3Greek1846
4Lorn2044
5Pickn2149
6Tayler2041

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
IDnameagemark
1Alex1740
2Rohn1844
3Greek1846
4Lorn2044
5Ravi2048
6Jem1943
1Big2045
2Remi1946
5Pickn2149
6Tayler2041

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
IDnameagemark
1Alex1740
2Rohn1844
3Greek1846
4Lorn2044
5Ravi2048
6Jem1943
1Big2045
2Remi1946
3Greek1846
4Lorn2044
5Pickn2149
6Tayler2041

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

IDnameagemark
1Alex1740
2Rohn1844
3Greek1846
1Big2045
2Remi1946
3Greek1846
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)
IDnameagemark
1Alex1740
2Rohn1844
3Greek1846
1Big2045
2Remi1946
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
IDnameagemark
1Alex1740
2Rohn1844
3Greek1846

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

IDnameagemark
1Alex1740
6Tayler2041
6Jem1943
4Lorn2044
2Rohn1844
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
IDnameagemark
1Alex1740
6Tayler2041
6Jem1943
4Lorn2044
4Lorn2044
2Rohn1844
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
IDnamemarkSEC
1Alex40Sec_A
6Tayler41Sec_B
6Jem43Sec_A
4Lorn44Sec_A
4Lorn44Sec_B
2Rohn44Sec_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
IDnamemarkSEC
6Tayler41Sec_B
4Lorn44Sec_B
1Big45Sec_B
3Greek46Sec_A
2Remi46Sec_B
3Greek46Sec_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


Subscribe

* indicates required
Subscribe to plus2net

    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.

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer