LOS NEONICOTINOIDES

Conceptually,thefirst stageofanyjoinisthecreationofaCartesianproduct. TorefineorconstrainthisCartesianproductandeliminatemeaningless combinationsofrowsofdata,includeaWHEREclausewith avalidjoin conditioninyourSELECTstatement.

Thissectionillustrates crossjoins,equi-joins,naturaljoins,andmultiple-table joins.Additionalcomplexforms, suchasself-joinsand outerjoins,arediscussed inChapter5.

CrossJoin(IDS)

Across joincombinesallrowsinalltablesselectedandcreatesa Cartesian product.Theresultsofacrossjoincanbeverylargeand difficulttomanage.

Figure2-103usesANSIjoinsyntax tocreateacrossjoin.

Theresultsof Figure2-103areidenticaltotheresultsofFigure2-101.In addition,youcanfilteracrossjoinbyspecifyingaWHEREclause.

FormoreinformationaboutCartesianproducts,see“CreatingaCartesian Product”onpage2-50. FormoreinformationaboutANSIsyntax, see“ANSI JoinSyntax”onpage5-14.

Equi-Join

Anequi-joinisa joinbasedonequalityormatchingcolumnvalues.This equalityisindicatedwith anequalsign(=)asthecomparisonoperatorinthe WHEREclause,asFigure2-104shows.

Figure2-104joinsthemanufactandstocktablesonthemanu_codecolumn.It retrievesonlythose rowsforwhichthevaluesofthetwocolumns areequal, someofwhichFigure2-105shows.

SELECT * FROM customer CROSS JOIN state

Figure2-103.Query

SELECT * FROM manufact, stock

WHERE manufact.manu_code = stock.manu_code

Inthis equi-join,Figure2-105includesthemanu_codecolumnfromboththe manufactandstocktablesbecausetheselectlistrequestedeverycolumn.

Youcanalso createanequi-joinwith additionalconstraints,wherethe comparisonconditionisbased ontheinequalityof valuesinthejoined columns.Thesejoinsusearelationaloperatorinadditiontotheequalsign(=) inthecomparisoncondition thatisspecifiedintheWHEREclause.

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 . . .

Tojointablesthatcontaincolumnswith thesame name,qualifyeachcolumn namewith thenameofitstableanda periodsymbol(.),asFigure2-106 shows.

Figure2-106joinsthecustomer_numcolumnand thenselectsonlythoserows wherethecall_dtimeinthecust_callstable isgreaterthanorequaltothe ship_dateintheorderstable.Figure2-107showsthecombinedrowsthatit returns.

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

NaturalJoin

Anaturaljoinisatypeof equi-joinand isstructuredsothatthejoincolumn doesnotdisplaydataredundantly,asFigure2-108shows.

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

Liketheexamplefor equi-join,Figure2-108joinsthemanufactand stock tablesonthemanu_codecolumn.Becausetheselectlistismoreclosely defined,themanu_codeislistedonlyonceforeachrowretrieved,as Figure2-109shows.

SELECT manu_name, lead_time, stock.* FROM manufact, stock

WHERE manufact.manu_code = stock.manu_code

Figure2-108.Query

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 . . .

Alljoinsareassociative;thatis, theorderof thejoiningtermsintheWHERE clausedoesnotaffectthemeaningofthejoin.

BothstatementsinFigure2-110createthesamenaturaljoin.

EachstatementretrievestherowthatFigure2-111shows.

Figure2-110includesaTEXT column,cat_descr;aBYTE column,cat_picture; andaVARCHAR column,cat_advert.

Multiple-TableJoin

Amultiple-table joinconnectsmorethantwotablesononeormoreassociated columns;itcanbeanequi-joinora naturaljoin.

Figure2-112creates anequi-joinonthecatalog,stock,and manufacttables. 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

Figure2-110.Query

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

Figure2-112retrievestherowsthatFigure2-113shows.

Themanu_codeisrepeatedthreetimes,onceforeachtable,andstock_numis repeatedtwice.

Toavoidtheconsiderableduplicationofamultiple-table querysuchas Figure2-112,includespecific columnsintheprojectionlisttodefinethe SELECTstatementmore closely,asFigure2-114 shows.

Figure2-114usesa wildcardtoselectall columnsfromthetablewiththemost columnsandthen specifiescolumns fromtheothertwotables.Figure2-115 SELECT * FROM catalog, stock, manufact

WHERE catalog.stock_num = stock.stock_num AND stock.manu_code = manufact.manu_code AND catalog_num = 10025 Figure2-112.Query 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

Figure2-113.QueryResult

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

showsthenaturaljointhatFigure2-114 produces.Itdisplays thesame informationasthepreviousexample,butwithoutduplication.