SQLjoin详细用法.docVIP

SQL join详细用法下面为join用法,但self join 好象写的有问题. 【 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_date1996/10/15)???? 可以写为???? select a.cust_id,b.order_date,b.tot_ant???? from custom a ???? left join (select * from sales where order_date1996/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_nameb.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.ida.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.ida.id and ic=c) as newid,* from?aa a where ic=c) bbbon aaa.newid=bbb.newidorder by aaa.name6.使用 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 pubsSELECT SUBSTRING((RTRIM(a.au_fname) + + LTRIM(a.