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

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:

  1. FROM < TABLE 1 >
  2. [INNER]
  3. {{LEFT | RIGHT | FULL } [OUTER]} JOIN < TABLE 2>
  4. [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
  1. SELECT maker, Product.model AS model_1, PC.model AS model_2, price
  2. FROM Product INNER JOIN
  3. PC ON PC.model = Product.model
  4. 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.

Next page

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

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