jointable

1 Jioning Tables Using Oracle Syntax
Using a join to query data form more than one table
select table1.column,table2,column
from table1,table2
where table1.column1=table2.column2 .
2 outjoin
 1)You use an outer join to also see rows that do not meet the join condition
 2)The Outer join operator is the plus sign(+)
   a left join
     select tabl1.column,table2,column
     from table1,table2
     where table1.column(+)=table2.column
  b  right join
     select table1.column,table2.column
     from table1,table2
     wheretable1.coulmn=table2.column(+)
  3) self join
     select worker.last_name||'works for'||manager.last_name
     from  employees owrker,employees manager
     where worker.manager_id=manager.employee_id;
 
3 Joining Tables Using SQL:1999 Syntax
  Use a join to query data from more than one table
  1) Creationg Cross Joins
   a The cross join clause produces thee cross product of two tables
   b This is the same as Cartesian product between the two tables
   select last_name,department_name
   from employees
   ccross join departments
  2) Creating Natual Joins
   a The Natual join clause is bassed on all columns in the two tables that have the same name
   b it select rows from the two tables that have the equal values in all matched columns
   c if the columns having the same name and have the different data types in an error is returned.
   select department_id,department_name,location_id,city
   from departments
   natual join locations
 3) using clause
   select e.employee_id,e.last_name
   from employees e join departments d
   using (department_id);
 4) Creating joins with thee on clause
  a The join condition for thee natual join is basically an equaljoin of all column with the same name.
  b To specify arbitrary condition or specify columns to join, the on clause is userd
  c The join condition is separated from other search conditions
  d The on claus make code easy to understand.
  select e.employee_id,e.last_name,e.department_id,
  from employees e join departments d
  on (e.department_id=d.department_id);
 
  from employe
  join departments d
  on d.department_id=e.department_id
  join locations l
  on d.location_id=l.location_id
 5) INNER Versus OuTER Joins
  a In SQL:1999,the join of two tables returning only matched rows is an inner join
 6) FULL OUTER JOIN
  select e.last_name,e,department_id,d.department_name
  from employees e
  full outer join departments d
  on (e.department_id=d.department_id);
 

posted on 2006-09-22 14:55 康文 阅读(356) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2006年9月>
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

搜索

最新评论

阅读排行榜

评论排行榜