内容数据库概论joins

1、SQL Outer Joinsfor Fun and ProfitBill KarwinProprietor/Chief Architect 2006-07-27OSCON 20062IntroductionOverview of SQL joins: inner and outerApplications of outer joinsSolving Sudoku puzzles with outer joins2006-07-27OSCON 20063Joins in SQLJoins:The SQL way to express relations between data in tabl

2、esForm a new row in the result set, from matching rows in each joined tableAs fundamental to using a relational database as a loop is in other programming languages2006-07-27OSCON 20064Inner joins refresherANSI SQL-89 syntax:SELECT . FROM products p, orders oWHERE duct_id = duct_id;ANSI SQ

3、L-92 syntax:SELECT . FROM products p JOIN orders o ON duct_id = duct_id;2006-07-27OSCON 20065Inner join exampleProductsproduct_idAbcDefEfgOrdersproduct_idorder_idAbc10Abc11Def92006-07-27OSCON 20066Inner join exampleQuery result setproduct_idProduct attributesorder_idOrder attributesAbc$10.

4、00102006/2/1Abc$10.00112006/3/10Def$5.0092005/5/2SELECT . FROM products p JOIN orders o ON duct_id = duct_id;2006-07-27OSCON 20067Outer joinsReturns all rows in one table, but only matching rows in joined table. Returns NULL where no row matches.Not supported in SQL-89SQL-92 syntax:SELECT

5、.FROM products pLEFT OUTER JOIN orders o ON duct_id = duct_id;2006-07-27OSCON 20068Types of outer joinsLEFT OUTER JOINReturns all rows from table on left. Returns NULLs in columns of right table where no row matchesRIGHT OUTER JOINReturns all rows from table on right. Returns NULLs in colu

6、mns of left table where no row matches.FULL OUTER JOINReturns all rows from both tables. Returns NULLs in columns of each, where no row matches.2006-07-27OSCON 20069Support for OUTER JOINOpen-source RDBMS products:MySQLPostgreSQLFirebirdSQLiteHypersonic HSQLDBApache DerbyIngres R3LEFT OUTER JOINRIGH

7、T OUTER JOINFULL OUTER JOIN2006-07-27OSCON 200610Outer join exampleProductsproduct_idAbcDefEfgOrdersproduct_idorder_idAbc10Abc11Def9NULLNULL2006-07-27OSCON 200611Outer join exampleQuery result setproduct_idProduct attributesorder_idOrder attributesAbc$10.00102006/2/1Abc$10.00112006/3/10Def$5.0092005

8、/5/2Efg$17.00NULLNULLSELECT .FROM products pLEFT OUTER JOIN orders o ON duct_id = duct_id;2006-07-27OSCON 200612So what?Difference seems trivial and uninterestingSQL works with sets and relationsOperations on sets combine in powerful ways (just like operations on numbers, strings, or boole

9、ans)INNER JOINLEFT OUTER JOINRIGHTOUTER JOINFULLOUTER JOIN2006-07-27OSCON 200613Solutions using outer joinsExtra join conditionsSubtotals per dayLocalizationMimic NOT IN (subquery)Greatest row per group Top three per groupFinding attributes in EAV tables(entity-attribute-value)Sudoku puzzle solver20

10、06-07-27OSCON 200614Extra join conditionsProblem: match only with orders created this year.Put extra conditions on the outer table into the ON clause. This applies the conditions before the join:SELECT .FROM products pLEFT OUTER JOIN orders o ON duct_id = duct_id AND o.date = 2006-01-01;20

11、06-07-27OSCON 200615Extra join conditionsProductsproduct_idAbcDefEfgOrdersproduct_idorder_iddateAbc102006/2/1Abc112006/3/10Def92005/5/2NULLNULLNULL2006-07-27OSCON 200616Extra join conditionsQuery result setproduct_idProduct attributesorder_idOrder attributesAbc$10.00102006/2/1Abc$10.00112006/3/10Def

12、$5.00NULLNULLEfg$17.00NULLNULLSELECT .FROM products pLEFT OUTER JOIN orders o ON duct_id = duct_id AND o.date = 2006-01-01;2006-07-27OSCON 200617Subtotals per dayProblem: show all days, and the subtotal of orders per day even when there are zero.Requires an additional table containing all

13、dates in the desired range.SELECT d.date, COUNT(o.order_id) FROM days d LEFT OUTER JOIN orders o ON o.date = d.dateGROUP BY d.date;2006-07-27OSCON 200618Subtotals per dayDaysdate2005/5/2. . . . . . . . .2006/2/1. . . . . . . . .2006/3/10. . .Ordersdateorder_id2005/5/292006/2/1102006/3/1011NULLNULL20

14、06-07-27OSCON 200619Subtotals per dayQuery result setdateCOUNT()2005/5/21. . .0. . .0. . .0. . .02006/2/11. . .0. . .0. . .0. . .02006/3/101. . .0SELECT d.date, COUNT(o.order_id) FROM days d LEFT OUTER JOIN orders o ON o.date = d.dateGROUP BY d.date;2006-07-27OSCON 200620LocalizationProblem: show tr

