17:37

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SELECT statement

Using multiple tables in a query

Explicit join operations page 1

Explicit join operation for two and more tables may be present in the FROM clause. Among the join operation series described in the SQL standard, the join-on-predicate operation is only supported by the number of database servers. A join-on-predicate syntax is:

FROM < TABLE 1 >
 [INNER]
 {{LEFT | RIGHT | FULL } [OUTER]} JOIN < TABLE 2>
[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 referred 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 evaluates 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 5.6.3.

Find the maker, model number, and price for each computer in the database:

Console

Execute

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, model_1

In this example, the query returns only the row combinations from the PC and Product tables with identical model numbers.

For better control, the result includes the model number both in the PC table and in the Product table:

Maker	model_1	model_2	price
A	1232	1232	600
A	1232	1232	400
A	1232	1232	350
A	1232	1232	350
A	1233	1233	600
A	1233	1233	950
A	1233	1233	980
A	1233	1233	970
B	1121	1121	850
B	1121	1121	850
B	1121	1121	850
E	1260	1260	350

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 column values in the right table are returned as NULL values.

Suggested exercises: 6, 7, 9, 13, 14, 18, 19, 21, 23, 25, 26, 27, 28, 32, 36, 37, 39, 43, 48, 49, 50, 51, 52, 54, 57, 58, 66, 72, 76, 77, 79, 87, 88, 93, 94, 102, 114, 127

Pages

inner join FROM LEFT OUTER JOIN join operations RIGHT JOIN FULL JOIN

Previous [Using multiple tables in a query]

[Equi-joins] 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

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Using multiple tables in a query

Explicit join operations page 1