接
左外连接
右外连接
B.自然连接 要求列名相同、数据类型相同
SQL> select ename,dname from emp natural join dept;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
列名不相同用以下语句
SQL> select ename,dname from emp join dept on emp.deptno=dept.id;
数据类型不同，做自然连接会报错，可以修改为以下
select ename,dname from emp join dept using(deptno);
join on ,join using,natural join n-1个连接条件
full join
cross join 交叉连接
子查询
在主查询执行之前，子查询会执行一次，子查询结果被用于主查询
SQL> select ename from emp where sal=(select max(sal) from emp);
ENAME
----------
KING
SQL> select ename from emp where sal in (select sal from emp where sal>2999);
ENAME
----------
FORD
SCOTT
KING
单行子查询
多行子查询 子查询结果为空，可能导致查询结果为空需要注意
any all in
SQL> select ename,sal from emp where sal > any(2500,3000);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
SCOTT 3000
KING 5000
FORD 3000
SQL> c/any/all
1* select ename,sal from emp where sal > all(2500,3000)
SQL> /
ENAME SAL
---------- ----------
KING 5000
SQL> c/>/<
1* select ename,sal from emp where sal < all(2500,3000)
SQL> /
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
CLARK 2450
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
9 rows selected.
SQL> c/all/any
1* select ename,sal from emp where sal < any(2500,3000)
SQL> /
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
11 rows selected.
****some与any 等价****
exists
SQL> select ename from emp where empno in (select mgr from emp);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
性能低下，6次循环
SQL> select ename from emp e where exists (select 1 from emp where mgr=e.empno);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
性能高，一次全表扫描，此处的1为标签，
SQL> c/where/where not
1* select ename from emp e where not exists (select 1 from emp where mgr=e.empno)
SQL> /
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
set操作
交并补
union 去除重复行,排序,升序,union all 不去除重复行,不排序
intersect 交集,没有重复
minus 补集A-B
表达式在数量上匹配，数据类型前后必须一致
括号可变优先级，order by 语句可以出现在语句最后
SQL> select deptno,null job,max(sal) from emp group by deptno
2 union all
3 select null ,job,max(sal) from emp group by job;
DEPTNO JOB MAX(SAL)
---------- --------- ----------
30 2850
20 3000
10 5000
CLERK 1300
SALESMAN 1600
PRESIDENT 5000
MANAGER 2975
ANALYST 3000
以上是构造伪列
以下是10g以后才会支持的
SQL> c/setss/sets
1* select deptno,job,max(sal) from emp group by grouping sets(deptno,job)
SQL> /
DEPTNO JOB MAX(SAL)
---------- --------- ----------
CLERK 1300
SALESMAN 1600
PRESIDENT 5000
MANAGER 2975
ANALYST 3000
30 2850
20 3000
10 5000
|