目录

一，join实现方式

（一），原理

二，join 后用 on  ，and 还是 where 区别

（一），原理

（二），on , where实例

1，创建表和数据

2，测试语句和结果

（三），on  ， and实例

1，添加数据

2，测试语句和结果

一，join实现方式

（一），原理

嵌套循环
A B
一行一行匹配，A1匹配B表全部，A2匹配B表全部。

索引
B表上必须有索引，匹配到索引后才会进行回表查询，
如果B表的关联键是主键的话，性能会非常高。
如果不是，需要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢。

如果有索引，会采用上面的方式进行join，但如果join列没有索引，会采用Block Nesated-Loop Join .中间会有一个缓冲区
join buffer，将驱动表的表所有JOIN相关的列都缓存到join buffer中，，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了费驱动表的访问频率，默认情况下 joinbuffersize =256k。在查找时会将所有需要的额列换粗到join buffer中，，包括select的列，而不是仅仅是只缓存关联列。在一个由N个JOIN关联的SQL当中会执行的时候分配N-1个join buffer.
show variables like '%join_buffer%';

当使用内连接的时候，两种方式一样
当使用左外连接时，会把左表的数据全部查出;
当使用右外连接时，会把右边的数据全部查出;

二，join 后用 on  ，and 还是 where 区别

（一），原理

当使用内连接的时候，两种方式一样

当使用左外连接时，会把左表的数据全部查出;
当使用右外连接时，会把右边的数据全部查出;

and则是通过在表连接前过来A表或者B表里面那些记录符合连接诶条件，同时会兼顾left join 还是 right join . 即假如是左连接的话，如果左边表的某个记录不符合连接条件，那么它不进行连接，但是仍然留在结果集中（此时右边部分的连接结果为null）。

on条件是在生成临时表示使用的条件，他不管on中的条件是否为真，都会返回左表中的记录。

（二），on , where实例

下面以mysql为例：

1，创建表和数据

CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 DEFAULT NULL,
  KEY `idx_test1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 DEFAULT NULL,
  KEY `idx_test2_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test1` VALUES ('1', 'test1一');
INSERT INTO `test1` VALUES ('3', 'test1三');
INSERT INTO `test2` VALUES ('1', 'test2一');
INSERT INTO `test2` VALUES ('4', 'test2四');

2，测试语句和结果

SELECT * FROM test1 LEFT  JOIN  test2 ON test1.id = test2.id;
SELECT * FROM test1 LEFT  JOIN  test2 ON test1.id = test2.id WHERE test1.id = test2.id;

SELECT * FROM test1 RIGHT JOIN  test2 ON test1.id = test2.id;
SELECT * FROM test1 RIGHT JOIN  test2 ON test1.id = test2.id WHERE test1.id = test2.id;

SELECT * FROM test1 INNER JOIN  test2 ON test1.id = test2.id;
SELECT * FROM test1 INNER JOIN  test2 ON test1.id = test2.id WHERE test1.id = test2.id;

（三），on  ， and实例

1，添加数据

INSERT INTO `test2` VALUES ('88', '恭喜发财');

INSERT INTO `test1` VALUES ('88', '恭喜发财');

2，测试语句和结果

SELECT * FROM test1 LEFT  JOIN  test2 ON test1.id = test2.id;
SELECT * FROM test1 LEFT  JOIN  test2 ON test1.id = test2.id and test1.name = test2.name;

SELECT * FROM test1 RIGHT JOIN  test2 ON test1.id = test2.id;
SELECT * FROM test1 RIGHT JOIN  test2 ON test1.id = test2.id and test1.name = test2.name;

SELECT * FROM test1 INNER JOIN  test2 ON test1.id = test2.id;
SELECT * FROM test1 INNER JOIN  test2 ON test1.id = test2.id and test1.name = test2.name;