MySQL 外连接

外连接

左外连接 左表是主表

mysql> select a.ename ‘员工’,b.ename ‘领导’ from emp a left join emp b on a.mgr =b.empno;
±-------±-------+
| 员工 | 领导 |
±-------±-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
±-------±-------+

mysql> select a.ename ‘员工’,b.ename ‘领导’ from emp b right outer join emp a on a.mgr =b.empno;
±-------±-------+
| 员工 | 领导 |
±-------±-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
±-------±-------+

outer 可以省略
mysql> select
-> d.*
-> from
-> emp e
-> right join
-> dept d
-> on
-> e.deptno = d.deptno
-> where
-> e.empno is null;
±-------±-----------±-------+
| DEPTNO | DNAME | LOC |
±-------±-----------±-------+
| 40 | OPERATIONS | BOSTON |
±-------±-----------±-------+