SQL里 inner JOIN、 left  (OUTER) JOIN 、right  (OUTER) JOIN、 full (OUTER) JOIN 之间的区别 

/*  直接上例子废话不多说
    此例子里两张表之间相等的都是各自的主键
    然而大部分情况下是A表的主键和B表里的外键相等,
    所以就会存在B表里多行记录匹配A表里的一行记录
    这里仅做例子
*/
/* 在写连接查询的时候,ON后面只跟连接条件,而对中间表限制的条件都写到WHERE子句中。 
*/
A表(aID,aCourse,aGrade)       |         B表(bID,bName)
aID  aCourse     aGrade       |         bID   bName
01   数学         95          |        01    张三
02   语文         90          |        02    李四
03   英语         80          |        04    王五

-- inner join(等值连接取交集) 只返回两个表中联结字段相等的行
SELECT a.*,b.* FROM A a INNER JOIN B b ON a.aID = b.bID; -- a.*,b.* 可用 * 替代,INNER JOIN中间没有OUTER 
SELECT a.*,b.* FROM A a , B b WHERE a.aID = b.bID; --INNER JOIN 的简写,效果等同
-- 结果集:
aID   aCourse    aGrade     bID    bName
01    数学         95        01     张三
02    语文         90        02     李四

-- left join(左联接) 返回包括左表中的所有记录以及左表与右表中联结字段相等的记录 
SELECT * FROM A a LEFT OUTER JOIN B b ON a.aID = b.bID; -- OUTER可省略
-- 结果集:B表记录不足的地方均为NULL
aID   aCourse   aGrade      bID    bName
01    数学         95        01     张三
02    语文         90        02     李四
03    英语         80        NULL   NULL

--right join(右联接) 返回包括右表中的所有记录以及右边与左表中联结字段相等的记录
SELECT * FROM A a RIGHT JOIN B b ON a.aID = b.bID;-- OUTER省略
-- 结果集:A表记录不足的地方均为NULL
aID   aCourse   aGrade     bID    bName
01    数学         95        01     张三
02    语文         90        02     李四
NULL  NULL        NULL      04     王五

-- full JOIN(全连接) 返回包括A,B表中的所有记录
SELECT * FROM A a FULL JOIN B b ON a.aID = b.bID;---- OUTER省略
结果集:A、B表记录不足的地方均为NULL
aID   aCourse   aGrade    bID     bName
01    数学       95        01      张三
02    语文       90        02      李四
03    英语       80        NULL    NULL
NULL  NULL      NULL       04      王五

注意:MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
LEFT JOIN  + UNION(可去除重复数据)+ RIGHT JOIN
select * from A left join B on A.id = B.id (where 条件)
union
select * from A right join B on A.id = B.id (where条件);

上述全连接就可以在MYSQL中就可以替换成以下:

SELECT * FROM A a LEFT OUTER JOIN B b ON a.aID = b.bID
UNION
SELECT * FROM A a RIGHT JOIN B b ON a.aID = b.bID

6.STRAIGHT JOIN

STRAIGHT JOIN 完全同样 INNE纳瓦拉 JOIN 只可是,JOIN
语法是依据“哪个表的结果集小,就以哪个表为驱动表”来调整哪个人先载入的,而
STRAIGHT JOIN 会强制选用其右边手的表先载入。
多次大家在解析 MySql 管理品质时,如(Explain),如果发现MySql
在载入顺序不成立的气象下,能够行使这几个讲话,但频仍mysql可以活动的解析并拍卖好。

 

3.INNER JOIN

4503.com 1

基础INNER JOIN.jpg

INNE汉兰达 JOIN
是基于条件两张表的混杂。在不点名条件(ON)的时候,获得的是两张表的笛Carl积(即两张表全部的结合恐怕)。
能够见到,join_a 表中 id 为 1的笔录出现了三遍,那是因为在 join_b
表中有七个 name 为 Iris 的记录。
再正是,可以看出,在 ON 条件的时候,name 字段加了引号,那应该正是 MySql
对语句中冒出的特殊字符的管理啊。
接下来,能够动用示例中的小才具为结果增添行号。但奇怪的是,*
必须在@num := @num + 1 AS NUMBER 前面, 如果 * 在后头就能够报错。
INNE福特Explorer JOIN 中得以选拔 AND。

SET @num = 0;

SELECT
    *, @num := @num + 1 AS NUMBER
FROM
    join_a
INNER JOIN join_b ON join_a.`name` = join_b.`name`
AND join_a.id = join_b.id

4503.com 2

INNER JOIN + AND.jpg

一.语法结构

... FROM table1 INNER|LEFT|RIGHT JOIN table2 on condition

组成 含义
table1 左表
table2 右表
INNER JOIN 获取两个表中存在连接匹配关系的记录
LEFT JOIN 获取左表完全记录,即使右表并无对应匹配记录
RIGHT JOIN 与 LEFT JOIN 相反

5.RIGHT JOIN

参见 LEFT JOIN。