<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    石建 | Fat Mind

    sql連接查詢

    請參考:http://en.wikipedia.org/wiki/Join_(SQL)#Sample_tables

    inner JOINS

      An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate. Actual SQL implementations normally use other approaches like a Hash join or a Sort-merge join where possible, since computing the Cartesian product is very inefficient.

      注意:innner查詢(默認的連接查詢方式),是先查詢“Cartesian”生成中間表,再根據where條件篩選結果;但此方法非常低效,SQL具體的實現可能是 
    Hash join or a Sort-merge join 。
            
    One can further classify inner joins as equi-joins, as natural joins, or as cross-joins.

    SELECT *
    FROM employee INNER JOIN department
    ON employee.DepartmentID = department.DepartmentID;
    The following example shows a query which is equivalent to the one from the previous example.
    
    
    SELECT *
    FROM   employee, department
    WHERE  employee.DepartmentID = department.DepartmentID;
    

    Outer joins

      An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

    Example of a left outer join, with the additional result row italicized:

    SELECT *
    FROM   employee  LEFT OUTER JOIN department
    ON employee.DepartmentID = department.DepartmentID;
    
    Employee.LastNameEmployee.DepartmentIDDepartment.DepartmentNameDepartment.DepartmentID
    Jones 33 Engineering 33
    Rafferty 31 Sales 31
    Robinson 34 Clerical 34
    Smith 34 Clerical 34
    John NULL NULL NULL
    Steinberg 33 Engineering 33


    Example right outer join, with the additional result row italicized:

    SELECT *
    FROM   employee RIGHT OUTER JOIN department
    ON employee.DepartmentID = department.DepartmentID;
    
    Employee.LastNameEmployee.DepartmentIDDepartment.DepartmentNameDepartment.DepartmentID
    Smith 34 Clerical 34
    Jones 33 Engineering 33
    Robinson 34 Clerical 34
    Steinberg 33 Engineering 33
    Rafferty 31 Sales 31
    NULL NULL Marketing 35


    Example full outer join: (mysql is not support)

    SELECT *
    FROM   employee
    FULL OUTER JOIN department
    ON employee.DepartmentID = department.DepartmentID;
    
    Employee.LastNameEmployee.DepartmentIDDepartment.DepartmentNameDepartment.DepartmentID
    Smith 34 Clerical 34
    Jones 33 Engineering 33
    Robinson 34 Clerical 34
    John NULL NULL NULL
    Steinberg 33 Engineering 33
    Rafferty 31 Sales 31
    NULL NULL Marketing 35


    Self-join

    A query to find all pairings of two employees in the same country is desired.

    An example solution query could be as follows:

    SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
    FROM Employee F, Employee S
    WHERE F.Country = S.Country
    AND F.EmployeeID < S.EmployeeID
    ORDER BY F.EmployeeID, S.EmployeeID;
    

    Which results in the following table being generated.

    Employee Table after Self-join by Country
    EmployeeIDLastNameEmployeeIDLastNameCountry
    123 Rafferty 124 Jones Australia
    123 Rafferty 145 Steinberg Australia
    124 Jones 145 Steinberg Australia
    305 Smith 306 John Germany










    Join algorithms

    Three fundamental algorithms exist for performing a join operation: Nested loop joinSort-merge join and Hash join.




     

    posted on 2010-11-03 15:36 石建 | Fat Mind 閱讀(297) 評論(0)  編輯  收藏 所屬分類: database


    只有注冊用戶登錄后才能發表評論。


    網站導航:
     

    導航

    <2010年11月>
    31123456
    78910111213
    14151617181920
    21222324252627
    2829301234
    567891011

    統計

    常用鏈接

    留言簿

    隨筆分類

    隨筆檔案

    搜索

    最新評論

    What 、How、Why,從細節中尋找不斷的成長點
    主站蜘蛛池模板: 水蜜桃视频在线观看免费播放高清| 亚洲色大情网站www| 国产福利免费视频| 亚洲AV无码专区日韩| 免费国产在线精品一区| 免费人成视频在线观看不卡| 美女尿口扒开图片免费| 亚洲 无码 在线 专区| 日韩a毛片免费观看| 国产自偷亚洲精品页65页| 最近中文字幕免费大全| 亚洲成Av人片乱码色午夜| 特级无码毛片免费视频尤物| 久久亚洲AV成人无码电影| 亚洲一级免费视频| 亚洲私人无码综合久久网| 日本成人免费在线| 一级毛片在线完整免费观看| 亚洲尤码不卡AV麻豆| 国产精品99久久免费观看| 亚洲免费在线观看视频| 免费看又爽又黄禁片视频1000 | 久久久久亚洲Av无码专| 3d动漫精品啪啪一区二区免费 | 亚洲国产精品VA在线看黑人| 免费毛片a线观看| 亚洲国产精品成人精品软件| 国产精品成人四虎免费视频| 久久久久国色AV免费观看| 亚洲国产精品高清久久久| 三年片在线观看免费观看高清电影 | 亚洲一区日韩高清中文字幕亚洲 | 亚洲人成网站看在线播放| 午夜高清免费在线观看| 在线视频网址免费播放| 亚洲网站在线播放| 免费国产成人午夜私人影视| 青青青国产手机频在线免费观看| 亚洲 欧洲 视频 伦小说| 亚洲一级特黄大片在线观看| 国产免费一区二区三区|