15、anslated messages, or in default language if translation is not available.SELECT en.message_id, COALESCE(sp.message, en.message) FROM messages AS sp RIGHT OUTER JOIN messages AS en ON sp.message_id = en.message_id AND sp.language = sp AND en.language = en;COALESCE() returns its first non-null argume

16、nt.2006-07-27OSCON 200621Localizationmessagesmessage_idlanguagemessage123enThank you123spGracias456enHelloNULL2006-07-27OSCON 200622LocalizationQuery result setmessage_idmessage123Gracias456HelloSELECT en.message_id, COALESCE(sp.message, en.message) FROM messages AS sp RIGHT OUTER JOIN messages AS e

17、nON sp.message_id = en.message_idAND sp.language = sp AND en.language = en;2006-07-27OSCON 200623Mimic NOT IN subqueryProblem: find rows for which there is no match.Often implemented using NOT IN (subquery):SELECT .FROM products pWHERE duct_id NOT IN (SELECT duct_id FROM orders o)2006-07-2

18、7OSCON 200624Mimic NOT IN subqueryCan also be implemented using an outer join:SELECT .FROM products pLEFT OUTER JOIN orders o ON duct_id = duct_idWHERE duct_id IS NULL;Useful when subqueries are not supported (e.g. MySQL 4.0)2006-07-27OSCON 200625Mimic NOT IN subqueryProductsproduct_i

19、dAbcDefEfgOrdersproduct_idorder_idAbc10Abc11Def9NULLNULL2006-07-27OSCON 200626Mimic NOT IN subqueryQuery result setproduct_idProduct attributesorder_idOrder attributesEfg$17.00NULLNULLSELECT .FROM products pLEFT OUTER JOIN orders o ON duct_id = duct_idWHERE duct_id IS NULL;2006-07-27O

20、SCON 200627Greatest row per groupProblem: find the row in each group with the greatest value in one columnSELECT .FROM products p JOIN orders o1 ON duct_id = duct_idLEFT OUTER JOIN orders o2 ON duct_id = duct_id AND o1.date o2.dateWHERE duct_id IS NULL;I.e., show the rows

21、 for which no other row exists with a greater date and the same product_id.2006-07-27OSCON 200628Greatest row per groupOrders o2product_idorder_iddateAbc102006/2/1Abc112006/3/10Def92005/5/2Orders o1product_idorder_iddateAbc102006/2/1Abc112006/3/10Def92005/5/2Productsproduct_idAbcDefEfgNULL2006-07-27

22、OSCON 200629Greatest row per groupQuery result setproduct_idProduct attributesorder_idOrder attributesAbc$10.00112006/3/10Def$5.0092005/5/2SELECT .FROM products p JOIN orders o1 ON duct_id = duct_idLEFT OUTER JOIN orders o2 ON duct_id = duct_id AND o1.date o2.dateWHERE du

23、ct_id IS NULL;2006-07-27OSCON 200630Top three per groupProblem: list the largest three cities per US state.SELECT c.state, c.city_name, c.populationFROM cities AS c LEFT JOIN cities AS c2 ON c.state = c2.state AND c.population = c2.populationGROUP BY c.state, c.city_name, c.populationHAVING COUNT(*)

24、 = 3ORDER BY c.state, c.population DESC;I.e., show the cities for which the number of cities with the same state and greater population is less than or equal to three.2006-07-27OSCON 200631Top three per groupCities c2statecity_namepopulationCALos Angeles3485KCASan Diego1110KCASan Jose782KCASan Franc

25、isco724KCities cstatecity_namepopulationCALos Angeles3485KCASan Diego1110KCASan Jose782KCASan Francisco724K2006-07-27OSCON 200632Top three per groupQuery result setstatecity_namepopulationCALos Angeles3485KCASan Diego1110KCASan Jose782KSELECT c.state, c.city_name, c.populationFROM cities AS c LEFT J

26、OIN cities AS c2 ON c.state = c2.state AND c.population = c2.populationGROUP BY c.state, c.city_name, c.populationHAVING COUNT(*) = 3ORDER BY c.state, c.population DESC;2006-07-27OSCON 200633Fetching EAV attributesEntity-Attribute-Value table structure for dynamic attributesNot normalized schema des

27、ignLacks integrity enforcementNot scalableNevertheless, EAV is used widely and is sometimes the only solution when attributes evolve quickly2006-07-27OSCON 200634Fetching EAV attributesAttributesproduct_idattributevalueAbcMediaDVDAbcDiscs2AbcFormatWidescreenAbcLength108 min.Productsproduct_idAbcDefE

28、fg2006-07-27OSCON 200635Fetching EAV attributesNeed an outer join per attribute:SELECT duct_id, media.value AS media, discs.value AS discs, format.value AS format, length.value AS lengthFROM products AS pLEFT OUTER JOIN attributes AS media ON duct_id = duct_id AND media.attribute

29、= MediaLEFT OUTER JOIN attributes AS discs ON duct_id = duct_id AND discs.attribute = DiscsLEFT OUTER JOIN attributes AS format ON duct_id = duct_id AND format.attribute = FormatLEFT OUTER JOIN attributes AS length ON duct_id = duct_id AND length.attribute

