A | B | |||
a | b | c | d | |
1 | 2 | 2 | 4 | |
2 | 1 | 3 | 3 |
in the query
SELECT * FROM A, B; |
a | b | c | d |
1 | 2 | 2 | 4 |
1 | 2 | 3 | 3 |
2 | 1 | 2 | 4 |
2 | 1 | 3 | 3 |
As a rule, the table listing is used with the condition of joining records from different tables in the WHERE clause. The condition for the above tables may be a coincidence of the values, say, in the columns a and c:
SELECT * FROM A, B WHERE a=c; |
Now the result set of that query is the following table:
a | b | c | d |
2 | 1 | 2 | 4 |
i.e. only those table rows that have equal values in specified columns (equi-join) are joined. Although some arbitrary conditions may be used, nevertheless the equi-join is most commonly used because this operation reproduces a certain entity split by two entities as a result of a normalization process.
Even though different tables have columns with the same names, the dot notation is needed for uniqueness:
<table name>.<column name>
In cases where ambiguity is not available, that notation need not be used.
Example. Find the model and the maker of PC priced below $600:
SELECT DISTINCT PC.model, maker FROM PC, Product WHERE PC.model = Product.model AND price < 600; |
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. Find the model pairs with equal price:
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; |
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:
SELECT DISTINCT PC.model, maker FROM PC, (SELECT maker, model FROM Product) AS prod WHERE PC.model = prod.model AND 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.
FROM <table1>
[INNER]
| {{LEFT | RIGHT | FULL } [OUTER]} JOIN <table2>
[ON <predicate>]
A join type may be either the inner or one of the outers. The INNER and OUTER keywords may be omitted, because the outer join is uniquely defined by its type: LEFT, RIGHT, or FULL, whereas the inner join is simply refered to as JOIN.
A predicate specifies the condition of joining the rows from different tables. In so doing INNER JOIN means that the result set will only include those combinations of rows in two tables for which the predicate elavuates to TRUE. As a rule, the predicate specifies the equi-join on foreign and primary keys of the tables joined, although need not be so.
Example. Find the maker, model number, and price for each computer in the database:
SELECT maker, Product.model AS model_1, PC.model AS model_2, price FROM Product INNER JOIN PC ON PC.model = Product.model ORDER BY maker, PC.model; |
maker | model_1 | model_2 | price |
A | 1232 | 1232 | 600.0 |
A | 1232 | 1232 | 400.0 |
A | 1232 | 1232 | 350.0 |
A | 1232 | 1232 | 350.0 |
A | 1233 | 1233 | 600.0 |
A | 1233 | 1233 | 950.0 |
A | 1233 | 1233 | 980.0 |
B | 1121 | 1121 | 850.0 |
B | 1121 | 1121 | 850.0 |
B | 1121 | 1121 | 850.0 |
E | 1260 | 1260 | 350.0 |
The LEFT JOIN implies that all the rows from the first (left) table are to be in the result set along with the rows for which the predicate evaluates to true. In so doing, the non-matching colunm values in the right table are returned as null values.
Example. Find all PC models, makers, and prices:
SELECT maker, Product.model AS model_1, PC.model AS model_2, price FROM Product LEFT JOIN PC ON PC.model = Product.model WHERE type = 'PC' ORDER BY maker, PC.model; |
maker | model_1 | model_2 | price |
A | 1232 | 1232 | 600.0 |
A | 1232 | 1232 | 400.0 |
A | 1232 | 1232 | 350.0 |
A | 1232 | 1232 | 350.0 |
A | 1233 | 1233 | 600.0 |
A | 1233 | 1233 | 950.0 |
A | 1233 | 1233 | 980.0 |
B | 1121 | 1121 | 850.0 |
B | 1121 | 1121 | 850.0 |
B | 1121 | 1121 | 850.0 |
E | 2111 | NULL | NULL |
E | 2112 | NULL | NULL |
E | 1260 | 1260 | 350.0 |
Because models 2111 and 2112 in the Product table are absent in the PC
table, the columns of the PC table contain NULL.
The RIGHT JOIN is the reverse of the LEFT JOIN, i.e. the result set will
include all the rows from the second table and only those rows from the first table
for which the join condition is met. In our case the left join
Product LEFT JOIN PC ON PC.model = Product.model
will be equivalent to the following right join
PC RIGHT JOIN Product ON PC.model = Product.model
Just the query
SELECT maker, Product.model AS model_1, PC.model AS model_2, price FROM Product RIGHT JOIN PC ON PC.model = Product.model ORDER BY maker, PC.model; |
SELECT A.*, B.* FROM A FULL JOIN B ON A.a = B.c; |
a | b | c | d |
1 | 2 | NULL | NULL |
2 | 1 | 2 | 4 |
NULL | NULL | 3 | 3 |
Note this join is symmetrical, i.e. "A FULL JOIN B" is equivalent to "B FULL JOIN A". Also, it should be noted that notation A.* implies "all column from the table".
Home | SELECT exercises (rating stages) | DML exercises | Developers |