mysql连接join查询结果_【SQL】join 连接查询

1. 交叉连接

示例的两个数据表

交叉连接为将两个表执行叉乘(笛卡尔积)，对于 t1 数据表中的每一条记录都有可能和 t2 中的每一条记录建立关联，因此得到了如下所示的结果。总记录数为两个表记录总数的乘积

SELECT * FROM t1, t2;

上面是隐式的交叉连接，显式的写法如下，两个SQL语句等价

SELECT * FROM t1 CROSS JOIN t2;

交叉连接查询的结果并不一定都是需要的，这时可以设置过滤条件只保留需要的结果

SELECT * FROM t1 CROSS JOIN t2 WHERE t1.name = '张三';

在跨越两个数据表进行查询时有一个过滤器设置是检查某个字段的值是否匹配，示例中的两个数据表的 id 字段假设是相同的概念(彼此关联匹配)

SELECT * FROM t1 CROSS JOIN t2 WHERE t1.id = t2.id;

这种查询是基于交叉连接的笛卡尔积的结果基础上，再次过滤出满足条件的记录重新组合的结果，是一种特殊的情况(内连接)

2. 内连接

两个数据表的某一个或多个字段相互关联的条件下进行的查询

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

当两个数据表中相互关联的字段名相同时(示例中 id 字段名相同)，可以使用另一种写法显示指定相同的字段名

SELECT * FROM t1 INNER JOIN t2 USING (id);

这样在查询时可以避免交叉连接查询出的多余数据而进行二次查询过滤，INNER JOIN ... ON 比 WHERE 理论上更有效率，然而现代SQL查询的底层优化，很有可能会为这两种查询方式优化成相同的执行脚本代码，从而忽略两者的性能差距。当然就可读性来说，能显示书写SQL语句，方便的分清楚是什么连接的查询，而不是在语句后加 WHERE 过滤器，这样会更好，对于多表的连接查询也是非常必要的

自然连接

某些数据库还支持自连接(具有相同字段名的内部连接数据表)，自连接是关系代数的专业术语，在SQL查询中并不常用

SELECT * FROM t1 NATURAL JOIN t2;

3. 外连接

上述的内连接是在两个数据表中找到了指定字段匹配的数据记录，然而如果想要依据数据表1中的所有记录在数据表2中进行匹配，可能会在数据表2中没有找到(null值)

SELECT

id, NAME, money

FROM

t1 INNER JOIN t2 USING (id)

UNION SELECT

id,NAME, NULL FROM

t1

WHERE

   id NOT IN (SELECT id FROM t2);

第一个查询是内连接，将id字段匹配的记录从笛卡尔积中过滤出来；第二个查询是使用子查询，将数据表t1中每一个记录的id值与数据表t2的所有id值进行比较，找出不存在的记录，用 null 表示不存在的数据；最后使用 UNION 整合两个查询的结果，两个查询结果的字段名和数量必须完全一致

左外连接

上述就是一个左外连接查询的展开SQL语句，为了可读性和便捷性，可以写成如下所示

SELECT * FROM t1 LEFT OUTER JOIN t2 USING (id);

这是左外连接，以 LEFT OUTER JOIN 左边的 t1 表为参照(所有记录都会显示在结果中)，在 t2 表中进行匹配，找不到时就以 null值 表示

右外连接

右外连接同理

SELECT * FROM t1 RIGHT OUTER JOIN t2 USING (id);

全外连接

左外连接和右外连接的并集就是全外连接

SELECT * FROM t1 FULL OUTER JOIN t2 USING (id);

MySQL数据库不支持全外连接，需要使用 UNION 整合左外连接和右外连接的查询结果，注意这两个查询的字段要对齐

SELECT

id, name, money

FROM

t1 LEFT OUTER JOIN t2 USING (id)

UNION ALL

SELECT

id, name, money

FROM

   t1 RIGHT OUTER JOIN t2 USING (id);

这里使用 UNION ALL 是因为在这里有一个坑：因为左外连接和右外连接查询时，会有重复的记录，UNION 在处理这些重复记录时，支持外部连接的数据库是不会删除的，但是不支持的数据库会去重，如果需要看到这些重复的记录，这时需要加 ALL ，尽管效率会降低，这是一种更准确的写法

SELECT

id, name, money

FROM

t1 LEFT OUTER JOIN t2 USING (id)

UNION SELECT

id, name, money

FROM

   t1 RIGHT OUTER JOIN t2 USING (id);

4. 多列连接

以上全是基于两个数据表一个字段的连接查询示例，SQL对于JOIN的语法并未限制只能有一个条件，所以可以在多个列和任意条件上进行连接，只需在 USING 之后的的括号中添加连接的字段名，使用逗号分隔即可

5. 多表连接

然而在实际的数据库中，数据常常被分在多个数据表中，例如有三张数据表(顾客表：customer 商品表：item 订单表：order)

SELECT * FROM customer;

SELECT * FROM item;

SELECT * FROM `order`;

现在有一个场景，需要查询所有购买香蕉的顾客的姓名、价格、生成订单的日期，则需要连接三个表进行查询

SELECT

customer_name, money, build_date

FROM

`order`

INNER JOIN item USING ( item_id )INNER JOIN customer USING ( customer_id )

WHERE

item_name = '香蕉';

这里需要注意数据表的连接顺序，通过显式的控制合理的数据表连接顺序(上述例子并不是必须的)，任意多个数据表的连接按照从左往右的顺序依次生成新的逻辑表，然后参与到后续的连接，这是建议这么去做的