30、 = LengthWHERE duct_id = Abc;2006-07-27OSCON 200636Fetching EAV attributesQuery result setproduct_idmediadiscsFormatlengthAbcDVD2Widescreen108 min.SELECT duct_id, media.value AS media, discs.value AS discs, format.value AS format, length.value AS lengthFROM products AS pLEFT OUTER JOIN att

31、ributes AS media ON duct_id = duct_id AND media.attribute = MediaLEFT OUTER JOIN attributes AS discs ON duct_id = duct_id AND discs.attribute = DiscsLEFT OUTER JOIN attributes AS format ON duct_id = duct_id AND format.attribute = FormatLEFT OUTER JOIN attri

32、butes AS length ON duct_id = duct_id AND length.attribute = LengthWHERE duct_id = Abc;2006-07-27OSCON 20063772693267193167Sudoku puzzles35114768594223153698642512867597312006-07-27OSCON 200638Sudoku schemaCREATE TABLE one_to_nine ( valueINTEGER NOT NULL );INSERT INTO one_to_nine

33、(value) VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9);CREATE TABLE sudoku (column INTEGER NOT NULL,row INTEGER NOT NULL,value INTEGER NOT NULL );INSERT INTO sudoku (column, row, value) VALUES(6,1,3), (8,1,5), (9,1,1), (1,2,1), (2,2,4), (5,2,7), (7,2,6), (2,3,8), (3,3,5), (4,3,9), (7,3,4), (9,3,2

34、),(3,4,2), (4,4,3), (7,4,1), (9,4,7), (1,5,5), (2,5,3), (8,5,6), (1,6,9), (4,6,8), (5,6,6), (6,6,4), (8,6,2),(2,7,5), (4,7,1), (6,7,2), (8,7,8), (1,8,6), (3,8,7), (4,8,5), (8,8,9), (6,9,7), (7,9,3), (8,9,1);2006-07-27OSCON 200639Showing puzzle stateSELECT GROUP_CONCAT(COALESCE(s.value, _) ORDER BY x

35、.value SEPARATOR ) AS Puzzle_stateFROM one_to_nine AS x INNER JOIN one_to_nine AS y LEFT OUTER JOIN sudoku AS s ON s.column = x.value AND s.row = y.valueGROUP BY y.value;+-+| Puzzle_state |+-+| _ _ _ _ _ 3 _ 5 1 | 1 4 _ _ 7 _ 6 _ _ | _ 8 5 9 _ _ 4 _ 2 | _ _ 2 3 _ _ 1 _ 7 | 5 3 _ _ _ _ _ 6 _ | 9 _ _

36、8 6 4 _ 2 _ | _ 5 _ 1 _ 2 _ 8 _ | 6 _ 7 5 _ _ _ 9 _ | _ _ _ _ _ 7 3 1 _ |+-+2006-07-27OSCON 200640Revealing possible values SELECT x_loop.value AS x, y_loop.value AS y, GROUP_CONCAT(cell.value ORDER BY cell.value) AS possibilitiesFROM (one_to_nine AS x_loop INNER JOIN one_to_nine AS y_loop INNER JOI

37、N one_to_nine AS cell) LEFT OUTER JOIN sudoku as occupied ON (occupied.column = x_loop.value AND occupied.row = y_loop.value) LEFT OUTER JOIN sudoku as num_in_col ON (num_in_col.column = x_loop.value AND num_in_col.value = cell.value) LEFT OUTER JOIN sudoku AS num_in_row ON (num_in_row.row = y_loop.

38、value AND num_in_row.value = cell.value) LEFT OUTER JOIN sudoku AS num_in_box ON (CEIL(x_loop.value/3) = CEIL(num_in_box.column/3) AND CEIL(y_loop.value/3) = CEIL(num_in_box.row/3) AND cell.value = num_in_box.value) WHERE COALESCE(occupied.value, num_in_col.value, num_in_row.value, num_in_box.value)

39、 IS NULL GROUP BY x_loop.value, y_loop.value Is there any value already in the cell x, y ?Does the value appear in column x ?Does the value appearin row y ?Does the value appearin the sub-square containing x, y ?Select for cases where all four outer joins find no matchesCartesian product:loop x over

40、 1.9 columns, loop y over 1.9 rows, loop cell over 1.9 values2006-07-27OSCON 200641Revealing singleton values SELECT x_loop.value AS x, y_loop.value AS y, cell.value AS possibilitiesFROM (one_to_nine AS x_loop INNER JOIN one_to_nine AS y_loop INNER JOIN one_to_nine AS cell) LEFT OUTER JOIN sudoku as

41、 occupied ON (occupied.column = x_loop.value AND occupied.row = y_loop.value) LEFT OUTER JOIN sudoku as num_in_col ON (num_in_col.column = x_loop.value AND num_in_col.value = cell.value) LEFT OUTER JOIN sudoku AS num_in_row ON (num_in_row.row = y_loop.value AND num_in_row.value = cell.value) LEFT OUTER JOIN sudoku AS num_in_box ON (CEIL(x_loop.val