四种联接
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;
+----+--------+------+--------+-------+
| 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 |
+----+--------+------+--------+-------+
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.idAnd b.id=2;从B表中检索符合的所有数据行,如果没有匹配的全部为null A Left join B On a.id=b.idWhere b.id=2;先做left join 再过滤, WHERE 条件查询发生在匹配阶段之后 |
---|