MySQL的多表查询（交叉连接，内连接，外连接）

MySQL的多表查询

多表的查询方式：

---

(假设有两张表A，B)

交叉连接

语法：SELECT * FROM A,B;

总结：得到的查询结果是两张表的笛卡尔积，也就是用A表中的每条数据都去匹配B表中的所有数据，获得的结果往往不是我们需要的，一般很少使用交叉连接。

---

内连接（inner join ，inner 可以省略）

• 显示内连接

语法：SELECT * FROM A INNER JOIN B ON 条件;

• 隐示内连接

语法：SELECT * FROM A,B WHERE 条件;

总结：显示内连接和隐示内连接获得的查询结果是一样的，都是A表和B表的交集（例：A.id = B.id），但是只能查到有关系的信息，如果A表的一条数据的与B表关联的字段（如：A.id = null），即：这条数据在B表中没有对应的信息，则无法获得。

---

外连接（outer join，outer可以省略）

• 左外链接（left outer join，outer可以省略）

语法：SELECT * FROM A LEFT OUTER JOIN B ON 条件;

总结：左外连接获得的查询结果是左边的表A的全部信息和A，B两张表的交集，左边A表的全部包含A表中在B表中没有对应关系的信息。

• 右外连接（right outer join，outer可以省略）

语法：SELECT * FROM A RIGHT OUTER JOIN B ON 条件;

总结：右外连接获得的查询结果是右边的表B的全部信息和A，B两张表的交集，右边B表的全部包含B表中在A表中没有对应关系的信息。

---

辨别内连接和外连接的小技巧：没有省略的情况下很好辨别（inner，outer），省略的时候只要（join）的是内连接，有（left，right）的是(左/右)外连接。

---

例子：

一对多关系表：

CREATE DATABASE test; # 创建test数据库CREATE TABLE emp ( # 创建员工表    eid INT PRIMARY KEY AUTO_INCREMENT, # 员工id    ename VARCHAR(32) NOT NULL, # 员工姓名    did INT,    FOREIGN KEY (did) REFERENCES dept(did) # 设置外键);CREATE TABLE dept ( # 部门表    did INT PRIMARY KEY AUTO_INCREMENT, # 部门id    dname VARCHAR(20)  # 部门名称);INSERT INTO emp VALUES (NULL,'张三',1);INSERT INTO emp VALUES (NULL,'李四',1);INSERT INTO emp VALUES (NULL,'王五',2);INSERT INTO emp VALUES (NULL,'赵六',2);INSERT INTO emp VALUES (NULL,'老王',NULL);INSERT INTO dept VALUES (NULL,'人事部');INSERT INTO dept VALUES (NULL,'宣传部');INSERT INTO dept VALUES (NULL,NULL);

交叉连接：

SELECT * FROM emp , dept;

结果：

内连接：

• 显示内连接

SELECT * FROM emp e INNER JOIN dept d ON e.did = d.did;

• 隐示内连接

SELECT * FROM emp e,dept d WHERE e.did = d.did;

结果：

外连接：

• 左外连接

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.did = d.did;

结果：

• 右外连接

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.did = d.did;

多对多关系表：

CREATE DATABASE test; # 创建test数据库CREATE TABLE goods ( # 创建商品表    g_no INT PRIMARY KEY AUTO_INCREMENT,  # 商品编号    gname VARCHAR(20) NOT NULL # 商品名);CREATE TABLE goods_orders ( # 商品表格订单表的关系表    id INT PRIMARY KEY AUTO_INCREMENT, # 主键    g_no INT ,    o_no INT );ALTER TABLE goods_orders ADD FOREIGN KEY (g_no) REFERENCES goods(g_no);ALTER TABLE goods_orders ADD FOREIGN KEY (o_no) REFERENCES orders(o_no);CREATE TABLE orders ( # 创建订单表    o_no INT PRIMARY KEY AUTO_INCREMENT, # 订单编号    oremark VARCHAR(100) # 订单的备注);INSERT INTO goods VALUES (NULL,'手机');INSERT INTO goods VALUES (NULL,'零食');INSERT INTO goods VALUES (NULL,'键盘');INSERT INTO goods VALUES (NULL,'鼠标');INSERT INTO goods_orders VALUES (NULL,1,1);INSERT INTO goods_orders VALUES (NULL,2,1);INSERT INTO goods_orders VALUES (NULL,2,2);INSERT INTO goods_orders VALUES (NULL,3,2);INSERT INTO goods_orders VALUES (NULL,NULL,NULL);INSERT INTO orders VALUES (NULL,'好贵。。');INSERT INTO orders VALUES (NULL,'快点发货。。');INSERT INTO orders VALUES (NULL,'修改送货地址。。');

交叉连接：

SELECT * FROM goods,orders,goods_orders;

结果：

内连接：

• 显示内连接

SELECT * FROM emp e INNER JOIN dept d ON e.did = d.did;

• 隐示内连接

SELECT * FROM goods g,goods_orders go,orders o WHERE g.g_no = go.g_no AND o.o_no = go.o_no;

结果：

外连接：

• 左外连接

SELECT * FROM goods g LEFT OUTER JOIN goods_orders go ON g.g_no = go.g_no LEFT OUTER JOIN orders o ON o.o_no = go.o_no;

结果：

• 右外连接

SELECT * FROM goods g RIGHT OUTER JOIN goods_orders go ON g.g_no = go.g_no RIGHT OUTER JOIN orders o ON o.o_no = go.o_no;

注意：如果先使用了左外连接，然后使用了右外连接，得到的查询结果是先执行左外连接得到查询结果，再将得到的结果执行右外连接，反之亦然。