mysql---多表查询

很多应用场景下，数据查询会涉及多张表，即多表查询。这些表之间存在连结关系。
多表连结分为：内连结，外连结, 交叉连结。
针对内外连结对应不同的连结方法

• 内连结：(inner)join
• 外连结：left(outer) join, right(outer) join, union
• 交叉连结：cross join

其中，
1. join和inner join是等价的
2. left join和left outer join是等价的，right join和right outer join是等价的；
3. union本来不属于连结方法，用来组合其他连结方法实现新的连结方法。

下面通过实例对这些连结方法进行描述，首先建立两张表

表a:
+------+---------+
| c_id | country |
+------+---------+
|    1 | CHINA   |
|    2 | UK      |
|    3 | USA     |
+------+---------+

表b:
+------+--------+
| n_id | name   |
+------+--------+
|    2 | Mike   |
|    3 | Hanson |
|    4 | Jack   |
+------+--------+

1. 内连结

使用inner join或者join

SELECT a.*, b.*
FROM a JOIN b
ON a.c_id = b.n_id;

或者

SELECT a.*, b.*
FROM a INNER JOIN b
ON a.c_id = b.n_id;

结果：
+------+---------+------+--------+
| c_id | country | n_id | name   |
+------+---------+------+--------+
|    2 | UK      |    2 | Mike   |
|    3 | USA     |    3 | Hanson |
+------+---------+------+--------+

求两个表的交集

2. 外连结

2.1 左外连结1

使用left outer join或者left join

SELECT a.*, b.*
FROM a LEFT JOIN b
ON a.c_id = b.n_id;

或者:
SELECT a.*, b.*
FROM a LEFT OUTER JOIN b
ON a.c_id = b.n_id;

结果:
+------+---------+------+--------+
| c_id | country | n_id | name   |
+------+---------+------+--------+
|    2 | UK      |    2 | Mike   |
|    3 | USA     |    3 | Hanson |
|    1 | CHINA   | NULL | NULL   |
+------+---------+------+--------+

以a表为基准，b表中没有的直接补NULL
这样得到的结果是，a的所有数据和满足一定条件的b的部分数据

2.2 左外连结2

SELECT a.*, b.*
FROM a LEFT JOIN b
ON a.c_id = b.n_id
WHERE b.n_id IS NULL;

+------+---------+------+------+
| c_id | country | n_id | name |
+------+---------+------+------+
|    1 | CHINA   | NULL | NULL |
+------+---------+------+------+

2.3 右外连结1

SELECT a.*, b.* 
FROM a RIGHT JOIN b
ON a.c_id = b.n_id
WHERE a.c_id IS NULL; 

+------+---------+------+------+
| c_id | country | n_id | name |
+------+---------+------+------+
| NULL | NULL    |    4 | Jack |
+------+---------+------+------+

2.4 右外连结2

SELECT a.*, b.* 
FROM a RIGHT JOIN b
ON a.c_id = b.n_id;

+------+---------+------+--------+
| c_id | country | n_id | name   |
+------+---------+------+--------+
|    2 | UK      |    2 | Mike   |
|    3 | USA     |    3 | Hanson |
| NULL | NULL    |    4 | Jack   |
+------+---------+------+--------+

2.5 全连结1

mysql不支持这种连结，但可以使用left join union right join来实现

SELECT a.*, b.*
FROM a LEFT JOIN b
ON a.c_id = b.n_id

UNION

SELECT a.*, b.* 
FROM a RIGHT JOIN b
ON a.c_id = b.n_id;

+------+---------+------+--------+
| c_id | country | n_id | name   |
+------+---------+------+--------+
|    2 | UK      |    2 | Mike   |
|    3 | USA     |    3 | Hanson |
|    1 | CHINA   | NULL | NULL   |
| NULL | NULL    |    4 | Jack   |
+------+---------+------+--------+

2.6 全连结2

SELECT a.*, b.*
FROM a LEFT JOIN b
ON a.c_id = b.n_id
WHERE b.n_id IS NULL

UNION

SELECT a.*, b.* 
FROM a RIGHT JOIN b
ON a.c_id = b.n_id
WHERE a.c_id IS NULL;

+------+---------+------+------+
| c_id | country | n_id | name |
+------+---------+------+------+
|    1 | CHINA   | NULL | NULL |
| NULL | NULL    |    4 | Jack |
+------+---------+------+------+

3. 交叉连结

得到两个表的笛卡尔积

SELECT a.* 
FROM a CROSS JOIN b;

+------+---------+
| c_id | country |
+------+---------+
|    1 | CHINA   |
|    2 | UK      |
|    3 | USA     |
|    1 | CHINA   |
|    2 | UK      |
|    3 | USA     |
|    1 | CHINA   |
|    2 | UK      |
|    3 | USA     |
+------+---------+