关于连接查询
- 在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询，多表查询就是同时查询两个或两个以上的表。
- 多表查询一般使用内连接和外连接，它们的效率要高于交叉连接。
一、交叉连接
- 交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积。
1. 笛卡尔积
user表
hosts表
在mysql中，笛卡尔积的写法为
select * from A,B # 大部分的写法，且推荐 # 或者 select * from A cross join B
到此为止还是没说清楚笛卡尔积到底是什么鬼
- 笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。例如，有 A 和 B 两个集合，它们的值如下：
A = {1,2} B = {3,4,5}
集合 A×B 和 B×A 的结果集分别表示为：
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) }; B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };
以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。
并且，从以上结果我们可以看出：
- 两个集合相乘，不满足交换率，即 A×B≠B×A。
- A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。
多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱。
再回到user表和hosts表
将user表和hosts表转化成2个集合
# user集合 { (ttr1,Admin), (ttr2,Admin), (ttr3,Admin), (ttr4,Admin) } # hosts集合 { (web01,192.168.11.10), (web02,192.168.11.11), (web03,192.168.11.12), (web04,192.168.11.13), (web05,192.168.11.14), (web06,192.168.11.15), (web07,192.168.11.16) }
两个集合的笛卡尔积为
笛卡尔（Descartes）乘积又叫直积。再次巩固一下概念，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}，遵循笛卡尔积的算法，就可以知道user集合、hosts集合的笛卡尔积是多少了。
类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。
2. 交叉连接查询案例
案例：使用 CROSS JOIN 查询出两张表中的笛卡尔积，SQL 语句和运行结果如下：
mysql> select * from zabbix.items, zabbix.hosts;
由运行结果可以看出，items 和 hosts 表交叉连接查询后，返回了 N 条记录。可以想象，当表中的数据较多时，得到的运行结果会非常长，而且得到的运行结果也没太大的意义。所以，通过交叉连接的方式进行多表查询的这种方法并不常用，我们应该尽量避免这种查询。
案例：查询 items 表中的 hostid 字段和 hosts 表中的 hostid 字段相等、且hosts表的status字段等于0的内容， SQL 语句和运行结果如下：
select i.name,h.name from zabbix.items as i cross join zabbix.hosts as h where h.status=0 and i.hostid=h.hostid; # 或 select i.name,h.name from zabbix.items as i, zabbix.hosts as h where h.status=0 and i.hostid=h.hostid;
如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接。在 MySQL 中，多表查询一般使用内连接和外连接，它们的效率要高于交叉连接。
二、内连接
内连接（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。
内连接使用 INNER JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件。如果没有连接条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。
案例：在 hosts 表和 interface 表之间，使用内连接查询主机名称和相对应的ip地址，SQL 语句和运行结果如下。
mysql> select h.name,i.ip from zabbix.hosts h inner join zabbix.interface i on h.hostid=i.hostid; +--------------+----------------+ | name | ip | +--------------+----------------+ | zbxser01 | 192.168.11.153 | | mysql-master | 192.168.11.151 | | mysql-db02 | 192.168.11.152 | | zbxser02 | 192.168.11.154 | | zbxproxy01 | 192.168.11.155 | | zbxproxy02 | 192.168.11.156 | | zbxproxy04 | 192.168.11.158 | | zbxproxy03 | 192.168.11.157 | +--------------+----------------+ 8 rows in set (0.00 sec) # inner可省略 mysql> select h.name,i.ip from zabbix.hosts h join zabbix.interface i on h.hostid=i.hostid;
在这里的查询语句中，两个表之间的关系通过 INNER JOIN 指定，连接的条件使用 ON 子句给出。
注意：当对多个表进行查询时，要在 SELECT 语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT 语句后面的写法是表名.列名。另外，如果表名非常长的话，也可以给表设置别名，这样就可以直接在 SELECT 语句后面写上表的别名.列名。
案例：使用内连接查询主机和相对应的监控模板名称，并进行分组显示
select h.name,group_concat(i.name) from zabbix.hosts h inner join zabbix.items i on h.status=0 and h.hostid=i.hostid group by h.name\G;
INNER JOIN 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN ... ON 语法是官方的标准写法，而且 WHERE 子句在某些时候会影响查询的性能。
多个表内连接时，在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。
三、外连接
- 外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。
- 外连接可以分为左外连接和右外连接
1. 左外连接
左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
左连接的语法格式如下：
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
语法说明如下：
- 字段名：需要查询的字段名称。
- <表1><表2>：需要左连接的表名。
- LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
- ON 子句：用来设置左连接的连接条件，不能省略。
上述语法中，“表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）。
案例：在 hosts 表和 interface 表中查询所有主机名称和相对应的ip地址，包括没有ip地址的模板名称，SQL 语句和运行结果如下：
- 以hosts表为基表，interface表为参照表
mysql> select h.name,i.ip from zabbix.hosts h left join zabbix.interface i on h.hostid=i.hostid; +-----------------------------------------------------------------------------+----------------+ | name | ip | +-----------------------------------------------------------------------------+----------------+ ... | Template VM VMware | NULL | | Template VM VMware Guest | NULL | | Template VM VMware Hypervisor | NULL | | Template VM VMware macros | NULL | | mysql-db02 | 192.168.11.152 | | mysql-master | 192.168.11.151 | | zbxproxy01 | 192.168.11.155 | | zbxproxy02 | 192.168.11.156 | | zbxproxy03 | 192.168.11.157 | | zbxproxy04 | 192.168.11.158 | | zbxser01 | 192.168.11.153 | | zbxser02 | 192.168.11.154 | | {#HV.NAME} | NULL | | {#VM.NAME} | NULL | +-----------------------------------------------------------------------------+----------------+ ...
可以看到，第1条记录Template VM VMware 没有 ip地址（它其实是模板名称），因为对应的 interface 表中没有该“主机”的ip地址信息，所以该条记录只取出了 hosts 表中相应的值“Template VM VMware”，而从 interface 表中取出的值为 NULL。
2. 右外连接
右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
右连接的语法格式如下：
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
语法说明如下。
- 字段名：需要查询的字段名称。
- <表1><表2>：需要右连接的表名。
- RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
- ON 子句：用来设置右连接的连接条件，不能省略。
与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。
案例：在 hosts 表和 interface 表中查询所有主机名称和相对应的ip地址，包括没有ip地址的模板名称，SQL 语句和运行结果如下：
- 以interface表为基表，hosts表为参照表
mysql> select h.name,i.ip from zabbix.hosts h right join zabbix.interface i on h.hostid=i.hostid; +--------------+----------------+ | name | ip | +--------------+----------------+ | zbxser01 | 192.168.11.153 | | mysql-master | 192.168.11.151 | | mysql-db02 | 192.168.11.152 | | zbxser02 | 192.168.11.154 | | zbxproxy01 | 192.168.11.155 | | zbxproxy02 | 192.168.11.156 | | zbxproxy04 | 192.168.11.158 | | zbxproxy03 | 192.168.11.157 | +--------------+----------------+ 8 rows in set (0.00 sec) mysql>
注意：使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。
多个表左/右连接时，在 ON 子句后连续使用 LEFT/RIGHT OUTER JOIN 或 LEFT/RIGHT JOIN 即可。
