SQL data manipulation language

SELECT statement

Example 5.6.1

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

Console

Execute

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

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

    Scheme

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

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:

Console

Execute

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

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

    Scheme

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:

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

Console

Execute

SELECT DISTINCT PC.model, maker
FROM PC, (SELECT maker, model
 FROM Product
 ) AS Prod
WHERE pc.model = Prod.model AND 
 price < 600;

SELECT DISTINCT PC.model, maker FROM PC, (SELECT maker, model FROM Product ) AS Prod WHERE pc.model = Prod.model AND price < 600;

    Scheme

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:

Console

Execute

SELECT DISTINCT model, maker
FROM PC, (SELECT maker, model
 FROM Product
 ) AS Prod(maker, model_1)
WHERE model = model_1 AND 
 price < 600;

SELECT DISTINCT model, maker FROM PC, (SELECT maker, model FROM Product ) AS Prod(maker, model_1) WHERE model = model_1 AND price < 600;

    Scheme

Content:

(5.6.1.) Explicit join operations
Equi-joins
FULL JOIN and MySQL