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

$sql = "SELECT t1.t_name, count(t2.uid) as aa 
FROM " . $xoopsDB->prefix('teacher') . " as t1 
LEFT OUTER JOIN " . $xoopsDB->prefix('student') . " as t2 
on t1.tid = t2.st_teacher 
group by t1.t_name";

Inner Join
Natural Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join

SELECT a.*, b.* FROM `test1` AS a, `test2` AS b WHERE a.id = b.id

SELECT a.*, b.* FROM `test1` AS a NATURAL JOIN `test2` AS b

SELECT a.*, b.* FROM `test1` AS a LEFT JOIN `test2` AS b ON a.id = b.id

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other).
In standard SQL, they are not equivalent.
INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

SELECT ...
FROM table 1
LEFT JOIN table 2 on (table1.id = table2.id)
LEFT JOIN table 3 on (table1.id2 = table3.id2)
LEFT JOIN table 4 on (table1.id3 = table4.id3)

SELECT d.degDegId, m1.majDescription AS major_1, m2.majDescription AS major_2
FROM degree AS d
LEFT OUTER JOIN major AS m1
ON d.degMajor1 = m1.majMajId
LEFT OUTER JOIN major AS m2
ON d.degMajor2 = m2.majMajId

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id

SELECT *
FROM A JOIN B ON A.id = B.id
UNION ALL
SELECT *
FROM A LEFT JOIN B ON A.id = B.id
WHERE B.id IS NULL
UNION ALL
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL

SELECT *
FROM A LEFT JOIN B ON A.id = B.id
UNION ALL
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL

create table tom1(
  aa number
, bb varchar2(100)
);

create table tom2(
  aa number
, cc varchar2(100)
);

create table tom3(
  cc varchar2(100)
, dd varchar2(100)
);

-- 建立 Temp Data
insert into tom1 values( 1, 'A' );
insert into tom1 values( 2, 'B' );
insert into tom1 values( 3, 'C' );
insert into tom1 values( 4, 'D' );
insert into tom1 values( 5, 'E' );

insert into tom2 values( 1, 'I' );
insert into tom2 values( 3, 'J' );
insert into tom2 values( 5, 'K' );

insert into tom3 values( 'J', 'JJJ' );
insert into tom3 values( 'K', 'KKK' );

commit;

select t1.aa
     , t1.bb
     , t2.cc
     , t3.dd
  from tom1 t1
     , tom2 t2
     , tom3 t3
 where t1.aa = t2.aa
   and t2.cc = t3.cc(+)  -- (+) 放置在可能沒有資料的 Table 一方
 order by t1.aa;

AA     BB     CC     DD
------ ------ ------ ------
1      A      I      (null)
3      C      J      JJJ
5      E      K      KKK

select t1.aa
     , t1.bb
     , t2.cc
     , t3.dd
  from tom1 t1
     , tom2 t2
     , tom3 t3
 where t1.aa = t2.aa(+)  -- (+) 放置在可能沒有資料的 Table 一方
   and t2.cc = t3.cc(+)  -- (+) 放置在可能沒有資料的 Table 一方
 order by t1.aa;

AA     BB     CC     DD
------ ------ ------ ------
1      A      I      (null)
2      B      (null) (null)
3      C      J      JJJ
4      D      (null) (null)
5      E      K      KKK