连接查询

连接查询

左连接查询
格式：
select 字段名列表 from
表a left join 表b on 条件；

以左表为主，显示查询结果。可以自己决定谁做左表。在上面，表a是左表。

右连接查询
格式：
select 字段名列表 from
表a right join 表b on 条件；

以右表为主，显示查询结果。可以自己决定谁做右表。在上面，表b是右表。

mysql> create table db4.t4
-> select name,uid,shell,homedir from db3.user
-> limit 8;

mysql> create table db4.t5
-> select name,uid,shell,homedir from db3.user
-> limit 12;

不管是左连接还是右连接，
查相同的记录时，以较少记录的表为主。
查不同的记录时，以较多记录的表为主。

查相同记录时，以记录少的表为标准来找，才比较容易！
查不同记录时，以记录多的表为标准来找！

mysql> select * from t4 left join t5
-> on
-> t4.uid=t5.uid;
+----------+------+----------------+----------------+----------+------+----------------+----------------+
| name | uid | shell | homedir | name | uid | shell | homedir |
+----------+------+----------------+----------------+----------+------+----------------+----------------+
| root | 1 | /bin/bash | /root | root | 1 | /bin/bash | /root |
| bin | 2 | /sbin/nologin | /bin | bin | 2 | /sbin/nologin | /bin |
| daemon | 3 | /sbin/nologin | /sbin | daemon | 3 | /sbin/nologin | /sbin |
| adm | 4 | /sbin/nologin | /var/adm | adm | 4 | /sbin/nologin | /var/adm |
| lp | 5 | /sbin/nologin | /var/spool/lpd | lp | 5 | /sbin/nologin | /var/spool/lpd |
| sync | 6 | /bin/sync | /sbin | sync | 6 | /bin/sync | /sbin |
| shutdown | 7 | /sbin/shutdown | /sbin | shutdown | 7 | /sbin/shutdown | /sbin |
| halt | 8 | /sbin/halt | /sbin | halt | 8 | /sbin/halt | /sbin |
+----------+------+----------------+----------------+----------+------+----------------+----------------+
8 rows in set (0.00 sec)

mysql> select t4.name,t5.name,t4.uid,t5.uid from t4 left join t5 on t4.uid=t5.uid;
+----------+----------+------+------+
| name | name | uid | uid |
+----------+----------+------+------+
| root | root | 1 | 1 |
| bin | bin | 2 | 2 |
| daemon | daemon | 3 | 3 |
| adm | adm | 4 | 4 |
| lp | lp | 5 | 5 |
| sync | sync | 6 | 6 |
| shutdown | shutdown | 7 | 7 |
| halt | halt | 8 | 8 |
+----------+----------+------+------+
8 rows in set (0.00 sec)

mysql> select * from t4 right join t5 on t4.uid=t5.uid; 显示不相同的，用右连接
+----------+------+----------------+----------------+----------+------+----------------+-----------------+
| name | uid | shell | homedir | name | uid | shell | homedir |
+----------+------+----------------+----------------+----------+------+----------------+-----------------+
| root | 1 | /bin/bash | /root | root | 1 | /bin/bash | /root |
| bin | 2 | /sbin/nologin | /bin | bin | 2 | /sbin/nologin | /bin |
| daemon | 3 | /sbin/nologin | /sbin | daemon | 3 | /sbin/nologin | /sbin |
| adm | 4 | /sbin/nologin | /var/adm | adm | 4 | /sbin/nologin | /var/adm |
| lp | 5 | /sbin/nologin | /var/spool/lpd | lp | 5 | /sbin/nologin | /var/spool/lpd |
| sync | 6 | /bin/sync | /sbin | sync | 6 | /bin/sync | /sbin |
| shutdown | 7 | /sbin/shutdown | /sbin | shutdown | 7 | /sbin/shutdown | /sbin |
| halt | 8 | /sbin/halt | /sbin | halt | 8 | /sbin/halt | /sbin |
| NULL | NULL | NULL | NULL | mail | 9 | /sbin/nologin | /var/spool/mail | t4没有的会写NULL
| NULL | NULL | NULL | NULL | operator | 12 | /sbin/nologin | /root |
| NULL | NULL | NULL | NULL | games | 13 | /sbin/nologin | /usr/games |
| NULL | NULL | NULL | NULL | ftp | 15 | /sbin/nologin | /var/ftp |
+----------+------+----------------+----------------+----------+------+----------------+-----------------+
12 rows in set (0.00 sec)

mysql> select t4.name,t5.name from t4 right join t5 on t4.uid=t5.uid;
+----------+----------+
| name | name |
+----------+----------+
| root | root |
| bin | bin |
| daemon | daemon |
| adm | adm |
| lp | lp |
| sync | sync |
| shutdown | shutdown |
| halt | halt |
| NULL | mail |
| NULL | operator |
| NULL | games |
| NULL | ftp |
+----------+----------+
12 rows in set (0.00 sec)