loading..
Русский    English
19:52

Union page 2

Let's consider the task.

Find out all available products of the producer 'B'. Output: model, type.

One laptop and three PCs are in the database from maker 'B', and all the PCs are of the same model.

If we'll use UNION ALL, we get all these items. Using UNION,  the duplicates will be excluded from the result:

Console
Execute
  1. SELECT p.model, p.type FROM pc JOIN Product p ON PC.model=p.model WHERE maker='B'
  2. UNION ALL
  3. SELECT p.model, p.type FROM printer pr JOIN Product p ON pr.model=p.model WHERE maker='B'
  4. UNION ALL
  5. SELECT p.model, p.type FROM laptop lp JOIN Product p ON lp.model=p.model WHERE maker='B';

model    type
1121    PC
1121    PC
1121    PC
1750    Laptop

Console
Execute
  1. SELECT p.model, p.type FROM pc JOIN Product p ON PC.model=p.model WHERE maker='B'
  2. UNION
  3. SELECT p.model, p.type FROM printer pr JOIN Product p ON pr.model=p.model WHERE maker='B'
  4. UNION
  5. SELECT p.model, p.type FROM laptop lp JOIN Product p ON lp.model=p.model WHERE maker='B';

model    type
1121    PC
1750    Laptop

The solution on the base of UNION corresponds to the somewhat different task, namely:

Find out which models of the maker 'B' are available. Output: model, type.


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
several days ago
https://exchangesumo.com/obmen/SBRFRUB-WEXRUB/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100