oracle 学习笔记四

SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 
  ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 
  ON (table1.column_name = table2.column_name)];




交叉连接  CROSS JOIN
    oracle 8i中
      select * from employees,departments;
    oracle 9i中
      select * from employees CROSS JOIN departments;


自然连接 NATURAL JOIN
      oracle 8i
       select * from emp e,dept d where e.deptno=d.deptno;
       oracle 9i
       select * from emp  NATURAL JOIN dept;
       
       select * from emp join dept using (deptno);
on子名
   sql-1999
   select * from emp;
   select * from dept;
   select e.ename,e.empno,d.dname,d.deptno
          from emp e join dept d on(e.deptno=d.deptno);
          
三个表的内连接
select * from employees;
select * from location;
select * from departments;
 oracle8i等值接连
        select e.employee_id,e.first_name||e.last_name,d.department_id,d.department_name,l.loc,l.city
               from employees e,departments d,location l where e.department_id=d.department_id and d.location_id=l.location_id;
 sql-1999
        select e.employee_id,e.first_name||e.last_name,d.department_id,d.department_name,l.loc,l.city
               from employees e join departments d on(e.department_id=d.department_id) join location l on(d.location_id=l.location_id);
  


左外连接 LEFT OUTER JOIN 对连接条件中左边的表中的记录不加限制  
         oracle8i右外接连
               select e.employee_id,e.first_name||e.last_name,d.department_id,d.department_name
                from employees e, departments d where e.department_id=d.department_id(+);  
         sql-1999
                 select e.employee_id,e.first_name||e.last_name,d.department_id,d.department_name
                  from employees e left outer join departments d on(e.department_id=d.department_id);
                  
右外连接 RIGHT OUTER JOIN 对连接条件中右边的表中的记录不加限制  
         oracle8i左外接连
               select e.employee_id,e.first_name||e.last_name,d.department_id,d.department_name
                from employees e, departments d where e.department_id(+)=d.department_id;  
         sql-1999
                 select e.employee_id,e.first_name||e.last_name,d.department_id,d.department_name
                  from employees e right outer join departments d on(e.department_id=d.department_id);
          
全外连接 Full outer Join  全外连接是对两个表中的记录都不加限制。
         select e.employee_id,e.first_name||e.last_name,d.department_id,d.department_name
                  from employees e full outer join departments d on(e.department_id=d.department_id);