目录
3.1.2 SQL92实现外连接(MYSQL不支持此语法,Oracle可以)
3.2.1SQL99语法实现内连接 (INNER JOIN)
3.2.2SQL99语法实现外连接 (OUTER JOIN)
拓展1:区分重复的列名
- 多个表中有相同列时,必须在列名之前加上表名前缀。
- 在不同表中具有相同列名的列可以用 表名 加以区分。
SELECT employees.last_name, departments.department_name,employees.department_id FROM employees, departments WHERE employees.department_id = departments.department_id;
拓展2:表的别名
- 使用别名可以简化查询。
- 列名前使用表名前缀可以提高查询效率。
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d ##给表起别名 WHERE e.department_id = d.department_id;
需要注意的是,如果我们使用了表的别名,在查询字段中、过滤条件中就只能使用别名进行代替,不能使用原有的表名,否则就会报错。
一.多表查询分类讲解
1.等值连接vs非等值连接
1.1等值连接
利用两张表中具有相同的列值进行查询
类似这样就是等值连接
1.2非等值连接
现有EMPLOYEES和JOB_GRADES两张表,如下。
现在需要利用EMPLOYEES表里面储存的员工信息和JOB_GRADES里面所展示的薪资等级分类。来查询每个员工的薪资及其薪资所处的等级。
代码如下:
SELECT *
FROM job_grades;
SELECT last_name,salary,grade_level
FROM employees e,job_grades j
WHERE e.'salary' BETWEEN j.'lowest_sl' AND j.'highest_sal';#这样就是非等值连接
查询结果如下:
2.自连接vs非自连接
2.1自连接
自连接:用同一张表中的数据进行查询处理
EMPLOYEES表如下
每行记录有employee_id和manager_id这两个字段
manager_id记录的是员工的上司的id
题目:查询employees表,返回“XXX works for XXX”
写法一:
SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager#起别名,本质是同一张表。
WHERE worker.manager_id = manager.employee_id ;#等值查询,利用WOKER表中的MANAGER_ID和MANAGER表中的EMPLOYEE_ID相等
worker和manager本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接,外连接等查询。
查询结果如下:
写法二:
#查询员工id和姓名及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.'manager_id' = mgr.'employee_id';
3.内连接vs外连接
- 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
- 外连接:合并具有同一列的两个以上的表的行,结果集中除了包含一个表与另一个表匹配的行之外,还查询到了左表(右表)中不匹配的行,这种连接称为左(右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。如果是左外连接,则连接条件中左边的表也称为主表 ,右边的表称为从表 。
- 外连接分为左外连接,右外连接,满连接(SQL92中不存在满连接)
3.1 SQL92语法实现多表查询
下面两张表为下面使用SQL92和SQL99语法进行多表查询内连接和外连接例子所用到的表。
已知employees表中有107行记录,departments表中有106条记录。
3.1.1 SQL92实现内连接
employees表中有107行记录,但是却只查询出了106条记录。
是因为在employees表中有一行记录(一行代表一个员工的信息)的deparment_id为null,意思是这个员工没有部门,则这种情况下,不满足查询条件,员工的数据就无法查询出来。
3.1.2 SQL92实现外连接(MYSQL不支持此语法,Oracle可以)
现在有一个员工没有部门,那么现在可以使用外连接,把这个没有部门的员工找出来,即把不符合匹配条件的数据查询出来。
下面的两张运行结果可知MySQL不支持SQL92的外连接,而Oracle可以
不满足查询条件的员工的employee_id和deparment_id也展示了出来(employees e为主表,departments d为从表,所以这个为左外连接)
3.2 SQL99语法实现多表查询
SQL99语法中使用JOIN...ON的方式实现多表查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的
使用JOIN...ON子句创建连接的语法结构
SELECT table1.column, table2.column, table3.column
FROM table1
JOIN table2 ON (table1和table2的连接条件)
JOINtable3 ON (table2和table3的连接条件)
语法说明:
- 可以使用 ON 子句指定额外的连接条件。
- 这个连接条件是与其它条件分开的。
- ON 子句使语句具有更高的易读性。
- 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的,都表示内连接
用到一个JOIN...ON语句的例子
SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id FROM employee e JOIN departments d ON e.department_id=d.department_id;
用到了两个JOIN...ON语句的例子
SELECT last_name, dapartment_name, city FROM employees e JOIN departments d ON e.'dapartment_id' = d.'department_id' JOIN locations i ON d.'location_id' = i.'location_id';
3.2.1SQL99语法实现内连接 (INNER JOIN)
用INNER JOIN语法实现,这个INNER可以省略,直接用上方介绍到的JOIN...ON语法就能实现
SELECT 字段列表
FROM A表
INNER JOIN B表 ON 关联条件
WHERE 等其他子句;
3.2.2SQL99语法实现外连接 (OUTER JOIN)
左外连接(LEFT OUTER JOIN)
- 语法:(OUTER可加可不加)
#实现查询结果是A
SELECT 字段列表
FROM A表
LEFT OUTER JOIN B表 ON 关联条件
WHERE 等其他子句;
- 举例
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
右外连接
-
语法
SELECT 字段列表
FROM A表
RIGHT OUTER JOIN B表 ON 关联条件
WHERE 等其他子句;
- 举例
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
需要注意的是,LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中,在 SQL92 中不存在, 只能用 (+) 表示。
满外连接(FULL OUTER JOIN)
- 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
- SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
- 需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT join代替。