1.join语法概述
join 用于多表中字段之间的联系,语法如下:
select * FROM table1 INNER|LEFT|RIGHTJOIN table2 ON condition
table1:左表;table2:右表。
JOIN 按照功能大致分为如下三类:
INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。
LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。
RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。
注意:mysql没有Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.
2.Inner join 内连接,也叫等值连接,inner join产生同时符合A和B的一组数据。
mysql> select a.id,a.name,b.id,b.name from a inner join b on a.name= b.name;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
| 2 | Jack | 2 | Jack |
+----+------+----+------+
1 row in set (0.00 sec)
3.Left Join 左连接,以左表为主表,从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。
mysql> select a.id,a.name,b.id,b.name from a left join b on a.name= b.name;
+----+------+------+------+
| id | name | id | name |
+----+------+------+------+
| 1 | Rose | NULL | NULL |
| 2 | Jack | 2 | Jack |
| 3 | Coko | NULL | NULL |
+----+------+------+------+
3 rows in set (0.00 sec)
如果想只从左表(A)中产生一套记录,但不包含右表(B)不匹配的记录,可以通过设置where语句来执行,如下:
mysql> select a.id,a.name,b.id,b.name from a left join b on a.name= b.name where b.id is not null;
+----+------+------+------+
| id | name | id | name |
+----+------+------+------+
| 2 | Jack | 2 | Jack |
+----+------+------+------+
1 row in set (0.00 sec)
结果与Inner Join效果相同。
求差集
根据上面的例子可以求差集,如下:
mysql> select a.id,a.name,b.id,b.name from a left join b on a.name=b.name where b.id is null union select a.id,a.name,b.id,b.name from a right join b on a.name = b.name where a.id is null;
+------+------+------+---------+
| id | name | id | name |
+------+------+------+---------+
| 1 | Rose | NULL | NULL |
| 3 | Coko | NULL | NULL |
| NULL | NULL | 1 | alice |
| NULL | NULL | 3 | crystal |
| NULL | NULL | 4 | lisi |
+------+------+------+---------+
5 rows in set (0.03 sec)
4.Right Join 右连接,以右表为主表,从右表(B)产生一套完整的记录,与匹配的记录(左表(A)) .如果没有匹配,左侧将包含null。
mysql> select a.id,a.name,b.id,b.name from a right join b on a.name = b.name;
+------+------+----+---------+
| id | name | id | name |
+------+------+----+---------+
| NULL | NULL | 1 | alice |
| 2 | Jack | 2 | Jack |
| NULL | NULL | 3 | crystal |
| NULL | NULL | 4 | lisi |
+------+------+----+---------+
4 rows in set (0.00 sec)
5.Cross Join 交叉连接,得到的结果是两个表的乘积,即笛卡尔积
笛卡尔(Descartes)乘积又叫直积。假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况。
mysql> select a.id,a.name,b.id,b.name from a cross join b ;
+----+------+----+---------+
| id | name | id | name |
+----+------+----+---------+
| 1 | Rose | 1 | alice |
| 2 | Jack | 1 | alice |
| 3 | Coko | 1 | alice |
| 1 | Rose | 2 | Jack |
| 2 | Jack | 2 | Jack |
| 3 | Coko | 2 | Jack |
| 1 | Rose | 3 | crystal |
| 2 | Jack | 3 | crystal |
| 3 | Coko | 3 | crystal |
| 1 | Rose | 4 | lisi |
| 2 | Jack | 4 | lisi |
| 3 | Coko | 4 | lisi |
+----+------+----+---------+
12 rows in set (0.00 sec)
#执行:mysql> select a.id,a.name,b.id,b.name from a inner join b;结果与cross join 完全相同;
#执行:mysql> select a.id,a.name,b.id,b.name from a cross join b on a.name=b.name;结果与
select a.id,a.name,b.id,b.name from a inner join b on a.name= b.name;相同;
实际上,在 MySQL 中(仅限于 MySQL) CROSS JOIN 与 INNER JOIN 的表现是一样的,在不指定 ON 条件得到的结果都是笛卡尔积,反之取得两个表完全匹配的结果。
INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字,因此下面的 SQL 效果是一样的:
select * FROM a INNERJOIN b
select * FROM a CROSSJOIN b
select * FROM a JOIN b
6.Full join 全连接,全连接产生的所有记录(双方匹配记录)在表A和表B。如果没有匹配,则对面将包含null
mysql> select a.id,a.name,b.id,b.name from a left join b on a.name=b.name union select a.id,a.name,b.id,b.name from a right join b on a.name=b.name;
+------+------+------+---------+
| id | name | id | name |
+------+------+------+---------+
| 1 | Rose | NULL | NULL |
| 2 | Jack | 2 | Jack |
| 3 | Coko | NULL | NULL |
| NULL | NULL | 1 | alice |
| NULL | NULL | 3 | crystal |
| NULL | NULL | 4 | lisi |
+------+------+------+---------+
6 rows in set (0.00 sec)
7.性能优化
7.1显式(explicit) inner join / 隐式(implicit) inner join
显式:mysql> select * from a inner join b on a.name=b.name;
隐式:mysql> select * from a,b where a.name = b.name;
在数据库(大数据)中比较得之,它们用时几乎相同,第一个是显示的inner join,后一个是隐式的inner join。
7.2 left join/right join / inner join
7.2.1 尽量用inner join,避免left join 和null
7.2.2 在使用left join/right join时,应该知道一下几点:
(1). on与 where的执行顺
ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有 列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。
例子:
select *from Ainnerjoin B on B.name = A.nameleftjoin Con C.name = B.nameleftjoin Don D.id = C.idwhere C.status>1and D.status=1;
优化sql
select *from Ainner join Bon B.name = A.nameleft join Con C.name = B.nameand C.status>1left join Don D.id = C.idand D.status=1
从上面例子可以看出,尽可能满足ON的条件,而少用Where的条件。从执行性能来看第二个显然更加省时。
(2).注意ON 子句和 WHERE 子句的不同
例子:
mysql> select a.id,a.name,b.id,b.name from a left join b on (a.id = b.id) and b.id =2;
+----+------+------+------+
| id | name | id | name |
+----+------+------+------+
| 1 | Rose | NULL | NULL |
| 2 | Jack | 2 | Jack |
| 3 | Coko | NULL | NULL |
+----+------+------+------+
3 rows in set (0.00 sec)
mysql> select a.id,a.name,b.id,b.name from a left join b on (a.id = b.id) where b.id =2;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
| 2 | Jack | 2 | Jack |
+----+------+----+------+
1 row in set (0.00 sec)
从上可知,第一条查询使用 ON 条件决定了从 LEFT JOIN的b表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN,然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。
7.2.3 straight_join实现强制多表的载入顺序,从左到右,如:
select * from A straight_join B on A.name = B.name ;
select * from A straight_join B on A.name = B.name ;
straight_join完全等同于inner join ,只不过inner join语法是根据“哪个表的结果集小,就以哪个表为驱动表”来决定谁先载入的,straight_join 会强制选择其左边的表先载入。
往往我们在分析mysql处理性能时,如(Explain),如果发现mysql在载入顺序不合理的情况下,可以使用这个语句,但往往mysql能够自动的分析并处理好。
By Alice