SQL 的 四大连接方式 左外连接、右外连接、内连接、全连接
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定;但是,用左或右向外联接指定表或视图时,表或视图的顺序很重要
---内连接
select e.*,d.* from emp e,dept d where e.deptno=d.deptno;
不仅查出emp表中的数据 也将dept表中的数据查出
empno ename deptno dname
1 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK
2 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 ACCOUNTING NEW YORK
3 7839 KING PRESIDENT 1981/11/17 5000.00 10 10 ACCOUNTING NEW YORK
4 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 20 RESEARCH DALLAS
5 7369 SMITH CLERK 7902 1980/12/17 800.00 20 20 RESEARCH DALLAS
6 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 20 RESEARCH DALLAS
7 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 20 RESEARCH DALLAS
8 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 20 RESEARCH DALLAS
9 7521 WARD SALESMAN 7698 1981/2/22 1750.00 30 30 SALES CHICAGO
10 7844 TURNER SALESMAN 7698 1981/9/8 2000.00 30 30 SALES CHICAGO
11 7499 ALLEN SALESMAN 7698 1981/2/20 2100.00 30 30 SALES CHICAGO
12 7900 JAMES CLERK 7698 1981/12/3 1450.00 30 30 SALES CHICAGO
13 7654 MARTIN SALESMAN 7698 1981/9/28 1750.00 30 30 SALES CHICAGO
14 7698 BLAKE MANAGER 7839 1981/5/1 3350.00 30 30 SALES CHICAGO
---左连接
select e.*,d.* from emp e left join dept d on e.deptno=d.deptno;--(输出要查询的左边这张表的所有数据)
查询到的数据中增加啦一条,在内连接中并没有出现的数据,因为内连接是将emp.deptno=dept.deptno查询
(如果emp表中没有其他数据,可以先添加一条,emp先不要给部门编号)
2 7839 KING PRESIDENT 1981/11/17 5000.00 10 10 ACCOUNTING NEW YORK
3 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK
4 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 20 RESEARCH DALLAS
5 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 20 RESEARCH DALLAS
6 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 20 RESEARCH DALLAS
7 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 20 RESEARCH DALLAS
8 7369 SMITH CLERK 7902 1980/12/17 800.00 20 20 RESEARCH DALLAS
9 7900 JAMES CLERK 7698 1981/12/3 1450.00 30 30 SALES CHICAGO
10 7844 TURNER SALESMAN 7698 1981/9/8 2000.00 0.00 30 30 SALES CHICAGO
11 7698 BLAKE MANAGER 7839 1981/5/1 3350.00 30 30 SALES CHICAGO
12 7654 MARTIN SALESMAN 7698 1981/9/28 1750.00 1400.00 30 30 SALES CHICAGO
13 7521 WARD SALESMAN 7698 1981/2/22 1750.00 500.00 30 30 SALES CHICAGO
14 7499 ALLEN SALESMAN 7698 1981/2/20 2100.00 300.00 30 30 SALES CHICAGO
15 123 小明 2017/9/30 18:26:33
--右链接
select e.*,d.* from emp e right join dept d on e.deptno=d.deptno;--(输出要查询的右边这张表的所有数据)
同样查询出15条数数据,但是查出的是dept表中的所有数据
1 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK
2 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 ACCOUNTING NEW YORK
3 7839 KING PRESIDENT 1981/11/17 5000.00 10 10 ACCOUNTING NEW YORK
4 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 20 RESEARCH DALLAS
5 7369 SMITH CLERK 7902 1980/12/17 800.00 20 20 RESEARCH DALLAS
6 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 20 RESEARCH DALLAS
7 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 20 RESEARCH DALLAS
8 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 20 RESEARCH DALLAS
9 7521 WARD SALESMAN 7698 1981/2/22 1750.00 30 30 SALES CHICAGO
10 7844 TURNER SALESMAN 7698 1981/9/8 2000.00 30 30 SALES CHICAGO
11 7499 ALLEN SALESMAN 7698 1981/2/20 2100.00 30 30 SALES CHICAGO
12 7900 JAMES CLERK 7698 1981/12/3 1450.00 30 30 SALES CHICAGO
13 7654 MARTIN SALESMAN 7698 1981/9/28 1750.00 30 30 SALES CHICAGO
14 7698 BLAKE MANAGER 7839 1981/5/1 3350.00 30 30 SALES CHICAGO
15 40 OPERATIONS BOSTON
--外链接
select e.*,d.* from emp e full join dept d on e.deptno=d.deptno;--(输出要查询两张表的所有数据)
查询出两张表中所有的数据
1 7369 SMITH CLERK 7902 1980/12/17 800.00 20 20 RESEARCH DALLAS
2 7499 ALLEN SALESMAN 7698 1981/2/20 2100.00 30 30 SALES CHICAGO
3 7521 WARD SALESMAN 7698 1981/2/22 1750.00 30 30 SALES CHICAGO
4 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 20 RESEARCH DALLAS
5 7654 MARTIN SALESMAN 7698 1981/9/28 1750.00 30 30 SALES CHICAGO
6 7698 BLAKE MANAGER 7839 1981/5/1 3350.00 30 30 SALES CHICAGO
7 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK
8 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 20 RESEARCH DALLAS
9 7839 KING PRESIDENT 1981/11/17 5000.00 10 10 ACCOUNTING NEW YORK
10 7844 TURNER SALESMAN 7698 1981/9/8 2000.00 30 30 SALES CHICAGO
11 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 20 RESEARCH DALLAS
12 7900 JAMES CLERK 7698 1981/12/3 1450.00 30 30 SALES CHICAGO
13 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 20 RESEARCH DALLAS
14 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 ACCOUNTING NEW YORK
15 123 小明 2017/9/30 18:26:33
16 40 OPERATIONS BOSTON