mysql中自连接怎么用 mysql自然连接和内连接( 三 )

MySQL 语法中还支持一个并不在 SQL 标准中的STRAIGHT_JOIN，它在表现上和内连接或者交叉连接并无区别，只是一种给 MySQL 优化器的一个提示， STRAIGHT_JOIN提示 MySQL 按照语句中表的顺序加载表，只有在你明确清楚 MySQL 服务器对你的JOIN语句做了负优化的时候才可能用到它。
还有一点需要说明的是，根据官方文档，在 MySQL 中，JOIN 、 CROSS JOIN和INNER JOIN实现的功能是一致的，它们在语法上是等价的。从语义上来说，CROSS JOIN特指无条件的连接（没有指定ON条件的JOIN或者没有指定WHERE连接条件的多表SELECT ）， INNER JOIN特指有条件的连接（指定了ON条件的JOIN或者指定了WHERE连接条件的多表SELECT ）。当然，如果你非要写... CROSS JOIN ... ON ...这样的语法，也是可以执行的，虽然写着交叉连接，实际上执行的是内连接。
下面我们就用例子来看一看 MySQL 中支持的几种连接的例子。
注：下面的例子都没有指定ORDER BY子句，返回结果的顺序可能会因为数据插入顺序的不同而略有不同。
MySQL 的交叉连接或内连接有两种写法，一种是使用JOIN并用ON或者USING 子句指定连接条件的写法，一种是普通的SELECT多表，并且用WHERE子句指定连接的键的写法。
下面的例子是一个交叉连接：
上面的写法等价于：
当然，第二种写法中如果将CROSS JOIN替换成JOIN或者INNER JOIN也是可以正确执行的。上面两条语句的执行结果如下：
可以看到共返回了 30 行结果，是两张表的笛卡尔积。
一个内连接的例子如下：
上面的写法等价于：
在连接条件比较的字段相同的情况下，还可以改用USING关键字，上面的写法等价于：
上面三条语句的返回结果如下：
可以看到只返回了符合连接条件customers.cust_id = orders.cust_id的 6 行结果，结果的含义是所有有订单的用户和他们的订单。
左外连接和右外连接的例子如下，其中的OUTER关键字可以省略：
其中右外连接的返回与内连接的返回是一致的（思考一下为什么），左外连接的返回结果如下：
可以看到一共返回了 8 行数据，其中最后两行数据对应的order_id的值为NULL ，结果的含义是所有用户的订单，不管这些用户是否已经有订单存在了。
根据前面介绍的自然连接的定义，自然连接会自动用参与连接的两张表中字段名相同的列做等值比较，由于例子中的customers和orders表只有一列名称相同，我们可以用自然连接的语法写一个与上面的内连接的例子表现行为一样的语句如下：
可以看到，使用自然连接就不能再用ON子句指定连接条件了，因为这完全是多余的。
当然，自然连接同样支持左外连接和右外连接。
下面用一个customers表自连接的例子再来说明一下自然连接，语句如下：
因为是自连接，因此必须使用AS指定别名，否则 MySQL 无法区分“两个”customers表，运行的结果如下：
可以看到结果集和customers表完全一致，大家可以思考一下为什么结果是这样的。
文章之前也提到了，MySQL 还支持一种 SQL 标准中没有定义的“方言”， STRAIGHT_JOIN，STRAIGHT_JOIN支持带ON子句的内连接和不带ON子句的交叉连接，我们来看一个STRAIGHT_JOIN版本的内连接的例子：
返回结果与前面内连接的例子是一致的，如下：
STRAIGHT_JOIN的表现和JOIN是完全一致的，它只是一种给 MySQL 优化器的提示，使得 MySQL 始终按照语句中表的顺序读取表（上面的例子中，MySQL 在执行时一定会先读取customers表，再读取orders表），而不会做改变读取表的顺序的优化。关于 MySQL 优化器的话题这里不做展开，需要说明的是除非你非常清楚你在做什么，否则不推荐直接使用STRAIGHT_JOIN。