数据库07笔记（92语法和99语法）

--99语法
--实现笛卡尔积效果   
--92写法
select * from emp ,dept;
--99写法   交叉连接 cross join
select * from emp cross join dept;

--等值连接
--自然连接  natural join  内部自动查找同名字段|主外键关系 自动实现等值连
--不能指定限定词
select deptno from emp natural join dept;

--using 连接  指定字段等值连接
select * from emp  join dept using(deptno);

--非等值连接|等值连接  join  ..on..   (Inner join内连接)
select * from emp join dept on emp.deptno = dept.deptno;

--查询工资答应1500的用户的信息和薪资等级
select * from emp e join salgrade s on e.sal between s.losal and s.hisal where sal>1500;

--查询10和30部门的员工的名称,部门名称,薪资,薪资等级,上级名称 
select e.ename, d.dname, e.deptno, e.sal, s.grade, m.ename
  from emp e
  join emp m
    on e.mgr = m.empno
  join salgrade s
    on e.sal between s.losal and s.hisal
  join dept d
    on e.deptno = d.deptno
 where deptno in (10, 30);
 
--外连接
--左外连接   left join .. on..
select * from emp e1 left join emp e2 on e1.mgr = e2.empno;

--右外连接   right join .. on..
select * from emp e2 right join emp e1 on e1.mgr = e2.empno;
    


select 1 no, 'a' "name"
  from dual
union
select 2 no, 'b' "name"
  from dual;

select 1 no, 'c' "name" from dual union select 3 no, 'd' "name" from dual;

--等值连接
select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) a
  join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) b
    on a.no = b.no;
--左链接
select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) a
  left join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) b
    on a.no = b.no;
--右连接
select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) a
  right join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) b
    on a.no = b.no;
--全连接  两个表都作为主表
select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) a
  full join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) b
    on a.no = b.no;