9、 案例如下：/*查询哪个部门没有员工
* 这里只需将部门的信息全部输出并且employee_id=null即可，所以这里用的是左外连接
* 左外连接(右边的表不加限制)，以左表为基准，查询出左表所有的数据和右表中连接字段相等的记录，
如果右表中没有对应数据，则显示为空（NULL）
* 右外连接(右边的表不加限制)，以右表为基准，查询出右表所有的数据和左表中连接字段相等的记录，
如果左表中没有对应数据，则显示为空（NULL）
* 全外连接(左右两表都不加限制)，左右两表的所有字段名均列出（且各只出现一次）
不符合on表达式的字段为null
* */
<select id="queryNoEmployees1" resultType="map"> SELECT d.*,e.employee_id FROM myemployees.departments d LEFT JOIN myemployees.employees e on d.department_id=e.department_id WHERE e.employee_id is NULL; </select> <select id="queryNoEmployees2" resultType="map"> SELECT d.*,e.employee_id FROM myemployees.employees e right JOIN myemployees.departments d on d.department_id=e.department_id WHERE e.employee_id is NULL; </select>
结果： 
10、 案例如下：用的是实体类查询指定字段，自己也可以给出自己喜欢的输出格式，但不是很方便。
/*指定返回类型为实体类，Sql查询到的内容就映射到相关实体类的属性中，
* 需要用多对一来实现三表查询
* 案例：查询员工姓名、部门名和所在城市，城市名以s开头
* 分析：多个员工可能位于同一个部门，可能位于同一城市
* 也就是说多个员工关联一个部门，一个城市
*/
Employees.java
package com.pojo; public class Employees { private String firstName; private String lastName; private Departments departments; private Locations locations; public Employees() { } public Employees(String firstName, String lastName, Departments departments, Locations locations) { this.firstName = firstName; this.lastName = lastName; this.departments = departments; this.locations = locations; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public Departments getDepartments() { return departments; } public void setDepartments(Departments departments) { this.departments = departments; } public Locations getLocations() { return locations; } public void setLocations(Locations locations) { this.locations = locations; } @Override public String toString() { return "姓名：" + firstName + " " + lastName +" "+ departments+" "+locations; } }
Departments.java
package com.pojo; public class Departments { private String departmentName; public Departments(){ } public Departments( String departmentName) { this.departmentName = departmentName; } public String getDepartmentName() { return departmentName; } public void setDepartmentName(String departmentName) { this.departmentName = departmentName; } @Override public String toString() { return " 部门名：" + departmentName ; } }
Locations.java
package com.pojo; public class Locations { private String city; public Locations(){ } public Locations(String city) { this.city = city; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } @Override public String toString() { return "城市名：" + city ; } }
<select id="queryByGroup2" resultMap="toDepartments"> select last_name 姓,first_name 名字,department_name 部门名,city 城市名 from myemployees.employees e,myemployees.departments d,myemployees.locations l where e.department_id=d.department_id and d.location_id=l.location_id and city like 's%' order by department_name desc </select> <resultMap id="toDepartments" type="Employees" > <result property="lastName" column="姓"/> <result property="firstName" column="名字"/> <association property="departments" javaType="Departments"> <result property="departmentName" column="部门名"/> </association> <association property="locations" javaType="Locations"> <result property="city" column="城市名"/> </association> </resultMap>
结果：
