MySQL数据库(9)----使用连接实现多表检索

　　有许多演示如何使用MySQL所支持的连接操作的示例，都用到了下列两个表 t1 和 t2:

mysql> SELECT * FROM t1;
+----+------+
| i1 | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+


mysql> SELECT * FROM t2;
+----+------+
| i2 | c2   |
+----+------+
|  2 | c    |
|  3 | b    |
|  4 | a    |
+----+------+

　　由于这两个表都很小，因此我们可以很清楚地看到每种类型的连接操作所执行的效果。

 

1.内连接

1.1　　如果SELECT 语句的 FROM 子句里列出了过个表的名字，并且这些名字是由 INNER JOIN 隔开的，那么 MySQL 就会执行一个内连接(inner join)操作----它会把一个表里的行与另一个表里的行进行匹配，然后产生结果。例如，如果像下面这样把 t1 和 t2 连接起来，那么 t1 里的每一行将于 t2 里的每一行进行组合：

mysql> SELECT * FROM t1 INNER JOIN t2;
+----+------+----+------+
| i1 | c1   | i2 | c2   |
+----+------+----+------+
|  1 | a    |  2 | c    |
|  2 | b    |  2 | c    |
|  3 | c    |  2 | c    |
|  1 | a    |  3 | b    |
|  2 | b    |  3 | b    |
|  3 | c    |  3 | b    |
|  1 | a    |  4 | a    |
|  2 | b    |  4 | a    |
|  3 | c    |  4 | a    |
+----+------+----+------+

　　上面这条语句也可以写成：

mysql> SELECT t1.* ,t2.* FROM t1 INNER JOIN t2;
+----+------+----+------+
| i1 | c1   | i2 | c2   |
+----+------+----+------+
|  1 | a    |  2 | c    |
|  2 | b    |  2 | c    |
|  3 | c    |  2 | c    |
|  1 | a    |  3 | b    |
|  2 | b    |  3 | b    |
|  3 | c    |  3 | b    |
|  1 | a    |  4 | a    |
|  2 | b    |  4 | a    |
|  3 | c    |  4 | a    |
+----+------+----+------+

　　根据某个表里的每一行与另一个表里的每一行得到的全部可能组合的连接操作，叫做生成 '笛卡尔积'(cartesian product)。按照这种方式连接表，很可能会产生数量巨大的行。如果增加 WHERE 子句，实现表之间基于某些列值的匹配，那么整个连接操作只会选取那些列值彼此相等的行：

mysql> SELECT t1.* , t2.* FROM t1 INNER JOIN t2 WHERE t1.i1=t2.i2;
+----+------+----+------+
| i1 | c1   | i2 | c2   |
+----+------+----+------+
|  2 | b    |  2 | c    |
|  3 | c    |  3 | b    |
+----+------+----+------+

连接类型 CROSS JOIN 和 JOIN 都等同于 INNER JOIN ，因此下面的语句时等价的：

SELECT t1.* , t2.* FROM t1 INNER JOIN t2 WHERE t1.i1=t2.i2;

SELECT t1.* , t2.* FROM t1 CROSS JOIN t2 WHERE t1.i1=t2.i2;

SELECT t1.* , t2.* FROM t1 JOIN t2 WHERE t1.i1=t2.i2;

　　

　　在指定如何对表里的列进行匹配方面，INNER JOIN、CROSS JOIN 和 JOIN (未包括逗号运算符) 还支持另外几种语法格式：

　　(i) 有一种语法是用 ON 子句代替 WHERE 子句。下面示例使用 INNER JOIN 展示了这一点：

SELECT  t1.* , t2.* FROM t1 INNER JOIN t2 ON t1.i1=t2.i2;

　　不管被连接的列是否同名，都可以使用ON。

　　(ii) 另一种语法是使用 USING() 子句。它在概念上类似于 ON 子句，但要求被连接的列必须同名。例如，下面这条查询语句可以将 mytbl1.b 连接到 mytbl2.b:

SELECT mytbl1.* , mytbl2.* FROM mytbl1 INNER JOIN mytbl2 USING(b);

 

1.2 对被连接的表里的列引用进行限定

　　如果被连接的表具有相同名字的列 b ，那么引用相同的列时必须将它限定为 mytbl1.b 或 mytbl2.b:

SELECT a, mytbl1.b, mytbl2.b, c FROM mytbl1 INNER JOIN mytbl2 ... ;

　　有时候，用表的名字进行限定仍不足以解决列的歧义问题。例如，用户正在进行一个自连接操作(即把一个表与它自身连接起来)，那么这会在同一条查询命令里多次用到同一个表，此时使用表明来限定列名已经无济于事。在这种情况下，可以使用表的别名来解决问题。只需为该表的某个实例取一个别名，便可以通过 alias_name.col_name 语法来引用该实例里的列。例如：

mysql> SELECT t1.i1, t.c1 from t1 inner join t1 as t
    -> where t1.i1 > t.i1;
+----+------+
| i1 | c1   |
+----+------+
|  2 | a    |
|  3 | a    |
|  3 | b    |
+----+------+

 

2. 左(外)连接和右(外)连接

2.1　　内连接只会显示在两个表里都匹配上的行。外连接除了显示同样的匹配结果，还可以把其中一个表在另一个表里没有匹配的行业显示出来。外连接分为 LEFT JOIN 和 RIGHT JOIN。LEFT JOIN 会把左表里的在右表里未匹配上的行也显示出来。RIGHT JOIN 在表的顺序上刚好与之相反。

　　LEFT JOIN 的工作方式是：先指定用于对两个表里的行进行匹配的列；接着，当左表的某行与右表的某行相匹配时，两行的内容会被选取为一个输出行；当左表的某行在右表里无匹配时，它仍会被选为一个输出行，只是与它连接的是右表的一个“假”行，其中的每一列都包含“NULL”。

　　例如：

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.i1=t2.i2;
+----+------+------+------+
| i1 | c1   | i2   | c2   |
+----+------+------+------+
|  2 | b    |    2 | c    |
|  3 | c    |    3 | b    |
|  1 | a    | NULL | NULL |
+----+------+------+------+

　　注意：在使用 LEFT JOIN 时，只有将右表里的列全部定义成 NOT NULL ，结果集里的行才会没有问题。例如，右表里包含值为 NULL 的列，那么你就无法将这些 NULL 值与标识为匹配行的那些 NULL 值区别开来。

　　RIGHT JOIN 与 LEFT JOIN 很相似，只是表的角色需要对换一下：

mysql> SELECT * FROM t1 RIGHT JOIN t2 ON t1.i1=t2.i2;
+------+------+----+------+
| i1   | c1   | i2 | c2   |
+------+------+----+------+
|    2 | b    |  2 | c    |
|    3 | c    |  3 | b    |
| NULL | NULL |  4 | a    |
+------+------+----+------+

2.2 当只想将左表里的那些在右表里无匹配的行找出来时，LEFT JOIN 非常有用。要实现这个功能，只需增加一条 WHERE 子句，让它把右表列值为 NULL 的行筛选出来：

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.i1=t2.i2
    -> WHERE t2.i2 IS NULL;
+----+------+------+------+
| i1 | c1   | i2   | c2   |
+----+------+------+------+
|  1 | a    | NULL | NULL |
+----+------+------+------+

　　上例中，把来自右表值为 NULL 的列显示出来毫无意义，因此可以把它们从输出列的列表里省略掉：

mysql> SELECT t1.i1 FROM t1 LEFT JOIN t2 ON t1.i1=t2.i2 WHERE t2.i2 IS NULL;
+----+
| i1 |
+----+
|  1 |
+----+