mysql 多表连接 left join 等

作者：zccst

一、  理论

(一)内连接(INNER JOIN)

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：

1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。


(二)外连接
外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。

内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。


(三)交叉连接(CROSS JOIN)

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。



二、  实践

两个表的连接比较简单（左连接、右连接、内连接），此处略。三个和三个以上的表道理相同，所以以三个表为例分析多表连接。

假设三个表的分别为a, b, c

(一)以a为主表，b和c为辅表

select a.a as a, b.b as b, c.c as c from a left join b on a.id=b.id left join c on b.id=c.id where…

批注1：左连接按先后顺序，先是表a和表b连接，再与表c连接。

批注2：加条件的时候用where。

批注3：如果b是主表的话，把表b放前面即可。

批注4：也可以按如下写法

Select * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) where…

//这个语句的执行结果和下面语句其实是一样的：
Select * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) where…

批注5：不同表的引擎最好一样，不然会很慢。比如都是innodb或MyISAM


(二)表a和表b中前者是主表，表c又以表b为主表
待补充





三、  附语法
MySQL支持Select和某些Update和Delete情况下的Join语法，具体语法上的细节有：

    table_references:

    table_reference [, table_reference] …

    table_reference:

    table_factor

    | join_table

    table_factor:

    tbl_name [[AS] alias]

    [{USE|IGNORE|FORCE} INDEX (key_list)]

    | ( table_references )

    | { OJ table_reference LEFT OUTER JOIN table_reference

    ON conditional_expr }

    join_table:

    table_reference [INNER | CROSS] JOIN table_factor [join_condition]

    | table_reference STRAIGHT_JOIN table_factor

    | table_reference STRAIGHT_JOIN table_factor ON condition

    | table_reference LEFT [OUTER] JOIN table_reference join_condition

    | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor

    | table_reference RIGHT [OUTER] JOIN table_reference join_condition

    | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

    join_condition:

    ON conditional_expr | USING (column_list)

table_reference其实就是表的引用的意思，因为在MySQL看来，连接就是一种对表的引用，因此把需要连接的表定义为 table_reference，同时在SQL Standard中也是如此看待的。而table_factor则是MySQL对这个引用的功能上的增强和扩充，使得引用的表可以是括号内的一系列表


如下面例子中的JOIN后面括号：

    Select * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) php开源嘛

    这个语句的执行结果和下面语句其实是一样的：

    Select * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)

    ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

这两个例子不仅让我们了解了MySQL中table_factor和table_reference含义，同时能理解一点CROSS JOIN的用法，我要补充的是在MySQL现有版本中CROSS JOIN的作用和INNER JOIN是一样的（虽然在SQL Standard中是不一样的，然而在MySQL中他们的区别仅仅是INNER JOIN需要附加ON参数的语句，而CROSS JOIN不需要）。


如果您觉得本文的内容对您的学习有所帮助，您可以微信：

评论

1 楼 wlzx0 2016-03-08  

楼主，(二)表a和表b中前者是主表，表c又以表b为主表  这个待补充，是否已经有了思路？