SQL UNION

SQL Using UNION

  • UNION - returns results from sets of queries.
  • By default, it removes duplicate rows so you see all unique rows and one copy (not two) of duplicates.

    Pictorial representation :

  • UNION ALL will not removes duplicate rows.

    Pictorial representation :

  • A skeleton version of UNION syntax is this:
    SELECT expression1, expression2, ... expression_n
    FROM tables
    [WHERE conditions]
    UNION [ALL]
    SELECT expression1, expression2, ... expression_n
    FROM tables
    [WHERE conditions];
    [ORDER BY union_column1, union_column1, ...] 
    Example of using UNION:
    SQL server, MySQL, Oracle, PostgreSQL :
    select CONCAT(Last_name,CONCAT(' ',First_name)) as Name,
       Telephone_type "Phone type",
       Telephone_number as "Phone number",
       us_state_nm as "USA State"
     FROM customers  c
     JOIN telephones t ON (c.Customer_Id=t.Customer_Id)
     JOIN Addresses a ON (c.Customer_Id=a.Customer_Id)
     JOIN US_states u ON (a.US_State_cd=u.US_State_cd)
     WHERE Address_type='Shipping'
    UNION
     select CONCAT(Last_name,CONCAT(' ',First_name)) as Name,
       Telephone_type "Phone type",
       Telephone_number as "Phone number",
       us_state_nm as "USA State"
     FROM customers  c
     JOIN telephones t ON (c.Customer_Id=t.Customer_Id)
     JOIN Addresses a ON (c.Customer_Id=a.Customer_Id)
     JOIN US_states u ON (a.US_State_cd=u.US_State_cd)
     WHERE Address_type='Home'
    ORDER BY Name;
    The result should be:
    name Phone type Phone number USA State
    Bowman Judith Cell (371) 153-54 New York
    Bowman Judith Cell (371) 153-54 New Jersey
    Bowman Judith Work (371) 143-54 New York
    Bowman Judith Work (371) 143-54 New Jersey
    Darnovsky Marcy Unknown Massachusetts
    Darnovsky Marcy Unknown (881) 141-54 Massachusetts
    Emerson Sandra Unknown Arkansas
    Emerson Sandra Unknown (881) 141-54 Arkansas
    Nixon Ricard Home (978) 667-94 Florida
    Nixon Ricard Work (978) 667-94 Florida
    Redford Robert Unknown (678) 223-94 California
    Redford Robert Home (679) 234-94 California
    Example of using UNION ALL:
    SQL server, MySQL, Oracle, PostgreSQL :
    select CONCAT(Last_name,CONCAT(' ',First_name)) as Name,
       Telephone_type "Phone type",
       Telephone_number as "Phone number",
       us_state_nm as "USA State"
     FROM customers  c
     JOIN telephones t ON (c.Customer_Id=t.Customer_Id)
     JOIN Addresses a ON (c.Customer_Id=a.Customer_Id)
     JOIN US_states u ON (a.US_State_cd=u.US_State_cd)
     WHERE Address_type='Shipping'
    UNION ALL
     select CONCAT(Last_name,CONCAT(' ',First_name)) as Name,
       Telephone_type "Phone type",
       Telephone_number as "Phone number",
       us_state_nm as "USA State"
     FROM customers  c
     JOIN telephones t ON (c.Customer_Id=t.Customer_Id)
     JOIN Addresses a ON (c.Customer_Id=a.Customer_Id)
     JOIN US_states u ON (a.US_State_cd=u.US_State_cd)
     WHERE Address_type='Home'
    ORDER BY Name;
    The result should be:
    name Phone type Phone number USA State
    Bowman Judith Work (371) 143-54 New Jersey
    Bowman Judith Work (371) 143-54 New York
    Bowman Judith Cell (371) 153-54 New Jersey
    Bowman Judith Cell (371) 153-54 New York
    Darnovsky Marcy Unknown (881) 141-54 Massachusetts
    Darnovsky Marcy Unknown (881) 141-54 Massachusetts
    Darnovsky Marcy Unknown Massachusetts
    Darnovsky Marcy Unknown Massachusetts
    Emerson Sandra Unknown Arkansas
    Emerson Sandra Unknown (881) 141-54 Arkansas
    Emerson Sandra Unknown (881) 141-54 Arkansas
    Emerson Sandra Unknown Arkansas
    Nixon Ricard Work (978) 667-94 Florida
    Nixon Ricard Work (978) 667-94 Florida
    Nixon Ricard Home (978) 667-94 Florida
    Nixon Ricard Home (978) 667-94 Florida
    Redford Robert Unknown (678) 223-94 California
    Redford Robert Home (679) 234-94 California
    Redford Robert Home (679) 234-94 California
    Redford Robert Unknown (678) 223-94 California

Using UNION as a kind of IF statement.

You can Display different values for one field, depending on what's in another. Without UNION, you'd have to use several queries or some procedural elements to get the same effect.

Here is an example of that:
SQL server, MySQL, Oracle, PostgreSQL :
select '10% off' as Discount, short_desc as Book,
        price as OldPrice, price * .90 as NewPrice
 from bookstore
 where price < 30.00
 UNION
 select '15% off', short_desc as Book, price, price * .85
 from bookstore
 where price between 30.00 and 40.00
 UNION
 select '20% off', short_desc as Book, price, price * .80
 from bookstore
 where price > 40.00
ORDER BY Book;
The result should be:
discount book oldprice newprice
10% off GDI+ Programming: Creating Custom Controls Using C# 29 26.1
15% off Professional Ajax 32 27.2
20% off Professional C# 42 33.6
15% off Professional JavaScript for Web Developers 35 29.75
15% off Professional Visual Basic 6 Databases 38 32.3
20% off Sams Teach Yourself XML in 21 Days, Third Edition 49 39.2
15% off The Practical SQL, Fourth Edition 39 33.15
15% off XSLT Cookbook, 2nd Edition 32 27.2
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.