Русский    English

Using multiple tables in a query page 2

Example 5.6.1

Find the model and the maker of PC priced below $600:

  1. SELECT DISTINCT PC.model, maker
  2. FROM PC, Product
  3. WHERE PC.model = Product.model AND
  4. price < 600;

As a result each model of the same maker occurs in the result set only once:

model maker
1232 A
1260 E

Sometimes the table in the FROM clause need to be pointed more than once. In this case renaming is indispensable.

Example 5.6.2

Find the model pairs with equal price:

  1. SELECT DISTINCT a.model AS model_1, b.model AS model_2
  2. FROM PC AS a, PC b
  3. WHERE a.price = b.price AND
  4. a.model < b.model;

Here the condition A.model < B.model is to issue one of similar pairs that is only distinguished by rearrangement, for example: {1232, 1233} and {1233, 1232}. The DISTINCT keyword is only used to eliminate duplicate rows because equal models with the same price are in the PC table. As a result, we get the following table:

model_1 model_2
1232 1233
1232 1260

Renaming is also needed in case the FROM clause uses a subquery. So, the first example can be rewritten as follows:

  1. SELECT DISTINCT PC.model, maker
  2. FROM PC, (SELECT maker, model
  3. FROM Product
  4. ) AS Prod
  5. WHERE pc.model = Prod.model AND
  6. price < 600;

Note that in this case the Product qualifier may not be already used in other clauses of the SELECT statement. This is because the Product table is just out of use. Instead of this name the Prod alias is used. Moreover, references are only possible to those Product table columns listed in the subquery.

Behind a alias of derived table expression in brackets there can be a list of names of columns which will be used instead of columns names of table expression. The order of names should naturally be corresponding to the list of columns of table expression (in our case - to the list in SELECT clause). Thus we can avoid ambiguity of names and, as consequence, necessities of their specification. The previous example now can be rewritten as:

  1. SELECT DISTINCT model, maker
  2. FROM PC, (SELECT maker, model
  3. FROM Product
  4. ) AS Prod(maker, model_1)
  5. WHERE model = model_1 AND
  6. price < 600;

Suggested exercises: 16, 17, 34, 96, 108

Bookmark and Share
Pages 1 2
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
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.