LEFT JOIN 实例
MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据。
以 author 为左表,article为右表。右表无对应数据自动填充为NULL:
mysql> select name, qq, phone, title, content, create_time from author as u left join article as a on u.id=a.author_id;
+--------+------------+-------------+--------------+----------------------------+---------------------+
| name | qq
| phone
| title
| content
| create_time
|
+--------+------------+-------------+--------------+----------------------------+---------------------+
| 君惜 | 2298630081 | 18500178899 | 流畅的python | Python各种拽
| 2017-09-12 16:36:41 |
| 糖糖 |
234567 | 13256987582 | 嘻哈
| 中国有嘻哈
| 2017-09-12 16:36:42 |
| 琳琳 |
345678 | 15636589521 | 严肃
| 你这辈子就是吃了太严肃的亏 | 2017-09-12 16:36:43 |
| 李天星 | 5678911 | 13345607861 | NULL
| NULL
| NULL
|
| 王星 | 5678912 | 13345607862 | NULL
| NULL
| NULL
|
| 张星星 | 5678913 | 13345607863 | NULL
| NULL
| NULL
|
+--------+------------+-------------+--------------+----------------------------+---------------------+
6 rows in set (0.00 sec)
RIGHT JOIN 实例
MySQL RIGHT JOIN 会读取右边数据表的全部数据,即便左边边表无对应数据。
以 article 为左表,author为右表,左表无对应数据自动填充为NULL。:
mysql> select title, content, create_time, name, qq, phone from article as a right join author as u on u.id=a.author_id;
+--------------+----------------------------+---------------------+--------+------------+-------------+
| title
| content
| create_time
| name | qq
| phone
|
+--------------+----------------------------+---------------------+--------+------------+-------------+
| 流畅的python | Python各种拽
| 2017-09-12 16:36:41 | 君惜 | 2298630081 | 18500178899 |
| 嘻哈
| 中国有嘻哈
| 2017-09-12 16:36:42 | 糖糖 |
234567 | 13256987582 |
| 严肃
| 你这辈子就是吃了太严肃的亏 | 2017-09-12 16:36:43 | 琳琳 |
345678 | 15636589521 |
| NULL
| NULL
| NULL
| 李天星 | 5678911 | 13345607861 |
| NULL
| NULL
| NULL
| 王星 | 5678912 | 13345607862 |
| NULL
| NULL
| NULL
| 张星星 | 5678913 | 13345607863 |
+--------------+----------------------------+---------------------+--------+------------+-------------+
6 rows in set (0.00 sec)
mysql> select title, content, create_time, name, qq, phone from article as a right join author as u on u.id=a.author_id where title is not null;
+--------------+----------------------------+---------------------+------+------------+-------------+
| title
| content
| create_time
| name | qq
| phone
|
+--------------+----------------------------+---------------------+------+------------+-------------+
| 流畅的python | Python各种拽
| 2017-09-12 16:36:41 | 君惜 | 2298630081 | 18500178899 |
| 嘻哈
| 中国有嘻哈
| 2017-09-12 16:36:42 | 糖糖 |
234567 | 13256987582 |
| 严肃
| 你这辈子就是吃了太严肃的亏 | 2017-09-12 16:36:43 | 琳琳 |
345678 | 15636589521 |
+--------------+----------------------------+---------------------+------+------------+-------------+
3 rows in set (0.00 sec)
先记录到这了。