join用法和性能浅析

一、JOIN用法

JOIN 分为：内连接（INNER JOIN）、外连接（OUTER JOIN）。其中，外连接分为：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN），其中外连接的“OUTER”关键字可以省略不写。

例：
表A有列ID，值为：
1
2
3
4

表B有列ID，值为：
3
4
5
6

1.内连接（显示左右两表能完全匹配的数据）：

select A.ID, B.ID from A INNER JOIN B ON A.ID = B.ID

结果为：
3 3
4 4

2.左外连接（显示左表所有数据，右表匹配不上的显示为NULL）：

select A.ID, B.ID from A LEFT JOIN B ON A.ID = B.ID

结果为：
1 NULL
2 NULL
3 3
4 4

如果想只从左表(A)中产生一套记录，但不包含右表(B)的记录，可以通过设置where语句来执行，如下：

select A.ID, B.ID from A LEFT JOIN B ON A.ID = B.ID WHERE A.ID is NULL or B.ID is NULL

结果为：
1 NULL
2 NULL

3.右外连接（显示右表所有数据，左表匹配不上的显示为NULL）：

select A.ID, B.ID from A RIGHT JOIN B ON A.ID = B.ID

结果为：
3 3
4 4
NULL 5
NULL 6

同LEFT JOIN。

4.全外连接（显示左右两量表所有数据，两表匹配不上的显示为NULL）：

select A.ID, B.ID from A FULL OUTER JOIN B ON A.ID = B.ID

结果为：
1 NULL
2 NULL
3 3
4 4
NULL 5
NULL 6

二、性能优化

left join/right join VS inner join

尽量用inner join.避免 LEFT JOIN 和 NULL.
在使用left join（或right join）时，应该清楚的知道以下几点：

1. on与 where的执行顺序
ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。如：

使用where：

select * from A
inner join B on B.id = A.id
left join C on C.id = B.id
left join D on D.id = C.id
where C.id>1 and D.id=1;

不使用where：

select * from A
inner join B on B.id = A.id
left join C on C.id = B.id and C.id>1
left join D on D.id = C.id and D.id=1

从上面例子可以看出，尽可能满足ON的条件，而少用Where的条件。从执行性能来看第二个显然更加省时。

2.注意ON 子句和 WHERE 子句的不同

mysql> 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 |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
mysql> 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 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)

从上可知，第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

3.尽量避免子查询，而用join

往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询。如下：
Pass

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id); 

Creat

insert into t1(a1)  
select b1 from t2  
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   
where t1.id is null;