转载

MySQL Join语法

CROSS JOIN（交叉联接）

CROSS JOIN对两个表执行笛卡儿积，返回两个表中所有列的组合。若左表有m行数据，右表有n行数据，则CROSS JOIN将返回m*n行的表。

SELECT * FROM tableA [CROSS] JOIN tableB SELECT * FROM tableA,tableB

INNER JOIN（内联接）

INNER JOIN首先产生笛卡儿积的虚拟表，再按照ON过滤条件来进行数据的匹配操作。

SELECT * FROM tableA [INNER] JOIN tableB ON tableA.id = tableB.id SELECT * FROM tableA,tableB WHERE tableA.id = tableB.id

INNER JOIN其实是取两个集合的并集
如果ON过滤字段值为NULL(table.A=NULL),即使table.B也为NULL，最终的结果中也不会含有该记录，因为NULL=NULL并不为true
JOIN, INNER JOIN, CROSS JOIN 是完全一样的有过滤条件则是内连接，没有则是CROSS JOIN

OUTER JOIN（外联接）

OUTER JOIN应用逻辑查询的前三个步骤，即产生笛卡儿积、应用ON过滤器和添加外部行。与INNER关键字一样，可以省略OUTER关键字。

... FROM table1 LEFT|RIGHT [OUTER] JOIN table2 ON condition

OUTER JOIN 分为 LEFT [OUTER] JOIN 和 RIGHT [OUTER] JOIN LEFT JOIN（左连接）：取得左表（table1）完全记录,如果是table2中未找到匹配数据，table2表的值用NULL进行填充。 RIGHT JOIN（右连接）：取得右表（table2）完全记录,如果是table1中未找到匹配数据，table1表的值用NULL进行填充。

可以使用OUTER JOIN 和 IS NULL 查找只属于一个集合的数据

ON与WHERE的区别INNER JOIN 的过滤条件既可以写在ON中也可以写在WHERE中，但是OUTER JOIN中这两者是有区别的。

ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。 SELECT * FROM product; | id | amount | |---|---| | 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | 400 |

SELECT * FROM product_details;| id | weight | exist | |----|--------|-------| | 2 | 22 | 0 | | 4 | 44 | 1 | | 5 | 55 | 0 | | 6 | 66 | 1 |

SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) AND product_details.id=2;

| id | amount | id | weight | exist | |----|--------|------|--------|-------| | 1 | 100 | NULL | NULL | NULL | | 2 | 200 | 2 | 22 | 0 | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | NULL | NULL | NULL |

product.id = product details.id AND product details.id=2作为一个整体条件

SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) WHERE product_details.id=2;

| id | amount | id | weight | exist | |----|--------|----|--------|-------| | 2 | 200 | 2 | 22 | 0 |

首先ON条件过滤，此时临时表会有四条记录，然后where条件(product_details.id=2)过滤

实现原理

在MySQL中，只有一种Join算法，就是大名鼎鼎的Nested Loop Join。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复，基本上MySQL采用的是最容易理解的算法来实现join。所以驱动表的选择非常重要，驱动表的数据小可以显著降低扫描的行数。对于INNER JOIN MySQL对自动选择结果集小的表作为驱动表。