mysql-6:多表联查

多表联查

• 多表联查即多表关联查询

JOIN

• 在 SELECT , UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询
• JON按照功能大致分为三类：
  • CROSS JOIN 交叉连接
  • INNER JOIN 内连接或等值连接
  • OUTER JOIN 外连接

交叉连接

• 交叉连接也叫笛卡尔积连接。笛卡尔积是指在数学中，两个集合 X 和 Y 的笛卡尓积（ Cartesian product ），又称直积，表示为 X*Y ，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。
• 交叉连接的表现：
  • 行数相乘、列数相加
• 隐式交叉连接

SELECT * FROM A, B

• 显示交叉连接

SELECT * FROM A CROSS JOIN B

• 例如：

mysql> select * from product,category;
+-----+-----------------+-------+-------+------+----+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+-----------------+-------+-------+------+----+--------------+
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 1 | 国外食品 |
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 2 | 国内食品 |
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 3 | 国内服装 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 2 | 国内食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 3 | 国内服装 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 1 | 国外食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 2 | 国内食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 3 | 国内服装 |
| 4 | 十三香 | 10 | NULL | 2 | 1 | 国外食品 |
| 4 | 十三香 | 10 | NULL | 2 | 2 | 国内食品 |
| 4 | 十三香 | 10 | NULL | 2 | 3 | 国内服装 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 1 | 国外食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 3 | 国内服装 |
| 6 | 泰国大枣 | 98 | NULL | 20 | 1 | 国外食品 |
| 6 | 泰国大枣 | 98 | NULL | 20 | 2 | 国内食品 |
| 6 | 泰国大枣 | 98 | NULL | 20 | 3 | 国内服装 |
| 7 | iPhone手机 | 800 | NULL | 30 | 1 | 国外食品 |
| 7 | iPhone手机 | 800 | NULL | 30 | 2 | 国内食品 |
| 7 | iPhone手机 | 800 | NULL | 30 | 3 | 国内服装 |
+-----+-----------------+-------+-------+------+----+--------------+
21 rows in set (0.00 sec)

内连接

• 内连接也叫等值连接，内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行
• 隐式内连接

SELECT * FROM A,B WHERE A.id = B.id

• 显式内连接

SELECT * FROM A INNER JOIN B ON A.id = B.id

• 例如：

mysql> select * from product a , category b where a.cid=b.id;
+-----+-----------------+-------+-------+------+----+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+-----------------+-------+-------+------+----+--------------+
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 1 | 国外食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 2 | 国内食品 |
| 4 | 十三香 | 10 | NULL | 2 | 2 | 国内食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
+-----+-----------------+-------+-------+------+----+--------------+
5 rows in set (0.00 sec)

外连接

• 外联接可以是左向外联接、右向外联接或完整外部联接。也就是说外连接又分为：左外连接、右外连接、全外连接
• 外连接需要有主表或者保留表的概念

左外连接

• 关键字：LEFT JOIN 或者 LEFT OUTER JOIN
• SELECT * FROM A LEFT JOIN B ON A.id = B.id
• 例如：

mysql> select * from product a left outer join category b on a.cid=b.id; # 以左表数据为准
+-----+-----------------+-------+-------+------+------+--------------+
| pid | pname | price | pdate | cid | id | cname |
+-----+-----------------+-------+-------+------+------+--------------+
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 1 | 国外食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 2 | 国内食品 |
| 4 | 十三香 | 10 | NULL | 2 | 2 | 国内食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
| 6 | 泰国大枣 | 98 | NULL | 20 | NULL | NULL |
| 7 | iPhone手机 | 800 | NULL | 30 | NULL | NULL |
+-----+-----------------+-------+-------+------+------+--------------+
7 rows in set (0.00 sec)

右外连接

• 关键字：RIGHT JOIN 或者 RIGHT OUTER JOIN
• SELECT * FROM A RIGHT JOIN B ON A.id = B.id
• 例如：

mysql> select * from product a right outer join category b on a.cid=b.id; #以右表数据为准
+------+-----------------+-------+-------+------+----+--------------+
| pid | pname | price | pdate | cid | id | cname |
+------+-----------------+-------+-------+------+----+--------------+
| 1 | 泰国大榴莲 | 98 | NULL | 1 | 1 | 国外食品 |
| 2 | 泰国大枣 | 38 | NULL | 1 | 1 | 国外食品 |
| 3 | 新疆切糕 | 68 | NULL | 2 | 2 | 国内食品 |
| 4 | 十三香 | 10 | NULL | 2 | 2 | 国内食品 |
| 5 | 泰国大枣 | 20 | NULL | 2 | 2 | 国内食品 |
| NULL | NULL | NULL | NULL | NULL | 3 | 国内服装 |
+------+-----------------+-------+-------+------+----+--------------+
6 rows in set (0.00 sec)

全外连接

• 关键字：FULL JOIN 或 FULL OUTER JOIN
• SELECT * FROM A FULL JOIN B ON A.id = B.id

通过业务需求，分析主从表
如果使用 LEFT JOIN ，则主表在它左边
如果使用 RIGHT JOIN ，则主表在它右边
查询结果以主表为主，从表记录匹配不到，则补 null