Mysql 7种join连接（转）-原创手记-慕课网

Sql如下：

1）左外连接，以左边的表为主，左连接即左边的表独有+左边表和右边表共有数据

SELECT * FROM dept t

LEFT JOIN emp t2

ON t.`id_dept` = t2.`id_dept`;

2）右外连接，以右表的表为主，右连接即右边表独有+右边和左边共有数据

SELECT * FROM dept t

RIGHT JOIN emp t2

ON t.`id_dept` = t2.`id_dept`;

3）内连接，也叫自然连接同时也叫全等连接，即左边表和右边表共有数据的查询

SELECT * FROM dept t

INNER JOIN emp t2

ON t.`id_dept` = t2.`id_dept`;

4）左连接，即查询左边表的独有数据

SELECT * FROM dept t

LEFT JOIN emp t2

ON t.`id_dept` = t2.`id_dept`

WHERE t2.`id_dept` IS NULL;

5）右连接，即查询右边表独有数据

SELECT * FROM dept t

RIGHT JOIN emp t2

ON t.`id_dept` = t2.`id_dept`

WHERE t.`id_dept` IS NULL;

6）全连接，即左边表数据+右边表数据+左边表和右边表共有数据查询

SELECT * FROM dept t

LEFT JOIN emp t2

ON t.`id_dept` = t2.`id_dept`

UNION

SELECT * FROM dept t

RIGHT JOIN emp t2

ON t.`id_dept` = t2.`id_dept`;

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。

UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。

同时，每条 SELECT 语句中的列的顺序必须相同.

7）全外连接，即左右表的共有数据之外的数据查询

SELECT * FROM dept t

FULL OUTER emp t2

ON t.`id_dept` = t2.`id_dept`

WHERE t.`id_dept` IS NULL

OR t2.`id_dept` IS NULL; --mysql不支持这种连接的语法

SELECT * FROM dept t

LEFT JOIN emp t2

ON t.`id_dept` = t2.`id_dept`

WHERE t2.`id_dept` IS NULL

UNION

SELECT * FROM dept t

RIGHT JOIN emp t2

ON t.`id_dept` = t2.`id_dept`

WHERE t.`id_dept` IS NULL;

oracle 的sql如下：

------------------自然连接把一张表当两张表用

select e1.ename ,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno ) ;

select　ename,dname from emp join dept on (emp.deptno=dept.deptno);

---等值连接的简洁写法:

select ename ,dname from emp join dept using (deptno);

---外连接:

--左外连接

select e1.ename ,e2.ename from emp e1 left join emp e2 on (e1.mgr =

e2.empno ) ;

select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+);

--右外连接:

select e1.ename ,e2.ename from emp e1 right join emp e2 on (e1.mgr =

e2.empno ) ;

select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr(+) = e2.empno;

---------------------

作者：wbsjhbl

来源：CSDN

原文：https://blog.csdn.net/weisg81/article/details/78937996