sql in 改写 join的几种方法

1. 创建测试数据

create table t1 (id int primary key ,name varchar(10));

insert into t1 values (1,'a');
insert into t1 values (2,'b');

create table t2 (id int primary key ,name varchar(10));
insert into t2 values (1,'a');
insert into t2 values (2,'b');

create table t3 (id int  ,name varchar(10));
insert into t3 values (1,'a');
insert into t3 values (1,'a');
insert into t3 values (2,'b');
insert into t3 values (2,'b');
insert into t3 values (null,'n');

MySQL [test]> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.01 sec)

MySQL [test]> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

MySQL [test]> select * from t3;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    1 | a    |
|    2 | b    |
|    2 | b    |
| NULL | n    |
+------+------+
4 rows in set (0.00 sec)

2. in 改写 join 情况1 ，一比一的关系

在这里插入情况1 ，一比一的关系 

select * from  t1 where t1.id in (select id from t2);

+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+


select * from  t1,t2  where t1.id = t2.id;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+代码片

3. in 改写 join 情况1 ，一对多的关系

select * from  t1 where t1.id in (select id from t3);
+----+------+
| id | name |
+----+------+
|  2 | b    |
|  1 | a    |
+----+------+

正确改写 inner join 
 select t1.* from  t1  inner join (select distinct(id) from t3) tt where t1.id = tt.id;
+----+------+
| id | name |
+----+------+
|  2 | b    |
|  1 | a    |
+----+------+

正确改写 left  join 
 select t1.* from  t1  left join  (select distinct(id) from  t3) tt on  t1.id = tt.id;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+




错误改写 
select t1.* from  (select distinct(id) from t3) tt left join t1  on t1.id = tt.id and  tt.id is not  null;
+------+------+
| id   | name |
+------+------+
| NULL | NULL |
|    2 | b    |
|    1 | a    |
+------+------+
错误改写
 select t1.* from  t1 left join t3 on t1.id=t3.id;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  1 | a    |
|  2 | b    |
|  2 | b    |
+----+------+


错误改写
select * from  t1,t3  where t1.id = t3.id;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  1 | a    |
|  2 | b    |
|  2 | b    |
+----+------+