mysql left join,right join,inner join 的区别

mysql> select * from str;
+----+-----------+-----------+
| id | string    | scenario  |
+----+-----------+-----------+
|  1 | dddd      |           | 
|  2 | ssssff    | ffff      | 
|  3 | 1231      | 23123123  | 
|  4 | 123123123 | 123123123 | 
|  5 | ssssff    | ffff      | 
|  6 | asdfas    | fffffdsdf | 
+----+-----------+-----------+
6 rows in set (0.00 sec)

mysql> select * from str1;
+------+------+
| s_id | tag  |
+------+------+
|    1 | 33   | 
|    4 | aas  | 
|    5 | asdf | 
|    7 | sdf  | 
|    8 | asdf | 
+------+------+
5 rows in set (0.00 sec)

mysql> select * from str s left join str1  t on s.id = t.s_id;
+----+-----------+-----------+------+------+
| id | string    | scenario  | s_id | tag  |
+----+-----------+-----------+------+------+
|  1 | dddd      |           |    1 | 33   | 
|  2 | ssssff    | ffff      | NULL | NULL | 
|  3 | 1231      | 23123123  | NULL | NULL | 
|  4 | 123123123 | 123123123 |    4 | aas  | 
|  5 | ssssff    | ffff      |    5 | asdf | 
|  6 | asdfas    | fffffdsdf | NULL | NULL | 
+----+-----------+-----------+------+------+
6 rows in set (0.00 sec)

mysql> select * from str s right join str1  t on s.id = t.s_id;
+------+-----------+-----------+------+------+
| id   | string    | scenario  | s_id | tag  |
+------+-----------+-----------+------+------+
|    1 | dddd      |           |    1 | 33   | 
|    4 | 123123123 | 123123123 |    4 | aas  | 
|    5 | ssssff    | ffff      |    5 | asdf | 
| NULL | NULL      | NULL      |    7 | sdf  | 
| NULL | NULL      | NULL      |    8 | asdf | 
+------+-----------+-----------+------+------+
5 rows in set (0.00 sec)

mysql> select * from str s inner join str1  t on s.id = t.s_id;
+----+-----------+-----------+------+------+
| id | string    | scenario  | s_id | tag  |
+----+-----------+-----------+------+------+
|  1 | dddd      |           |    1 | 33   | 
|  4 | 123123123 | 123123123 |    4 | aas  | 
|  5 | ssssff    | ffff      |    5 | asdf | 
+----+-----------+-----------+------+------+
3 rows in set (0.00 sec)

mysql> select * from str s inner join str1  t ;
+----+-----------+-----------+------+------+
| id | string    | scenario  | s_id | tag  |
+----+-----------+-----------+------+------+
|  1 | dddd      |           |    1 | 33   | 
|  1 | dddd      |           |    4 | aas  | 
|  1 | dddd      |           |    5 | asdf | 
|  1 | dddd      |           |    7 | sdf  | 
|  1 | dddd      |           |    8 | asdf | 
|  2 | ssssff    | ffff      |    1 | 33   | 
|  2 | ssssff    | ffff      |    4 | aas  | 
|  2 | ssssff    | ffff      |    5 | asdf | 
|  2 | ssssff    | ffff      |    7 | sdf  | 
|  2 | ssssff    | ffff      |    8 | asdf | 
|  3 | 1231      | 23123123  |    1 | 33   | 
|  3 | 1231      | 23123123  |    4 | aas  | 
|  3 | 1231      | 23123123  |    5 | asdf | 
|  3 | 1231      | 23123123  |    7 | sdf  | 
|  3 | 1231      | 23123123  |    8 | asdf | 
|  4 | 123123123 | 123123123 |    1 | 33   | 
|  4 | 123123123 | 123123123 |    4 | aas  | 
|  4 | 123123123 | 123123123 |    5 | asdf | 
|  4 | 123123123 | 123123123 |    7 | sdf  | 
|  4 | 123123123 | 123123123 |    8 | asdf | 
|  5 | ssssff    | ffff      |    1 | 33   | 
|  5 | ssssff    | ffff      |    4 | aas  | 
|  5 | ssssff    | ffff      |    5 | asdf | 
|  5 | ssssff    | ffff      |    7 | sdf  | 
|  5 | ssssff    | ffff      |    8 | asdf | 
|  6 | asdfas    | fffffdsdf |    1 | 33   | 
|  6 | asdfas    | fffffdsdf |    4 | aas  | 
|  6 | asdfas    | fffffdsdf |    5 | asdf | 
|  6 | asdfas    | fffffdsdf |    7 | sdf  | 
|  6 | asdfas    | fffffdsdf |    8 | asdf | 
+----+-----------+-----------+------+------+
30 rows in set (0.00 sec)
此条目发表在mysql分类目录。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

看不清?