Conceptually, the first stage of any join is the creation of a Cartesian product. To refine or constrain this Cartesian product and eliminate meaningless combinations of rows of data, include a WHERE clause with a valid join condition in your SELECT statement.
This section illustrates cross joins, equi-joins, natural joins, and multiple-table joins. Additional complex forms, such as self-joins and outer joins, are discussed in Composing Advanced SELECT Statements.
A cross join combines all rows in all tables selected and creates a Cartesian product. The results of a cross join can be very large and difficult to manage.
Figure 107 uses ANSI join syntax to create a cross join.
SELECT * FROM customer CROSS JOIN state
The results of Figure 107 are identical to the results of Figure 105. In addition, you can filter a cross join by specifying a WHERE clause.
For more information about Cartesian products, see Creating a Cartesian Product. For more information about ANSI syntax, see ANSI Join Syntax.
An equi-join is a join based on equality or matching column values. This equality is indicated with an equal sign (=) as the comparison operator in the WHERE clause, as Figure 108 shows.
SELECT * FROM manufact, stock WHERE manufact.manu_code = stock.manu_code
Figure 108 joins the manufact and stock tables on the manu_code column. It retrieves only those rows for which the values of the two columns are equal, some of which Figure 109 shows.
manu_code SMT manu_name Smith lead_time 3 stock_num 1 manu_code SMT description baseball gloves unit_price $450.00 unit case unit_descr 10 gloves/case manu_code SMT manu_name Smith lead_time 3 stock_num 5 manu_code SMT description tennis racquet unit_price $25.00 unit each unit_descr each manu_code SMT manu_name Smith lead_time 3 stock_num 6 manu_code SMT description tennis ball unit_price $36.00 unit case unit_descr 24 cans/case manu_code ANZ manu_name Anza lead_time 5 stock_num 5 manu_code ANZ description tennis racquet unit_price $19.80 unit each unit_descr each
·
·
·
In this equi-join, Figure 109 includes the manu_code column from both the manufact and stock tables because the select list requested every column.
You can also create an equi-join with additional constraints, where the comparison condition is based on the inequality of values in the joined columns. These joins use a relational operator in addition to the equal sign (=) in the comparison condition that is specified in the WHERE clause.
To join tables that contain columns with the same name, qualify each column name with the name of its table and a period symbol (.), as Figure 110 shows.
SELECT order_num, order_date, ship_date, cust_calls.*
FROM orders, cust_calls
WHERE call_dtime >= ship_date
AND cust_calls.customer_num = orders.customer_num
ORDER BY orders.customer_num Figure 110 joins the customer_num column and then selects only those rows where the call_dtime in the cust_calls table is greater than or equal to the ship_date in the orders table. Figure 111 shows the combined rows that it returns.
order_num 1004
order_date 05/22/1998
ship_date 05/30/1998
customer_num 106
call_dtime 1998-06-12 08:20
user_id maryj
call_code D
call_descr Order received okay, but two of the cans of
ANZ tennis balls within the case were empty
res_dtime 1998-06-12 08:25
res_descr Authorized credit for two cans to customer,
issued apology. Called ANZ buyer to report
the qa problem.
order_num 1008
order_date 06/07/1998
ship_date 07/06/1998
customer_num 110
call_dtime 1998-07-07 10:24
user_id richc
call_code L
call_descr Order placed one month ago (6/7) not received.
res_dtime 1998-07-07 10:30
res_descr Checked with shipping (Ed Smith). Order out
yesterday-was waiting for goods from ANZ.
Next time will call with delay if necessary.
order_num 1023
order_date 07/24/1998
ship_date 07/30/1998
customer_num 127
call_dtime 1998-07-31 14:30
user_id maryj
call_code I
call_descr Received Hero watches (item # 304) instead
of ANZ watches
res_dtime
res_descr Sent memo to shipping to send ANZ item 304
to customer and pickup HRO watches. Should
be done tomorrow, 8/1 A natural join is a type of equi-join and is structured so that the join column does not display data redundantly, as Figure 112 shows.
SELECT manu_name, lead_time, stock.* FROM manufact, stock WHERE manufact.manu_code = stock.manu_code
Like the example for equi-join, Figure 112 joins the manufact and stock tables on the manu_code column. Because the select list is more closely defined, the manu_code is listed only once for each row retrieved, as Figure 113 shows.
manu_name Smith lead_time 3 stock_num 1 manu_code SMT description baseball gloves unit_price $450.00 unit case unit_descr 10 gloves/case manu_name Smith lead_time 3 stock_num 5 manu_code SMT description tennis racquet unit_price $25.00 unit each unit_descr each manu_name Smith lead_time 3 stock_num 6 manu_code SMT description tennis ball unit_price $36.00 unit case unit_descr 24 cans/case manu_name Anza lead_time 5 stock_num 5 manu_code ANZ description tennis racquet unit_price $19.80 unit each unit_descr each
·
·
·
All joins are associative; that is, the order of the joining terms in the WHERE clause does not affect the meaning of the join.
Both statements in Figure 114 create the same natural join.
SELECT catalog.*, description, unit_price, unit, unit_descr
FROM catalog, stock
WHERE catalog.stock_num = stock.stock_num
AND catalog.manu_code = stock.manu_code
AND catalog_num = 10017
SELECT catalog.*, description, unit_price, unit, unit_descr
FROM catalog, stock
WHERE catalog_num = 10017
AND catalog.manu_code = stock.manu_code
AND catalog.stock_num = stock.stock_num Each statement retrieves the row that Figure 115 shows.
catalog_num 10017
stock_num 101
manu_code PRC
cat_descr
Reinforced, hand-finished tubular. Polyurethane belted.
Effective against punctures. Mixed tread for super wear
and road grip.
cat_picture <BYTE value>
cat_advert Ultimate in Puncture Protection, Tires
Designed for In-City Riding
description bicycle tires
unit_price $88.00
unit box
unit_descr 4/box Figure 114 includes a TEXT column, cat_descr; a BYTE column, cat_picture; and a VARCHAR column, cat_advert.
A multiple-table join connects more than two tables on one or more associated columns; it can be an equi-join or a natural join.
Figure 116 creates an equi-join on the catalog, stock, and manufact tables.
SELECT * FROM catalog, stock, manufact
WHERE catalog.stock_num = stock.stock_num
AND stock.manu_code = manufact.manu_code
AND catalog_num = 10025 Figure 116 retrieves the rows that Figure 117 shows.
catalog_num 10025 stock_num 106 manu_code PRC cat_descr Hard anodized alloy with pearl finish; 6mm hex bolt hard ware. Available in lengths of 90-140mm in 10mm increments. cat_picture <BYTE value> cat_advert ProCycle Stem with Pearl Finish stock_num 106 manu_code PRC description bicycle stem unit_price $23.00 unit each unit_descr each manu_code PRC manu_name ProCycle lead_time 9
The manu_code is repeated three times, once for each table, and stock_num is repeated twice.
To avoid the considerable duplication of a multiple-table query such as Figure 116, include specific columns in the projection list to define the SELECT statement more closely, as Figure 118 shows.
SELECT catalog.*, description, unit_price, unit,
unit_descr, manu_name, lead_time
FROM catalog, stock, manufact
WHERE catalog.stock_num = stock.stock_num
AND stock.manu_code = manufact.manu_code
AND catalog_num = 10025 Figure 118 uses a wildcard to select all columns from the table with the most columns and then specifies columns from the other two tables. Figure 119 shows the natural join that Figure 118 produces. It displays the same information as the previous example, but without duplication.
catalog_num 10025
stock_num 106
manu_code PRC
cat_descr
Hard anodized alloy with pearl finish. 6mm hex bolt
hardware. Available in lengths of 90-140mm in 10mm increments.
cat_picture <BYTE value>
cat_advert ProCycle Stem with Pearl Finish
description bicycle stem
unit_price $23.00
unit each
unit_descr each
manu_name ProCycle
lead_time 9