DB研討會 : [轉貼]MSSQL Join總結

Select * from A a, B b where a.categoryID = b.categoryID;

Select * from A a inner join B b on a.categoryID = b.categoryID;

select * from A a full(left/right) outer  join B b  on a on a.categoryID = b.categoryID;

Select * from A a, B b where a.categoryID *=  b.categoryID;Select * from A a, B b where a.categoryID =*  b.categoryID;

      select au_lname ,title from authors cross join  titiles

     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

     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

    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

  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