MSSQL Join总结

SQL 之JOIN 用法完全版
一、各种JOIN的含义
SQL中大概有这么几种JOIN：
cross join
inner join
left outer join   
right outer join  
full outer join 
 
首先都是基于cross join(笛卡尔乘积），然后是inner join，在笛卡尔乘积的结果集中去掉不符合连接条件的行。
left outer join 是在inner join的结果集上加上左表中没被选上的行，行的右表部分每个字段都用NUll填充。
right outer join 是在inner join的结果集上加上右表中没被选上的行，行的左表部分全用NULL填充。
outer的意思就是"没有关联上的行"。
 
二、旧式写法和标准写法：
 
1、INNER Join code as the following:
 
Select * from A a, B b where a.categoryID = b.categoryID;
Equals:
Select * from A a inner join B b on a.categoryID = b.categoryID;
 
2、OUTER Join code as the following
 
select * from A a full(left/right) outer  join B b  on a on a.categoryID = b.categoryID;
Equals::
Select * from A a, B b where a.categoryID *=  b.categoryID;
Select * from A a, B b where a.categoryID =*  b.categoryID;
 
三、例子
Table A have 12( 8+4) entries, 8 entries have valid relation with B
Table B have 80(77+3) entries , 77 entries have valid relation with A.
 
then the return amount of join is :
cross join : 12*80
inner join : 77
full outer join : 77+4+3
left outer join: 77 + 4
right outrer join: 77 + 3

 

 

join 方式
（1) cross join
      参与select语句所有表的的所有行的笛卡尔乘积
      select au_lname ,title from authors cross join  titiles
      outer join 对参与join的两个表有主从之分，处理方式以主表的每条数据去match 从属表的列，合乎条件的数据是我们所要的答案，不合乎条件的也是我们要的答案，只不过哪些从属表选取的列将被添上null。
(2) left join
     左边的为主表，右边为从属表
     select a.cust_id ,b.order_date,b.tot_ant from customer a left join sales b
       on (a.cust_id =b.cust_id and b.order_date>''1996/10/15'')
     可以写为
     select a.cust_id,b.order_date,b.tot_ant from custom a
        left join (select * from sales where order_date>''1996/10/15'') b
        on a.cust_id =b.cust_id  
(3) right join
     左边的表为从属表，右边的表为主表
(4) self join
     self join 常用在同一表内不同数据间对同一列的比较
     select a.emp_no,a.emp_name,b.emp_no,b.emp_name,a.date_hired from employee a
        join employee b on (a.emp_no!=b.emp_no and a.date_hired=b.date_hired)
            order by a.date_hired
     这样会重复数据，只要加上一句 and a.emp_name>b.emp_name
(5) full join
     不仅列出符合条件的数据，两边未符合join条件的数据也会一并列出。哪些未符合join条件的数据如果在select列中无法得到对应的值则填上null
    select a.cust_id,b.tot_amt from customer a full join sales b
         on a.cust_id=b.cust_id
    有表
     id   ic  name  amount
      I    *        *
      c
      i
      c
      i
      i  
   要求结果为 
    ic  name  amount  ic   name  amount
    i                         c
    i                         c
    i
    i
  select aaa.*,bbb.*  from (select (select count(id) from aa as b where (b.id<a.id) and (ic=''i'')) as newid, *
        from aa a where ic=''i'') aaa full join
         (select (select count(id) from aa as b where b.id<a.id and ic=''c'') as newid,* from
             aa a where ic=''c'') bbb on aaa.newid=bbb.newid order by aaa.name 
 6.使用 HASH 和 MERGE 联接提示
   此示例在 authors、titleauthors 和 titles 表之间建立三表联接，以生成一个作者及其著作的列表。查询优化器使用 MERGE 联接将 authors 和 titleauthors (A x TA) 联接在一起。然后，将 authors 和 titleauthors MERGE 联接 (A x TA) 的结果与 titles 表进行 HASH 联结以生成 (A x TA) x T。
 重要  指定联接提示后，要执行 INNER JOIN 时 INNER 关键字不再为可选，而必须显式说明。
  USE pubs
  SELECT SUBSTRING((RTRIM(a.au_fname) + '' '' + LTRIM(a.au_lname)), 1, 25)
     AS Name, SUBSTRING(t.title, 1, 20) AS Title
     FROM authors a INNER MERGE JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER HASH JOIN titles t
      ON t.title_id = ta.title_id
     ORDER BY au_lname ASC, au_fname ASC

  下面是结果集：

  Warning: The join order has been enforced because a local join hint is used.
  Name                      Title               
  ------------------------- --------------------
  Abraham Bennet            The Busy Executive''s
  Reginald Blotchet-Halls   Fifty Years in Bucki
  Cheryl Carson             But Is It User Frien
  Michel DeFrance           The Gourmet Microwav
  Innes del Castillo        Silicon Valley Gastr
  ...                    ...
  Johnson White             Prolonged Data Depri
  Akiko Yokomoto            Sushi, Anyone?      

  (25 row(s) affected)

 

                      dbo.CheckPerson.checkPersonID = dbo.Employee.employeeID INNER JOIN
                      dbo.Employee AS Employee_1 ON dbo.RequirePlan.establishPersonID = Employee_1.employeeID AND
                      dbo.CheckPerson.checkPersonID = Employee_1.employeeID

Employee_1前边没有dbo.