多表关联查询
我们已经学会了如何在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据 表中读取数据。
本章节我们将向大家介绍如何使用 MySQL的 JOIN在两个或多个表中查询数据。
你可以在 SELECT, UPDATE和 DELETE语句中使用 MySQL的 JOIN来联合多表查询。
JOIN按照功能大致分为如下三类:
- CROSS JOIN(交叉连接)
- INNER JOIN(内连接或等值连接)。
- OUTER JOIN(外连接)
交叉连接
关键字:
CROSS JOIN
交叉连接也叫笛卡尔积连接。笛卡尔积是指在数学中,两个集合 X和 Y的笛卡尓积( Cartesian product),又称直积,表示为 X*Y,第一个对象是 X的成员而第二个对象是 Y的所有可能有序对的其 中一个成员。
交叉连接的表现:
行数相乘、列数相加
- 隐式交叉连接
SELECT * FROM A, 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 CROSS JOIN B
内连接
关键字:
INNER JOIN
内连接也叫等值连接,内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
- 隐式内连接
SELECT * FROM A,B WHERE 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)
显式内连接
SELECT * FROM A INNER JOIN B ON A.id = B.id
外连接
外联接可以是左向外联接、右向外联接或完整外部联接。也就是说外连接又分为:
左外连接、右外连接、全外连接
外连接需要有主表或者保留表的概念。
在 FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:
- 左外连接:
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)
全外连接(MySQL不支持):
FULL JOIN或 FULL OUTER JOIN
SELECT * FROM A FULL JOIN B ON A.id = B.id
外连接总结:
- 通过业务需求,分析主从表
- 如果使用 LEFT JOIN,则主表在它左边
- 如果使用 RIGHT JOIN,则主表在它右边
- 查询结果以主表为主,从表记录匹配不到,则补 null