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.

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;