建立英文网站,网站建设分析魅族,中国电信网站备案 流程,网站建设岗位廉政风险防控数据库中的外连接#xff08;Outer Join#xff09;用于连接两个表#xff0c;并包括两个表中的匹配行以及左表#xff08;LEFT JOIN#xff09;或右表#xff08;RIGHT JOIN#xff09;中未匹配的行。外连接分为两种主要类型#xff1a;
左外连接#xff08;LEFT OU…数据库中的外连接Outer Join用于连接两个表并包括两个表中的匹配行以及左表LEFT JOIN或右表RIGHT JOIN中未匹配的行。外连接分为两种主要类型
左外连接LEFT OUTER JOIN返回左表FROM子句中第一个表的所有行即使在右表中没有匹配的行。如果右表中没有匹配的行则结果集中右表的部分将包含NULL值。
右外连接RIGHT OUTER JOIN返回右表JOIN子句中指定的表的所有行即使在左表中没有匹配的行。如果左表中没有匹配的行则结果集中左表的部分将包含NULL值。
表的初始化 mysql create table stu (id int ,name varchar(30));
Query OK, 0 rows affected (0.02 sec)mysql desc stu;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)mysql insert into stu values(1,Jack),(2,Tom),(3,Kity),(4,Nono);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql select*from stu;
------------
| id | name |
------------
| 1 | Jack |
| 2 | Tom |
| 3 | Kity |
| 4 | Nono |
------------
4 rows in set (0.00 sec)mysql create table exam (id int ,grade int );
Query OK, 0 rows affected (0.02 sec)mysql desc exam;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| id | int(11) | YES | | NULL | |
| grade | int(11) | YES | | NULL | |
-------------------------------------------
2 rows in set (0.00 sec)mysql insert into exam values(1,56),(2,76),(11,82);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql select*from exam;
-------------
| id | grade |
-------------
| 1 | 56 |
| 2 | 76 |
| 11 | 82 |
-------------
3 rows in set (0.00 sec)mysql 查询所有学生的成绩如果这个学生没有成绩也要将学生的个人信息显示出来 mysql select *from stu;
------------
| id | name |
------------
| 1 | Jack |
| 2 | Tom |
| 3 | Kity |
| 4 | Nono |
------------
4 rows in set (0.00 sec)mysql select *from exam;
-------------
| id | grade |
-------------
| 1 | 56 |
| 2 | 76 |
| 11 | 82 |
-------------
3 rows in set (0.00 sec)mysql select *from stu left join exam on stu.idexam.id;
-------------------------
| id | name | id | grade |
-------------------------
| 1 | Jack | 1 | 56 |
| 2 | Tom | 2 | 76 |
| 3 | Kity | NULL | NULL |
| 4 | Nono | NULL | NULL |
-------------------------
4 rows in set (0.00 sec)mysql select *from stu inner join exam on stu.idexam.id;
-------------------------
| id | name | id | grade |
-------------------------
| 1 | Jack | 1 | 56 |
| 2 | Tom | 2 | 76 |
-------------------------
2 rows in set (0.00 sec)mysql 左外连接LEFT JOIN的查询结果显示了stu表中所有的学生包括那些在exam表中没有成绩的学生。对于没有成绩的学生如Kity和Nono成绩列grade显示为NULL。这说明左外连接包括了左表stu的所有行如果右表exam中没有匹配的行则相关列填充NULL。 内连接INNER JOIN的查询结果仅显示了那些在stu表和exam表中都有记录的学生。这意味着只有那些既在学生表中也在成绩表中有对应记录的学生才会被显示出来。例如Jack和Tom的记录被显示了出来因为他们在两个表中都有对应的记录。而Kity和Nono没有在exam表中的对应记录所以他们不在查询结果中。 总结来说外连接特别是左外连接用于查询主表LEFT JOIN左侧的表中的所有记录并尝试匹配外表右侧的表中的记录。如果外表中没有匹配的记录则结果集中对应的列会被填充NULL。内连接INNER JOIN则仅返回两个表中都有匹配的记录。这些特性使得外连接和内连接在数据查询中各有适用的场景。
对stu表和exam表联合查询把所有的成绩都显示出来即使这个成绩没有学生与之对应也要显示出来 mysql select*from stu right join exam on stu.id exam.id;
-------------------------
| id | name | id | grade |
-------------------------
| 1 | Jack | 1 | 56 |
| 2 | Tom | 2 | 76 |
| NULL | NULL | 11 | 82 |
-------------------------
3 rows in set (0.00 sec)mysql select *from exam left join stu on stu.idexam.id;
-------------------------
| id | grade | id | name |
-------------------------
| 1 | 56 | 1 | Jack |
| 2 | 76 | 2 | Tom |
| 11 | 82 | NULL | NULL |
-------------------------
3 rows in set (0.01 sec)mysql
右外连接RIGHT JOIN的查询结果展示了exam表中所有的记录包括那些在stu表中没有对应id的记录。在这个例子中学生id为11的记录在stu表中没有对应的条目因此name列显示为NULL。这表明右外连接包括了右表exam的所有行如果左表stu中没有匹配的行则相关列填充NULL。 左外连接LEFT JOIN的查询结果与右外连接的查询相反显示了exam表中所有记录的另一种方式同时尝试匹配stu表中的记录。与右外连接的结果相同学生id为11的记录在stu表中没有匹配导致name列为NULL。 这两个查询展示了外连接的互补性质
右外连接RIGHT JOIN强调了右侧表在ON关键字右边的表本例中为exam的所有行即使左侧表stu中没有匹配的行。
左外连接LEFT JOIN强调了左侧表在ON关键字左边的表本例中为exam由于查询的书写方式实际操作作为左侧表处理的所有行即使右侧表stu中没有匹配的行。
列出部门名称和这些部门的员工信息同时列出没有员工的部门 mysql select *from emp;
--------------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------------
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
--------------------------------------------------------------------------------
14 rows in set (0.00 sec)mysql select distinct deptno from emp;
--------
| deptno |
--------
| 20 |
| 30 |
| 10 |
--------
3 rows in set (0.00 sec)mysql select*from dept;
------------------------------
| deptno | dname | loc |
------------------------------
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
------------------------------
4 rows in set (0.00 sec)mysql select * from dept left join emp on dept.deptnoemp.deptno;
--------------------------------------------------------------------------------------------------------------
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------------------------------------------
| 20 | RESEARCH | DALLAS | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 30 | SALES | CHICAGO | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 20 | RESEARCH | DALLAS | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 30 | SALES | CHICAGO | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 10 | ACCOUNTING | NEW YORK | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 30 | SALES | CHICAGO | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 20 | RESEARCH | DALLAS | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 20 | RESEARCH | DALLAS | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
--------------------------------------------------------------------------------------------------------------
15 rows in set (0.00 sec)mysql select * from dept left join emp on dept.deptnoemp.deptno order by dept.deptno desc;
--------------------------------------------------------------------------------------------------------------
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------------------------------------------
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 30 | SALES | CHICAGO | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 30 | SALES | CHICAGO | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 30 | SALES | CHICAGO | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 30 | SALES | CHICAGO | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 30 | SALES | CHICAGO | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 30 | SALES | CHICAGO | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 20 | RESEARCH | DALLAS | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
--------------------------------------------------------------------------------------------------------------
15 rows in set (0.00 sec)mysql select * from dept left join emp on dept.deptnoemp.deptno order by dept.deptno asc;
--------------------------------------------------------------------------------------------------------------
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------------------------------------------
| 10 | ACCOUNTING | NEW YORK | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 30 | SALES | CHICAGO | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 30 | SALES | CHICAGO | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 30 | SALES | CHICAGO | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 30 | SALES | CHICAGO | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 30 | SALES | CHICAGO | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
--------------------------------------------------------------------------------------------------------------
15 rows in set (0.00 sec)mysql select dname,ename,dept.deptno from dept left join emp on dept.deptnoemp.deptno order by dept.deptno asc;
----------------------------
| dname | ename | deptno |
----------------------------
| ACCOUNTING | CLARK | 10 |
| ACCOUNTING | MILLER | 10 |
| ACCOUNTING | KING | 10 |
| RESEARCH | FORD | 20 |
| RESEARCH | JONES | 20 |
| RESEARCH | SMITH | 20 |
| RESEARCH | ADAMS | 20 |
| RESEARCH | SCOTT | 20 |
| SALES | BLAKE | 30 |
| SALES | WARD | 30 |
| SALES | TURNER | 30 |
| SALES | MARTIN | 30 |
| SALES | ALLEN | 30 |
| SALES | JAMES | 30 |
| OPERATIONS | NULL | 40 |
----------------------------
15 rows in set (0.00 sec)mysql 查询员工表 (emp) 和部门表 (dept):
select * from emp; 查询显示了emp表中所有的员工记录。
select * from dept; 查询显示了dept表中所有的部门记录。
使用DISTINCT关键字查询唯一的部门编号 (deptno):
select distinct deptno from emp; 查询从emp表中检索不重复的部门编号。这可以帮助了解有多少不同的部门拥有员工。
左外连接查询:
select * from dept left join emp on dept.deptnoemp.deptno; 这个查询使用左外连接来检索dept表中的所有部门记录以及与之匹配的emp表中的员工记录。如果某个部门没有员工对应的员工字段将为NULL。这确保了即使某些部门没有员工部门信息也会被检索出来。
根据部门编号 (deptno) 降序排序:
select * from dept left join emp on dept.deptnoemp.deptno order by dept.deptno desc; 这个查询不仅执行了左外连接还按照部门编号降序排列了结果。
根据部门编号 (deptno) 升序排序:
select * from dept left join emp on dept.deptnoemp.deptno order by dept.deptno asc; 类似地这个查询执行了左外连接但是按照部门编号升序排列了结果。
选择特定字段进行展示:
select dname, ename, dept.deptno from dept left join emp on dept.deptnoemp.deptno order by dept.deptno asc; 这个查询选择了部门名称(dname)、员工名称(ename)和部门编号(dept.deptno)作为输出并按部门编号升序排列。这样的查询有助于快速查看每个部门及其员工的概览。
结尾
最后感谢您阅读我的文章希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点请随时在评论区留言。 同时不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中我将继续探讨这个话题的不同方面为您呈现更多深度和见解。 谢谢您的支持期待与您在下一篇文章中再次相遇
