多表联查
多表联查
- 多表联查
- JOIN
- 交叉连接
- 内连接
- 外连接
- 左外连接
- 右外连接
- 全外连接
- 多表联查即多表关联查询
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