loading..
Ðóññêèé    English
01:02

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, 41, 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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
wmu íà wmz îáìåíÿòü . Âñå î ñòðîèòåëüñòâå è íå òîëüêî íîâîñòè ñòðîèòåëüñòâà.
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.