mysql数据库左/右 内/外连接使用方法

(1).表A和B的描述

mysql> desc A;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI |         |       |
| name  | varchar(128)     | NO   |     |         |       |
+-------+------------------+------+-----+---------+-------+

mysql> desc B;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI |         |       |
| score | int(10) unsigned | YES  |     | 0       |       |
+-------+------------------+------+-----+---------+-------+

(2).表A和B中的内容

mysql> select * from A ;

+----+------+
| id | name |
+----+------+
|  1 | yao  |
|  2 | yao1 |
|  3 | yao3 |
|  4 | yao4 |
+----+------+

mysql> select * from B;
+----+-------+
| id | score |
+----+-------+
|  1 |   100 |
|  2 |    90 |
|  3 |    80 |
+----+-------+

(3)左外连接

mysql> select * from A left outer join B on A.id=B.id;
+----+------+------+-------+
| id | name | id   | score |
+----+------+------+-------+
|  1 | yao  |    1 |   100 |
|  2 | yao1 |    2 |    90 |
|  3 | yao3 |    3 |    80 |
|  4 | yao4 | NULL |  NULL |
+----+------+------+-------+

(4)左连接

mysql> select * from A left join B on A.id=B.id;      
+----+------+------+-------+
| id | name | id   | score |
+----+------+------+-------+
|  1 | yao  |    1 |   100 |
|  2 | yao1 |    2 |    90 |
|  3 | yao3 |    3 |    80 |
|  4 | yao4 | NULL |  NULL |
+----+------+------+-------+

(5)右外连接

mysql> select * from A right outer join B on A.id=B.id;    
+------+------+----+-------+
| id   | name | id | score |
+------+------+----+-------+
|    1 | yao  |  1 |   100 |
|    2 | yao1 |  2 |    90 |
|    3 | yao3 |  3 |    80 |
+------+------+----+-------+

(6)右连接

mysql> select * from A right join B on A.id=B.id;      
+------+------+----+-------+
| id   | name | id | score |
+------+------+----+-------+
|    1 | yao  |  1 |   100 |
|    2 | yao1 |  2 |    90 |
|    3 | yao3 |  3 |    80 |
+------+------+----+-------+
//

1.表A的内容

mysql> select * from A;
+----+------+
| id | name |
+----+------+
|  1 | yao  |
|  2 | yao1 |
|  3 | yao3 |
|  4 | yao4 |
+----+------+

2.表B的内容

mysql> select * from B;
+----+-------+
| id | score |
+----+-------+
|  1 |   100 |
|  2 |    90 |
|  3 |    80 |
|  5 |    70 |
+----+-------+

3.内连接

mysql> select * from A inner join B on A.id=B.id;    
+----+------+----+-------+
| id | name | id | score |
+----+------+----+-------+
|  1 | yao  |  1 |   100 |
|  2 | yao1 |  2 |    90 |
|  3 | yao3 |  3 |    80 |
+----+------+----+-------+

4.左边接

mysql> select * from A left join B on A.id=B.id;
+----+------+------+-------+
| id | name | id   | score |
+----+------+------+-------+
|  1 | yao  |    1 |   100 |
|  2 | yao1 |    2 |    90 |
|  3 | yao3 |    3 |    80 |
|  4 | yao4 | NULL |  NULL |
+----+------+------+-------+

5.左外连接

mysql> select * from A left outer join  B on A.id=B.id;
+----+------+------+-------+
| id | name | id   | score |
+----+------+------+-------+
|  1 | yao  |    1 |   100 |
|  2 | yao1 |    2 |    90 |
|  3 | yao3 |    3 |    80 |
|  4 | yao4 | NULL |  NULL |
+----+------+------+-------+

6.右连接

mysql> select * from A right join B on A.id=B.id;
+------+------+----+-------+
| id   | name | id | score |
+------+------+----+-------+
|    1 | yao  |  1 |   100 |
|    2 | yao1 |  2 |    90 |
|    3 | yao3 |  3 |    80 |
| NULL | NULL |  5 |    70 |
+------+------+----+-------+

7.右外连接

mysql> select * from A right outer join B on A.id=B.id;
+------+------+----+-------+
| id   | name | id | score |
+------+------+----+-------+
|    1 | yao  |  1 |   100 |
|    2 | yao1 |  2 |    90 |
|    3 | yao3 |  3 |    80 |
| NULL | NULL |  5 |    70 |
+------+------+----+-------+

8.交叉连接

mysql> select * from A corss join B;
+----+------+----+-------+
| id | name | id | score |
+----+------+----+-------+
|  1 | yao  |  1 |   100 |
|  2 | yao1 |  1 |   100 |
|  3 | yao3 |  1 |   100 |
|  4 | yao4 |  1 |   100 |
|  1 | yao  |  2 |    90 |
|  2 | yao1 |  2 |    90 |
|  3 | yao3 |  2 |    90 |
|  4 | yao4 |  2 |    90 |
|  1 | yao  |  3 |    80 |
|  2 | yao1 |  3 |    80 |
|  3 | yao3 |  3 |    80 |
|  4 | yao4 |  3 |    80 |
|  1 | yao  |  5 |    70 |
|  2 | yao1 |  5 |    70 |
|  3 | yao3 |  5 |    70 |
|  4 | yao4 |  5 |    70 |
+----+------+----+-------+

总结

1.内连接结果集中只保留on条件匹配上的记录.

2.外连接保留左/右表中所有记录,不匹配的记录补NULL

3.左连接与左外连接无区别;右连接与右外连接无区别

ps:实验没有验证到的地方或者有误的地方欢迎大家拍砖