mysql dba系统学习（23）必须明白的sql知识

一，两表外连接查询

现有两个表A,B内容如下

mysql> select * from A;
+------+------+
| id   | Col1 |
+------+------+
|    1 | AA   |
|    2 | BB   |
|    3 | CC   |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from B;
+------+------+
| id   | Col2 |
+------+------+
|    2 | DD   |
|    3 | EE   |
|    4 | FF   |
+------+------+
3 rows in set (0.01 sec)

1，A表和B表左连接

先将左表（A）数据查出，然后根据on后面的条件，将右表中凡是id与左表id相等的记录都查出来，与匹配的左表记录依次排成一行或多行，若无匹配的记录，则显示null。

mysql> select * from A left join B on A.id=B.id;
+------+------+------+------+
| id   | Col1 | id   | Col2 |
+------+------+------+------+
|    1 | AA   | NULL | NULL |
|    2 | BB   |    2 | DD   |
|    3 | CC   |    3 | EE   |
+------+------+------+------+
3 rows in set (0.00 sec)
mysql> select A.id ID ,A.Col1 C1 ,B.Col2 C2  from A left join B on A.id=B.id;
+------+------+------+
| ID   | C1   | C2   |
+------+------+------+
|    1 | AA   | NULL |
|    2 | BB   | DD   |
|    3 | CC   | EE   |
+------+------+------+
3 rows in set (0.00 sec)

下面的结果也是一样的

2，A表和B表右连接

先将右表（B）数据查出，然后根据on后面的条件，将左表中凡是id与右表id相等的记录都查出来，与匹配的左表记录依次排成一行或多行，若无匹配的记录，则显示null

mysql> select * from A right join B on A.id=B.id;
+------+------+------+------+
| id   | Col1 | id   | Col2 |
+------+------+------+------+
|    2 | BB   |    2 | DD   |
|    3 | CC   |    3 | EE   |
| NULL | NULL |    4 | FF   |
+------+------+------+------+
3 rows in set (0.07 sec)
mysql> select A.id ID ,A.Col1 C1 ,B.Col2 C2  from A right  join B on A.id=B.id;
+------+------+------+
| ID   | C1   | C2   |
+------+------+------+
|    2 | BB   | DD   |
|    3 | CC   | EE   |
| NULL | NULL | FF   |
+------+------+------+
3 rows in set (0.00 sec)

（编辑：源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!