loading..
   English
03:17

Union page 1

The UNION keyword is used for integrating queries:

  1. < query 1 >
  2. UNION [ALL]
  3. < query 2 >

The UNION clause combines the results of two SELECT statements into a single result set. If the ALL parameter is given, all the duplicates of the rows returned are retained; otherwise the result set includes only unique rows. Note that any number of queries may be combined. Moreover, the union order can be changed with parentheses.

The following conditions should be observed:

  • The number of columns of each query must be the same;

  • Result set columns of each query must be compared by the data type to each other (as they follows);

  • The result set uses the column names from the first query;

  • The ORDER BY clause is applied to the union result, so it may only be written at the end of the combined query.

Example 5.7.1

Find the model numbers and prices of the PCs and laptops:

Console
Execute
  1. SELECT model, price
  2. FROM PC
  3. UNION
  4. SELECT model, price
  5. FROM Laptop
  6. ORDER BY price DESC;

model Price
1750 1200
1752 1150
1298 1050
1233 980
1321 970
1233 950
1121 850
1298 700
1232 600
1233 600
1232 400
1232 350
1260 350

Example 5.7.2

Find out the product type, the model number, and the price of the PCs and laptops:

Console
Execute
  1. SELECT Product.type, PC.model, price
  2. FROM PC INNER JOIN
  3. Product ON PC.model = Product.model
  4. UNION
  5. SELECT Product.type, Laptop.model, price
  6. FROM Laptop INNER JOIN
  7. Product ON Laptop.model = Product.model
  8. ORDER BY price DESC;

Type Model price
Laptop 1750 1200
Laptop 1752 1150
Laptop 1298 1050
PC 1233 980
Laptop 1321 970
PC 1233 950
PC 1121 850
Laptop 1298 700
PC 1232 600
PC 1233 600
PC 1232 400
PC 1232 350
PC 1260 350

Suggested exercises: 7, 24, 26, 29, 30, 32, 36, 37, 43, 44, 45, 48, 49, 51, 54, 56, 57, 102, 113, 131

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100