四种联接
| left join(左联接) | 返回包括左表中的所有记录和右表中联结字段相等的记录 |
| right join(右联接) | 返回包括右表中的所有记录和左表中联结字段相等的记录 |
| inner join(等值联接) | 只返回两个表中联结字段相等的行 |
| cross join(交叉联接) | 得到的结果是两个表的乘积，即笛卡尔积 |
创建表
CREATE TABLE `product` ( `id` int(10) unsigned not null auto_increment, `amount` int(10) unsigned default null, PRIMARY KEY (`id`) ) ENGINE=innodb; |
CREATE TABLE `product_details` ( `id` int(10) unsigned not null, `weight` int(10) unsigned default null, `exist` int(10) unsigned default null, PRIMARY KEY (`id`) ) ENGINE=innodb; |
插入数据
INSERT INTO product (id,amount) VALUES (1,100),(2,200),(3,300),(4,400); INSERT INTO product_details (id,weight,exist) VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1); |
| mysql>
SELECT * FROM product; +----+--------+ | id | amount | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | 400 | +----+--------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM product_details; +----+--------+-------+| id | weight | exist | +----+--------+-------+ | 2 | 22 | 0 | | 4 | 44 | 1 | | 5 | 55 | 0 | | 6 | 66 | 1 | +----+--------+-------+ 4 rows in set (0.00 sec) |
inner join(等值联接)
mysql> select * from product a inner join product_details b on a.id=b.id; +----+--------+----+--------+-------+ | id | amount | id | weight | exist | +----+--------+----+--------+-------+ | 2 | 200 | 2 | 22 | 0 || 4 | 400 | 4 | 44 | 1 | +----+--------+----+--------+-------+ 2 rows in set (0.00 sec) |
left join（左联接）
| mysql>
select * from product a left join product_details b on a.id=b.id; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 2 | 200 | 2 | 22 | 0 | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | 4 | 44 | 1 | +----+--------+------+--------+-------+ mysql> select * from product a left join product_details b on a.id=b.id and b.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 | +----+--------+------+--------+-------+ mysql> select * from product a left join product_details b on a.id=b.id where b.id=2; +----+--------+----+--------+-------+ | id | amount | id | weight | exist |+----+--------+----+--------+-------+ | 2 | 200 | 2 | 22 | 0 | +----+--------+----+--------+-------+
mysql> select * from product a left join product_details b on a.id=b.id where a.id=3; +----+--------+------+--------+-------+ | id | amount | id | weight | exist| +----+--------+------+--------+-------+ | 3 | 300 | NULL | NULL | NULL |+----+--------+------+--------+-------+ mysql> select * from product a left join product_details b on a.id=b.id and a.id=3; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 2 | 200 | NULL | NULL | NULL | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | NULL | NULL | NULL | +----+--------+------+--------+-------+ mysql> SELECT * FROM product a LEFT JOIN product_details b ON a.id=b.id AND b.weight!=44 AND b.exist=0 WHERE b.id IS NULL; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | NULL | NULL | NULL | +----+--------+------+--------+-------+ mysql> SELECT * FROM product a LEFT JOIN product_details b ON a.id=b.id AND b.weight!=44 AND b.exist=0 WHERE b.id IS not NULL; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 2 | 200 | 2 | 22 | 0 | +----+--------+------+--------+-------+ |
right join跟left join相反，不多做解释，MySQL本身不支持所说的full join（全连接），但可以通过union来实现。
mysql> SELECT * FROM product a left join product_details b ON a.id=b.id where b.id is null union SELECT * FROM product a right join product_details b ON a.id=b.id where a.id is null; +------+--------+-----+--------+-------+ | id | amount | id | weight | exist |+------+--------+-----+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 3 | 300 | NULL | NULL | NULL | | NULL | NULL | 5 | 55 | 0 | | NULL | NULL | 6 | 66 | 1 | +------+--------+-----+--------+-------+ |
Cross join（交叉联接）
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 * from product a cross join
product_details b; +----+--------+----+--------+-------+ | id | amount | id | weight | exist | +----+--------+----+--------+-------+ | 1 | 100 | 2 | 22 | 0 | | 2 | 200 | 2 | 22 | 0 | | 3 | 300 | 2 | 22 | 0 | | 4 | 400 | 2 | 22 | 0 | | 1 | 100 | 4 | 44 | 1 | | 2 | 200 | 4 | 44 | 1 | | 3 | 300 | 4 | 44 | 1 | | 4 | 400 | 4 | 44 | 1 | | 1 | 100 | 5 | 55 | 0 | | 2 | 200 | 5 | 55 | 0 | | 3 | 300 | 5 | 55 | 0 | | 4 | 400 | 5 | 55 | 0 | | 1 | 100 | 6 | 66 | 1 | | 2 | 200 | 6 | 66 | 1 | | 3 | 300 | 6 | 66 | 1 | | 4 | 400 | 6 | 66 | 1 | +----+--------+----+--------+-------+ |
on与 where的执行顺序
ON
条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON
的条件，将会额外生成一行所有列为 NULL 的数据，在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE
子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
所以我们要注意：在使用left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。
A Left join B On a.id=b.id And b.id=2; 从B表中检索符合的所有数据行，如果没有匹配的全部为nullA Left join B On a.id=b.id Where b.id=2; 先做left join 再过滤, WHERE 条件查询发生在匹配阶段之后 |
