多表查询
也称关联查询,两个或更多个表一起完成查询操作。
前提,一起查询的表之间是有关系的(一对一、一对多)
之间一定是有关联字段
从1表得到部门id,查询2表得到位置id,查询3表得到位置
但若多次来回从主机到数据库查耗费多,所以要一次就查到
笛卡尔积错误查询
用这种方式进行多表查询的错误
最终会出现2889条记录,员工表中有107行,部门表中有27行
相当于每个员工记录和所有部门记录都匹配了一遍,107*27=2889
原因:缺少了多表连接条件
笛卡尔积 -》 数学运算,又称交叉连接(CROSS JOIN)
两个集合X、Y,X和Y的笛卡尔积就是X和Y的所有组合。
在SQL中CROSS JOIN是关键字,把任意表进行连接
和上面结果一样,一般省略
多表查询正确写法
WHERE 后就是多表连接条件,通过关联字段
表名.字段名‘
不显示department_id=NULL的记录
注意:若查询的字段在两个表中都存在,必须指明一个表来显示
建议,不管是否都存在,全都在字段前加上表名
例如:现在得到department_id,这是关联字段,在两个表中都存在
指明employees.department_id
因为表名,整个执行的代码很长
可以在FROM 中给表起别名
但是,在SELECT中起别名后,在SELECT和WHERE中必须用别名
用表名会报错
实现n个表的查询,至少需要n-1个连接条件,少于n-1则出现笛卡尔积错误
多表查寻分类:非等值连接
之前内容,两个表关联字段相等的查找方式称为等值连接
非等值连接,两个表的关联字段不用等号查找
例如:有一个工资等级表job_grades
用employees表的工资和job_grades的工资等级来连接
自连接
之前的都是非自连接,两个或多个不同表的连接
自连接,自己表和自己表连接
本质是一张表,用虚拟方式成两张不同意义的表
查询员工信息和他的管理者信息
外连接
之前都是内连接
合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
例如查询员工和部门名字时
实际查询出来只有106条记录
而员工表中有107条记录,因为有一个员工的部门id是NULL
同理,若部门表中存在没有员工匹配的空部门,也无法查询
外连接
两个表在连接过程中除了返回满足连接条件的行以外
还返回左(或右)表中不满足条件的 行 ,这种连接称为左(或右) 外连接
例如,同样显示员工表中部门为NULL的记录,为左外连接
显示部门表中没有被匹配的部门是右外连接
两个都显示,是满外连接
左连接,则左边表是主表,右边是从表
右连接,则右边表是主表,左边是从表
SQL99语法
MySQL支持99进行外连接,而不支持92,99语法较复杂,但可读性较好
SQL99内外连接...JOIN,
INNER JOIN内连接(INNER通常省略)
LEFT OUTER JOIN(左外连接,OUTER可以省略)
RIGHT OUTER JOIN(右外连接,OUTER可以省略)
内连接:
SQL92
SQL99
使用JOIN...ON...关键字
加入一个匹配一个,不用WHERE关键字
外连接:
SQL92写法MySQL不支持,只需要(+)就拿实现
SQL99
左外连接:
查询出107行,包括部门为NULL的记录
右外连接:
查询出122行,包括部门里没有员工的记录
满外连接:SQL99语法是FULL JOIN或FULL OUTER JOIN
但MySQL不支持满外连接
在MySQL中通过LEFT JOIN UNION RIGHT JOIN代替