mysql--多表查询与7种join连接

1.多表查询的正确方式

进行多表连接需要有连接条件 如果想查询多个表中都存在的字段 。
必须指明此字段所在的表 例如如果想查询id 则必须指明 是在employees中还是yoxi中
同样的如果查询的字段是重名的 那么也需要指明查询的是哪一个表中的字段。
查询过程中如果某个表名字长可以取别名增加可读性。

SELECT id1,`name`,name1
FROM employees AS emp,yoxi
WHERE emp.id=yoxi.glid;

同时如果字段名与关键字重名需要给字段名加上着重号 这个例子中employees表和yoxi表中都有id与glid字段 于是需要指明是在哪一个表中

2.多表查询的分类

1.等值连接和非等值连接
2.自连接和非自连接
3. 内连接和外连接

2.1 等值连接和非等值连接

区分是不是等值连接的条件就是其判断的条件是否为=
例如

SELECT id1,sum3,`name`
FROM yoxi,employees
WHERE sum3 BETWEEN sum1 AND sum2;

这里的判断条件就是sum3是否在sum1与sum2之间 这就是非等值连接

2.2 自连接和非自连接

将表自己与自己连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定

例 一个表中每个人都有自己的id与他的管理的人的glid 现在要查询每个人的的名字与他管理者的名字

SELECT yo.id1,yo.name1,xi.id1,xi.name1
FROM yoxi AS yo JOIN yoxi AS xi
ON yo.glid=xi.id1;

这里就是当gild和id相同时代表找到了id的管理者 这时就能查询出id的名字和他管理者的名字

2.3内连接和外连接

内连接：合并具有同一列的两个以上的表的行,结果不包含不匹配的行

SELECT id1,`name`,name1
FROM employees AS emp,yoxi
WHERE emp.id=yoxi.glid;

例如这里只有满足emp.id=yoxi.id的记录
SQL92语法实现内连接如上
SQL92语法实现外连接 使用+ 但是MYSQL 不支持SQL92语法外连接的写法
SQL99语法使用JOIN 。。。ON 的方法实现多表的查询 这种方式可以解决外连接的问题
SQL语法实现内连接 使用JOIN ON

外连接：合并具有同一列的两个以上的表的行,结果包含匹配的行之外

还查询到了左表或右表中不匹配的行

外连接的分类：左外连接 右外连接 满外连接

#SQL99语法实现外连接

左外连接 LEFT OUTER JOIN
右外连接 RIGHT OUTER JOIN
满外连接 FULL OUTER JOIN 但是MYSQL不支持FULL OUTER JOIN

UNION 操作符

UNION 操作符返回两个查询的结果集的并集 去除重复记录
UNION ALL 操作符返回两个查询的结果集的并集 不去除重复记录
而执行UNION ALL比较省资源 如果明确知道不会有重复记录的情况下用UNION AL

7钟JOIN的实现

1.左上图左外连接

SELECT id1,`name`,name1
FROM employees AS emp LEFT OUTER JOIN yoxi
ON emp.id=yoxi.glid;

右上图 右外连接

SELECT id1,`name`,name1
FROM employees AS emp RIGHT OUTER JOIN yoxi
ON emp.id=yoxi.glid;

左中图 ：

SELECT id1,`name`,name1,yoxi.glid
FROM employees AS emp LEFT OUTER JOIN yoxi
ON emp.id=yoxi.glid
WHERE  yoxi.glid IS NULL;

在左外连接的基础上加了一个WHERE yoxi.glid IS NULL;
而yoxi的gild都不是NULL 所以就能去除他们的相同部分得到左中图

右中图 ：

SELECT id1,`name`,name1
FROM employees AS emp RIGHT OUTER JOIN yoxi
ON emp.id=yoxi.glid
WHERE  emp.id IS NULL;

与左中图同理 不过条件改成WHERE emp.id IS NULL;

左下图 满外连接

SELECT id1,`name`,name1
FROM employees AS emp LEFT OUTER JOIN yoxi
ON emp.id=yoxi.glid
UNION ALL
SELECT id1,`name`,name1
FROM employees AS emp RIGHT OUTER JOIN yoxi
ON emp.id=yoxi.glid
WHERE  emp.id IS NULL;

这里就是把左上图与右中图进行连接就能得到

自然连接 NATURAL

自动查询两张连接表中所有相同的字段进行等值连接
例如

SELECT employee_id,last_name,department_name
FROM employees AS e NATURAL JOIN departments d

这里就是查询employees表与departments表中自动查询所有相同的字段进行等值连接

USING连接

在USING后的括号假如同名字段便可

SELECT employee_id,last_name,department_name
FROM employees AS e  JOIN departments d
USING (department_id);

这里就是找寻两个表中字段为department_id的字段进行等值连接