oracle初识9.23-表连接

select e.last_name,d.department_name
from employees e,departments d
where last_name='Grant'and e.department_id = d.department_id
等价于：
select e.last_name,d.department_name
from employees e join departments d on e.department_id=d.department_id
where last_name='Grant'

 

Lesson 7
   
    Natural joins(等值连接)
        -NATURAL JOIN 要求连接的两列数据类型相同(等值连接)；不用指定列，自动匹配名字内容相同的类；
        -USING clause using 加的列的名字不能在其他子句中限定条件；
        -ON clause on指定条件;(相当于where);
   
Lesson 8
    非等值连接
        select e.ename,s.grade
        from emp e,salgrade s
        where e.sal between s.losal and s.hisal and e.ename='SMITH'
  
      Outer joins
        LEFT OUTER JOIN(只显示左表中不匹配的记录)
         例: select e.last_name,e.department_id,d.department_id
            from employees e LEFT [OUTER] JOIN departments d
            on (e.department_id = d.department_id)
            (把e中不匹配的显示出来)
        (oracle语法)
            select e.last_name,d.department_id,d.department_id
            from employees e LEFT OUTER JOIN departments d
            where e.department_id = d.department_id(+)

        RIGHT OUTER JOIN(只显示右表中不匹配的记录)
            select e.last_name,d.department_id,d.department_id
            from employees e RIGHT [OUTER] JOIN departments d
            on (e.department_id = d.department_id)
            (把d中不匹配的显示出来)
        (oracle语法)
           select e.last_name,e.department_id,d.department_id
           from employees e LEFT OUTER JOIN departments d
           where e.department_id(+) = d.department_id

        FULL OUTER JOIN(左右两个表都显示)
            select e.last_name,d.department_id,d.department_id
            from employees e FULL [OUTER] JOIN departments d
            on (e.department_id = d.department_id)
       Cartesian product(笛卡尔集)做测试数据用；
         Cross join(当没有连接条件时，或一个无效的连接条件)
           select last_name,department_name
           from employees
           cross join departments(输出结果为第一个表的行数*第二个表的行数)