目录
概述
内连接：INNER JOIN
外连接：OUTER JOIN
1、全连接 FULL OUT JOIN
全连接：
2、左外连接LEFT OUT JOIN
左外连接：
3、右外连接：RIGHT JOIN
右外连接
笛卡尔积：CROSS JOIN
自然连接：NATURAL JOIN
SEMI JOIN
ANTI JOIN
自连接：SELF JOIN
概述
Oracle中表之间的连接（JOIN）有很多种，当然也不仅限于表与表，还可以表与视图、物化视图等联结，以下是官方解释
A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
JOIN有多种方式，分类如下图所示
内连接：INNER JOIN
内连接又称简单连接或等值连接
例如想知道每位员工所在部门的名称
查看员工表emp和部门表dept
| SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- ---------- ----- ------------------- ----- ----- ------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
------ --------------- ---------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON |
实现方式有三种
第一种
| SQL> select e.empno,e.ename,e.deptno,d.dname from emp e INNER JOIN dept d on e.deptno = d.deptno;
EMPNO ENAME DEPTNO DNAME
----- ------- ------ ---------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected. |
第二种
| SQL> select e.empno,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
EMPNO ENAME DEPTNO DNAME
----- ------- ------ ---------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected. |
第三种 是用USING，但是不常用
| SQL> select e.empno,e.ename,e.deptno,d.dname
from emp e
INNER JOIN dept d
USING(deptno);
select e.empno,e.ename,e.deptno,d.dname from emp e INNER JOIN dept d USING(deptno)
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier |
怎么报错了？
在 Oracle 里用 JOIN USING 或 NATURAL JOIN 时，如果两表共有的列的名称前加上某表名作为前缀，则会报编译错误: "ORA-25154: column part of USING clause cannot have qualifier" 或 "ORA-25155: column used in NATURAL join cannot have qualifier".
把共有的列名称的前缀去掉即可
| SQL> select e.empno,e.ename,deptno,d.dname
from emp e
INNER JOIN dept d
USING(deptno);
EMPNO ENAME DEPTNO DNAME
----- ------- ------ ---------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected. |
INNER JOIN也可以简写成JOIN，但是建议不使用，以免造成混乱
| SQL>
select e.empno,e.ename,e.deptno,d.dname
from emp e
JOIN dept d on e.deptno = d.deptno;
EMPNO ENAME DEPTNO DNAME
----- ------- ------ ---------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected.
SQL>
select e.empno,e.ename,deptno,d.dname
from emp e
JOIN dept d
USING(deptno);
EMPNO ENAME DEPTNO DNAME
----- ------- ------ ---------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected. |
用韦恩图表示INNER JOIN大概是这样的
http://pafumi.net/SQL_Joins.html
外连接：OUTER JOIN
1、全连接 FULL OUT JOIN
全连接：
包括左、右表的全部行，无论另外一边的表中是否存在与它们匹配的行。不符合条件的，均空值代替。
参照 emp 创建员工表 t1emp，参照 dept 创建部门表 t1dept，并修改测试数据
| SQL> show user
USER is "SCOTT"
SQL> create table t1emp as select * from emp;
Table created.
SQL> create table t1dept as select * from dept;
Table created.
SQL> update t1emp set deptno=99 where empno in(7369,7499,7782);
3 rows updated.
SQL> select * from t1emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- ---------- ----- ------------------- ----- ----- ------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 99
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 99
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 99
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SQL> select * from t1dept;
DEPTNO DNAME LOC
------ --------------- ---------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON |
| SQL>
select e.empno,e.ename,e.deptno,d.deptno,d.dname
from t1emp e
FULL OUTER JOIN t1dept d
ON e.deptno = d.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME
----- ------- ------ ------ ---------------
7369 SMITH 99
7499 ALLEN 99
7521 WARD 30 30 SALES
7566 JONES 20 20 RESEARCH
7654 MARTIN 30 30 SALES
7698 BLAKE 30 30 SALES
7782 CLARK 99 7788 SCOTT 20 20 RESEARCH
7839 KING 10 10 ACCOUNTING
7844 TURNER 30 30 SALES
7876 ADAMS 20 20 RESEARCH
7900 JAMES 30 30 SALES
7902 FORD 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
40 OPERATIONS
15 rows selected. |
用韦恩图表示INNER JOIN大概是这样的
2、左外连接LEFT OUT JOIN
左外连接：
又叫左连接，包含左边表所有记录，右边所有的匹配的记录，没有则用NULL补齐。也就是说列出左边表全部的，及右边表符合条件的，不符合条件的以空值代替
| SQL> select e.empno,e.ename,e.deptno,d.deptno,d.dname
from t1emp e
LEFT OUTER JOIN t1dept d
ON e.deptno = d.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME
----- ------- ------ ------ ---------------
7934 MILLER 10 10 ACCOUNTING
7839 KING 10 10 ACCOUNTING
7902 FORD 20 20 RESEARCH
7876 ADAMS 20 20 RESEARCH
7788 SCOTT 20 20 RESEARCH
7566 JONES 20 20 RESEARCH
7900 JAMES 30 30 SALES
7844 TURNER 30 30 SALES
7698 BLAKE 30 30 SALES
7654 MARTIN 30 30 SALES
7521 WARD 30 30 SALES
7782 CLARK 99
7499 ALLEN 99
7369 SMITH 99
14 rows selected.
SQL> select e.empno,e.ename,e.deptno,d.deptno,d.dname
from t1emp e
LEFT JOIN t1dept d
ON e.deptno = d.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME
----- ------- ------ ------ ---------------
7934 MILLER 10 10 ACCOUNTING
7839 KING 10 10 ACCOUNTING
7902 FORD 20 20 RESEARCH
7876 ADAMS 20 20 RESEARCH
7788 SCOTT 20 20 RESEARCH
7566 JONES 20 20 RESEARCH
7900 JAMES 30 30 SALES
7844 TURNER 30 30 SALES
7698 BLAKE 30 30 SALES
7654 MARTIN 30 30 SALES
7521 WARD 30 30 SALES
7782 CLARK 99
7499 ALLEN 99
7369 SMITH 99
14 rows selected. |
在ORACLE 9i之前，使用在(+)来表示左连接，哪个带(+)哪个需要条件符合的，另一个全部的。即放左表示右连接，放右表示左连接
| SQL> select e.empno,e.ename,e.deptno,d.deptno,d.dname from t1emp e,t1dept d where e.deptno=d.deptno(+);
EMPNO ENAME DEPTNO DEPTNO DNAME
----- ------- ------ ------ ---------------
7934 MILLER 10 10 ACCOUNTING
7839 KING 10 10 ACCOUNTING
7902 FORD 20 20 RESEARCH
7876 ADAMS 20 20 RESEARCH
7788 SCOTT 20 20 RESEARCH
7566 JONES 20 20 RESEARCH
7900 JAMES 30 30 SALES
7844 TURNER 30 30 SALES
7698 BLAKE 30 30 SALES
7654 MARTIN 30 30 SALES
7521 WARD 30 30 SALES
7782 CLARK 99
7499 ALLEN 99
7369 SMITH 99
14 rows selected.
SQL> select e.empno,e.ename,e.deptno,d.deptno,d.dname from t1emp e,t1dept d where d.deptno(+)=e.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME
----- ------- ------ ------ ---------------
7934 MILLER 10 10 ACCOUNTING
7839 KING 10 10 ACCOUNTING
7902 FORD 20 20 RESEARCH
7876 ADAMS 20 20 RESEARCH
7788 SCOTT 20 20 RESEARCH
7566 JONES 20 20 RESEARCH
7900 JAMES 30 30 SALES
7844 TURNER 30 30 SALES
7698 BLAKE 30 30 SALES
7654 MARTIN 30 30 SALES
7521 WARD 30 30 SALES
7782 CLARK 99
7499 ALLEN 99
7369 SMITH 99
14 rows selected. |
结论:1.加号(+)在右表，就属于左连接，以左表数据为基准(注:左右表的区分要看在 from 中的位 置); 2.由于(+)的位置容易混淆连接的方式，所以工作中在 oracle 的环境中多采用
sql-99 的书写方式 (left [outer] join)，便于识别连接方式
用韦恩图表示INNER JOIN大概是这样的
3、右外连接：RIGHT JOIN
右外连接
又叫右连接： 包含右边表所有记录，左边所有的匹配的记录，没有则用NULL补齐，也就是说，与左连接一样，列出右边表全部的，及左边表符合条件的，不符合条件的用空值代替
| SQL> select e.empno,e.ename,e.deptno,d.deptno,d.dname from t1emp e RIGHT OUTER JOIN t1dept d ON e.deptno=d.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME
----- ------- ------ ------ ---------------
7521 WARD 30 30 SALES
7566 JONES 20 20 RESEARCH
7654 MARTIN 30 30 SALES
7698 BLAKE 30 30 SALES
7788 SCOTT 20 20 RESEARCH
7839 KING 10 10 ACCOUNTING
7844 TURNER 30 30 SALES
7876 ADAMS 20 20 RESEARCH
7900 JAMES 30 30 SALES
7902 FORD 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
40 OPERATIONS
12 rows selected.
SQL> select e.empno,e.ename,e.deptno,d.deptno,d.dname from t1emp e RIGHT JOIN t1dept d ON e.deptno=d.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME
----- ------- ------ ------ ---------------
7521 WARD 30 30 SALES
7566 JONES 20 20 RESEARCH
7654 MARTIN 30 30 SALES
7698 BLAKE 30 30 SALES
7788 SCOTT 20 20 RESEARCH
7839 KING 10 10 ACCOUNTING
7844 TURNER 30 30 SALES
7876 ADAMS 20 20 RESEARCH
7900 JAMES 30 30 SALES
7902 FORD 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
40 OPERATIONS
12 rows selected.
SQL> select e.empno,e.ename,e.deptno,d.deptno,d.dname from t1emp e,t1dept d where e.deptno(+)=d.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME
----- ------- ------ ------ ---------------
7521 WARD 30 30 SALES
7566 JONES 20 20 RESEARCH
7654 MARTIN 30 30 SALES
7698 BLAKE 30 30 SALES
7788 SCOTT 20 20 RESEARCH
7839 KING 10 10 ACCOUNTING
7844 TURNER 30 30 SALES
7876 ADAMS 20 20 RESEARCH
7900 JAMES 30 30 SALES
7902 FORD 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
40 OPERATIONS
12 rows selected. |
用韦恩图表示INNER JOIN大概是这样的
笛卡尔积：CROSS JOIN
CROSS JOIN就是笛卡尔乘积连接，不需要任何关联条件，实现M*N的结果集，其实这种SQL JOIN方式基本上只在理论上有意义，实际当中，很少有用的CORSS JOIN方式。
注意： cross join跟inner join、outer join等有所不同，不需要关键词on，因为它不需要相关字段做关联。
注意:此时得到的结果中，有些数据是不正常的，成为业务中的‘垃圾数据’(没有实际的意义);但是， 有时候需要短时间内生成大量的测试数据，便于进行压力测试，或者性能测试，此时笛卡尔积才有点意义;
建立测试表tcs1、tcs2并插入数据
| SQL> create table tcs1(id number(2),name varchar2(10),deptno number(2));
Table created.
SQL> create table tcs2(deptno number(2),dname varchar2(10),loc varchar2(10));
Table created.
SQL> insert into tcs1 values(1,'name1',10);
1 row created.
SQL> insert into tcs1 values(2,'name2',20);
1 row created.
SQL> insert into tcs1 values(3,'name3',30);
1 row created.
SQL> insert into tcs2 values(10,'dept1','loc1');
1 row created.
SQL> insert into tcs2 values(20,'dept2','loc2');
1 row created.
SQL> insert into tcs2 values(30,'dept3','loc3');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tcs1;
ID NAME DEPTNO
---------- ------------------------------ ------
1 name1 10
2 name2 20
3 name3 30
SQL> select * from tcs2;
DEPTNO DNAME LOC
------ --------------- ---------------
10 dept1 loc1
20 dept2 loc2
30 dept3 loc3 |
查看笛卡尔积CROSS JOIN
| SQL> select rownum,tcs1.name,tcs2.dname from tcs1 CROSS JOIN tcs2;
ROWNUM NAME DNAME
---------- ------------------------------ ---------------
1 name1 dept1
2 name1 dept2
3 name1 dept3
4 name2 dept1
5 name2 dept2
6 name2 dept3
7 name3 dept1
8 name3 dept2
9 name3 dept3
9 rows selected.
SQL> select rownum,tcs1.name,tcs2.dname from tcs1,tcs2;
ROWNUM NAME DNAME
---------- ------------------------------ ---------------
1 name1 dept1
2 name1 dept2
3 name1 dept3
4 name2 dept1
5 name2 dept2
6 name2 dept3
7 name3 dept1
8 name3 dept2
9 name3 dept3
9 rows selected. |
本来应该是可以确定每个人在哪个部门，但是因为笛卡尔积的原因，出现了不正常数据
笛卡尔积无法用韦恩图表示
自然连接：NATURAL JOIN
在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列
创建测试表tna1、tna2，并插入数据
| SQL> create table tna1(id number(2),name varchar2(10));
Table created.
SQL> create table tna2(id number(2),other varchar2(10));
Table created.
SQL> insert into tna1 values(1,'name1');
1 row created.
SQL> insert into tna1 values(1,'name1');
1 row created.
SQL> insert into tna1 values(2,'name2');
1 row created.
SQL> insert into tna2 values(1,'other1');
1 row created.
SQL> insert into tna2 values(2,'other2');
1 row created.
SQL> insert into tna2 values(3,'other3');
1 row created.
SQL> select * from tna1;
ID NAME
---------- ------------------------------
1 name1
1 name1
2 name2
SQL> select * from tna2;
ID OTHER
---------- ------------------------------
1 other1
2 other2
3 other3
SQL> select * from tna1 NATURAL JOIN tna2;
ID NAME OTHER
---------- ------------------------------ --------------
1 name1 other1
1 name1 other1
2 name2 other2 |
官方解释：
The NATURAL keyword indicates that a natural join is being performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias
另外NATURAL JOIN 仅需两张表中字段名称相同，即可完成连接
举例如下
建立表tna3、tna4并插入数据
| SQL> create table tna3(id number(2),name varchar2(10));
Table created.
SQL> create table tna4(id varchar2(2),other varchar2(10));
Table created.
SQL> insert into tna3 values(1,’name1’);
1 row created.
SQL> insert into tna3 values(1,’name1’);
1 row created.
SQL> insert into tna3 values(2,’name2’);
1 row created.
SQL> insert into tna4 values('1','other1');
1 row created.
SQL> insert into tna4 values('2','other2');
1 row created.
SQL> insert into tna4 values('3','other3');
1 row created.
SQL> select * from tna3;
ID NAME
---------- ------------------------------
1 name1
1 name1
2 name2
SQL> select * from tna4;
ID OTHER
------ ------------------------------
1 other1
2 other2
3 other3
SQL> desc tna3;
Name Null? Type
----------------- -------- ------------
ID NUMBER(2)
NAME VARCHAR2(10)
SQL> desc tna4;
Name Null? Type
----------------- -------- ------------
ID VARCHAR2(2)
OTHER VARCHAR2(10)
SQL> select * from tna3 NATURAL JOIN tna4;
ID NAME OTHER
------ --------------------- ------------------------------
1 name1 other1
1 name1 other1
2 name2 other2 |
自然连接的两个表的有多个字段都满足有相同名称，那么他们会被作为自然连接的条件
| SQL> create table tna5(id number(2),name varchar2(10));
Table created.
SQL> create table tna6(id number(2),name varchar2(10));
Table created.
SQL> insert into tna5 values(1,'name1');
1 row created.
SQL> insert into tna5 values(2,'name2');
1 row created.
SQL> insert into tna6 values(1,'name3');
1 row created.
SQL> insert into tna6 values(2,'name2');
1 row created.
SQL> select * from tna5;
ID NAME
---------- ------------------------------
1 name1
2 name2
SQL> select * from tna6;
ID NAME
---------- ------------------------------
1 name3
2 name2
SQL> select * from tna5 NATURAL JOIN tna6;
ID NAME
---------- ------------------------------
2 name2 |
自然连接的韦恩图和内连接的一样
SEMI JOIN
通常出现在使用了exists或in的sql中，所谓semi-join即在两表关联时，当第二个表中存在一个或多个匹配记录时，返回第一个表的记录；
与普通join的区别在于semi-join时，第一个表里的记录最多只返回一次
In the following example, only one row needs to be returned from the departments table, even though many rows in the employees table might match the subquery. If no index has been defined on thesalary column in employees, then a semijoin can be used to improve query performance.
SELECT * FROM departments
WHERE EXISTS
(SELECT * FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500)
ORDER BY department_name;
举例如下
创建部门测试表tsemi1和测试员工表tsemi2并修改数据
| SQL> show user;
USER is "SCOTT"
SQL> create table tsemi1 as select * from dept;
Table created.
SQL> create table tsemi2 as select * from emp;
Table created.
SQL> select * from tsemi1;
DEPTNO DNAME LOC
------ --------------- ---------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from tsemi2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- ---------- ----- ------------------- ----- ----- ------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
15 rows selected. |
查询出职员不为空的部门
用普通连接查询会产生重复记录（打开执行计划）
| SQL> alter session set statistics_level=all;
Session altered.
SQL> select d.deptno,d.dname from tsemi1 d,tsemi2 e where e.deptno=d.deptno;
DEPTNO DNAME
------ ---------------
20 RESEARCH
30 SALES
30 SALES
20 RESEARCH
30 SALES
30 SALES
10 ACCOUNTING
20 RESEARCH
10 ACCOUNTING
30 SALES
20 RESEARCH
30 SALES
20 RESEARCH
10 ACCOUNTING
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 674227419
---------------------------------------------------------------------
| Id | Operation |Name |Rows| Bytes |Cost (%CPU)|Time
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 525 | 7 (0)|00:00:01 |
|* 1 | HASH JOIN | | 15 | 525 | 7 (0)|00:00:01 |
| 2 | TABLE ACCESS FULL|TSEMI1| 4 | 88 | 3 (0)|00:00:01 |
| 3 | TABLE ACCESS FULL|TSEMI2| 15 | 195 | 4 (0)|00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2) |
而使用semi-join时候，不会返回重复记录
| SQL> select d.deptno,d.dname from tsemi1 d where d.deptno in( select e.deptno from tsemi2 e );
DEPTNO DNAME
------ ---------------
20 RESEARCH
30 SALES
10 ACCOUNTING
Execution Plan
----------------------------------------------------------
Plan hash value: 2980023518
---------------------------------------------------------------------
|Id | Operation |Name |Rows|Bytes| Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0| SELECT STATEMENT | | 4 | 140| 7 (0)| 00:00:01 |
|* 1| HASH JOIN SEMI | | 4 | 140| 7 (0)| 00:00:01 |
| 2| TABLE ACCESS FULL|TSEMI1| 4 | 88| 3 (0)| 00:00:01 |
| 3| TABLE ACCESS FULL|TSEMI2| 15 | 195| 4 (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2) |
ANTI JOIN
anti-join则与semi-join相反，即当在第二张表没有发现匹配记录时，才会返回第一张表里的记录；
当使用not exists/not in的时候会用到，两者在处理null值的时候会有所区别
何时选择anti-join
1 使用not in且相应列有not null约束
2 not exists，不保证每次都用到anti-join
当无法选择anti-join时，oracle常会采用filter替代
Using Antijoins: Example
The following example selects a list of employees who are not in a particular set of departments:
SELECT * FROM employees
WHERE department_id NOT IN
(SELECT department_id FROM departments
WHERE location_id = 1700)
ORDER BY last_name;
举例如下
查询职员为空的部门
先用普通连接查询
| SQL> select d.deptno,d.dname from tsemi1 d minus select d.deptno,d.dname from tsemi1 d where d.deptno in ( select e.deptno from tsemi2 e );
DEPTNO DNAME
------ ---------------
40 OPERATIONS
Execution Plan
----------------------------------------------------------
Plan hash value: 2826136730
----------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes| Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0|SELECT STATEMENT | | 4 | 613 | 12 (17)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 4 | 88 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL |TSEMI1| 4 | 88 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 15 | 525 | 8 (13)| 00:00:01 |
|*5 | HASH JOIN | | 15 | 525 | 7 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL|TSEMI1| 4 | 88 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL|TSEMI2| 15 | 195 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2) |
使用anti join
| SQL> select d.deptno,d.dname from tsemi1 d where d.deptno not in ( select deptno from tsemi2 );
DEPTNO DNAME
------ ---------------
40 OPERATIONS
Execution Plan
----------------------------------------------------------
Plan hash value: 2185638631
----------------------------------------------------------------------
|Id | Operation |Name |Rows |Bytes| Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0| SELECT STATEMENT | | 4 | 140 | 7 (0)| 00:00:01 |
|* 1| HASH JOIN ANTI NA | | 4 | 140 | 7 (0)| 00:00:01 |
| 2| TABLE ACCESS FULL|TSEMI1| 4 | 88 | 3 (0)| 00:00:01 |
| 3| TABLE ACCESS FULL|TSEMI2| 15 | 195 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2) |
注：倘若subquery返回一条null值，则整个not in都变为false，即不返回任何值
If the subquery of a NOT IN clause returns at least one row with a null value, the entire NOT IN clause evaluates to false for all rows
插入一条空值
| SQL> insert into tsemi2 values(9999,null,null,null,sysdate,null,null,null);
1 row created. |
再次使用anti join没有返回数据
| SQL> select d.deptno,d.dname from tsemi1 d where d.deptno not in (select deptno from tsemi2);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2185638631
----------------------------------------------------------------------
|Id | Operation | Name |Rows|Bytes| Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 140 | 7 (0)| 00:00:01 |
|*1 | HASH JOIN ANTI NA | | 4 | 140 | 7 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TSEMI1 | 4 | 88 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TSEMI2 | 15 | 195 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2) |
自连接：SELF JOIN
从表自身内部的关联中查询相关信息
例:想知道 emp 表中，每位员工对应的上级主管的名字
| SQL> select e.empno,e.ename,e.mgr,m.empno,m.ename from emp e,emp m where e.mgr=m.empno;
EMPNO ENAME MGR EMPNO ENAME
----- ------- ----- ----- -------
7902 FORD 7566 7566 JONES
7788 SCOTT 7566 7566 JONES
7844 TURNER 7698 7698 BLAKE
7499 ALLEN 7698 7698 BLAKE
7521 WARD 7698 7698 BLAKE
7900 JAMES 7698 7698 BLAKE
7654 MARTIN 7698 7698 BLAKE
7934 MILLER 7782 7782 CLARK
7876 ADAMS 7788 7788 SCOTT
7698 BLAKE 7839 7839 KING
7566 JONES 7839 7839 KING
7782 CLARK 7839 7839 KING
7369 SMITH 7902 7902 FORD
13 rows selected. |
1416
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
