多表连接

多表连接

select table1.aaa,table2.bbb from table1

–cross join table2

–natural join table2

–join table2 using (xxx)

–join table on table1.table1_id = table2.table2 _id

–left/right/full (outer) join table2 on table1.table1_id = table2.table2 _id

  • 内连接(等值连接)

    
    select e.last_name,d.department_name from employees e,departments d where 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
  • 外连接 left/right join 表名 on

    left join 表名 on
    
    
    select e.last_name,d.department_name from employees e left join departments d on e.department_id=d.department_id
    =
    
    使用(+), 带(+)的为补充表
    select e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id(+)
  • 自连接

    select worker.last_name ||' works for '|| manager.last_name from employees worker,employees manager where worker.manager_id = manager.employee_id
  • 自然连接 natural 表名 join

    以两个表中名字相同的列为条件创建等值连接

    如果有相同名字的列数据类型不同,返回一个错误

    
    select * from employees natural join departments   
    =
    
    select * from employees e,departments d where e.department_id=d.department_id and d.manager_id=e.manager_id
    
    using指定等值连接用到的列   join 表名 usingnatural joinusing 是互相排斥的
    在引用列不要使用表名或别名
    
    select * from employees join departments using (department_id) 
    =
    
    select * from employees e,departments d where e.department_id=d.department_id
    
    select * from employees join departments using (department_id) where department_id = 10
    
    select * from employees join departments d using (department_id) where d.department_id = 10 --错误的
  • 交叉连接 cross 表名 join

    两个表的交叉乘积,该连接和两个表之间的笛卡尔乘积是一样的

    
    select last_name,department_name from employees cross join departments
    =
    
    select last_name,department_name from employees,departments
  • 全外连接 full join 表名 on

    返回两个表中的行,即使不匹配的行也会返回

    select * from employees e full join departments d on e.department_id=d.department_id

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/Christin_z/article/details/77969684
文章标签: oracle数据库
个人分类: oracle数据库
想对作者说点什么? 我来说一句

oracle多表连接技巧

2009年03月04日 550KB 下载

oracle sql 多表连接和子查询

2011年12月18日 339KB 下载

多表连接的多种方式.sql

2013年02月20日 2KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