17:18

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SELECT statement

Conventional set-theoretic operations and the SELECT statement

Union page 1

The UNION keyword is used for integrating queries:

< query 1 >
UNION [ALL]
< 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

SELECT model, price
FROM PC
UNION
SELECT model, price
FROM Laptop
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

SELECT Product.type, PC.model, price
FROM PC INNER JOIN   
     Product ON PC.model = Product.model
UNION
SELECT Product.type, Laptop.model, price 
FROM Laptop INNER JOIN   
     Product ON Laptop.model = Product.model
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

Pages

UNION UNION ALL

Previous [Cartesian product]

[Intersect and Except] Next

Last added:

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 Problems and solutions

S. I. Moiseenko

SELECT statement

Conventional set-theoretic operations and the SELECT statement

Union page 1