Oracle 多表查询(外连接，左连接，右连接，any，all等)

多表查询

• 创建表数据
  • student(学生表)
    id name age sex class_id
    1 xiaoming 12 男 1
    2 xiaozhang 13 (null) (null)
    3 zhangsan 14 男 2
  • class(班级表)
    id name
    1 1班
    2 2班
    3 3班
• 笛卡尔积，在多表查询的时候，如果不带任何条件，则会出现笛卡尔积，怎么避免：多表查询的条件至少不能少于表的个数减1
  • 通过

    select * from student,class
    

    • dept表的每一条记录，都会与emp表的每一条记录进行匹配
  • 执行结果
    id name age sex class_id id name
    1 xiaoming 12 男 1 1 1班
    1 xiaoming 12 男 1 2 2班
    1 xiaoming 12 男 1 3 3班
    2 xiaozhang 13 (null) (null) 1 1班
    2 xiaozhang 13 (null) (null) 2 2班
    2 xiaozhang 13 (null) (null) 3 3班
    3 zhangsan 14 男 2 1 1班
    3 zhangsan 14 男 2 2 2班
    3 zhangsan 14 男 2 3 3班
• 自连接
  • 自连接,把student表看做两张表，一张是s1表，一张是s2表
    • 通过

      select * from student s1,student s2 where s1.class_id=s2.class_id
      

    • 执行结果
      id name age sex class_id id name age sex class_id
      1 xiaoming 12 男 1 1 xiaoming 12 男 1
      3 zhangsan 14 男 2 3 zhangsan 14 男 2
• 左外连接
  • 通过

    select * from student s,class c where s.class_id=c.id(+)
    -- 或者
    select * from student s left join class c on s.class_id=c.id
    -- 或者
    select * from student s left outer join class c on s.class_id=c.id
    

  • 执行结果
    id name age sex class_id id name
    1 xiaoming 12 男 1 1 1班
    3 zhangsan 14 男 2 2 2班
    2 xiaozhang 13 (null) (null) (null) (null)
• 右外连接
  • 通过

    select * from student s,class c where s.class_id(+)=c.id
    -- 或者
    select * from student s right join class c on s.class_id=c.id
    -- 或者
    select * from student s right outer join class c on s.class_id=c.id
    

  • 执行结果
    id name age sex class_id id name
    1 xiaoming 12 男 1 1 1班
    3 zhangsan 14 男 2 2 2班
    (null) (null) (null) (null) (null) 3 3班
• 全外连接（FULL OUTER JOIN/FULL JOIN）
  • 左表和右表都不做限制，所有的记录都显示，两表不足的地方均为NULL。 全外连接不支持（+）写法。
    • 通过

      select * from student s full join class c on s.class_id=c.id
      -- 或者
      select * from student s full outer join class c on s.class_id=c.id
      

    • 执行结果
      id name age sex class_id id name
      1 xiaoming 12 男 1 1 1班
      3 zhangsan 14 男 2 2 2班
      (null) (null) (null) (null) (null) 3 3班
      2 xiaozhang 13 (null) (null) (null) (null)
• all操作符的使用
  • 插入数据

    insert into student values(4,'huahua',12,'女',3)
    

  • 查询student表

    select *  from student
    

    • 结果
      id name age sex class_id
      1 xiaoming 12 男 1
      2 xiaozhang 13 (null) (null)
      3 zhangsan 14 男 2
      4 huahua 12 女 3
  • all操作，通过

    select * from student where class_id > all(select class_id from student where sex='男')
    

  • 执行结果
    id name age sex class_id
    4 huahua 12 女 3
• any操作符的使用
  • 通过

    select * from student where class_id > any(select class_id from student where age=12)
    

  • 执行结果
    id name age sex class_id
    4 huahua 12 女 3
    3 zhangsan 14 男 2
• 多列子查询：
  • 查询性别为女的年龄和班级ID完全相同的所有学员
    • 通过

      select * from student where (age,class_id) = (select age,class_id from student where sex='女')
      

    • 执行结果
      id name age sex class_id
      4 huahua 12 女 3
• 分页查询：
  • 从第2条开始取，到第4条结束(不包含第4条)
    • 通过

      select t2.* from (select t1.*,rownum rn from (select *  from student) t1 where rownum<4) t2 where rn>1
      

    • 执行结果
      id name age sex class_id
      2 xiaozhang 13 (null) (null) 2
      3 zhangsan 14 男 2 3
• 合并查询
  • union取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复行。
  • union all 与union相似，但不会取消重复行，而且不会排序
  • intersect取得交集
  • minus取得差集，显示存在第一个集合中的数据，不显示第二个集合中的数据。