外连接分为两种，分别是：

左外连接右外连接

1、左外连接

说明：左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

语法：

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

2、右外连接

说明：右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

语法：

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

说明：SQLAlchemy 支持左外连接（LEFT OUTER JOIN）和右外连接（RIGHT OUTER JOIN）。不过，在 SQLAlchemy 的 ORM 层，通常更常见的是使用左外连接，因为 SQLAlchemy 更多地是按照关系型数据库的标准来设计的，而标准 SQL 中右外连接并不如左外连接那样常见。

但是，你可以使用 SQLAlchemy 的 Core 表达式语言来执行右外连接。以下是如何在 SQLAlchemy 中使用左外连接和右外连接的示例：

注意事项：左外连接和右外连接是可以相互替换，只需要调整在连接查询时SQL中，表结构的先后顺序就可以。在实际开发使用时，左外连接常用。

示例：

1、查询t_emp表的所有数据, 和对应的部门信息 (左外连接)

# 1、查询t_emp表的所有数据, 和对应的部门信息(左外连接)

# select e.*,d.name from t_emp e left outer join t_dept d on e.dept_id = d.id;

# 注意：outerjoin()函数中的连接条件可以省略，Employes.dept_id == Departments.id

res = session.query(Employes, Departments.name).outerjoin(Departments).all()

# print(res)

2、查询t_dept表的所有数据, 和对应的员工信息（右外连接）

# 查询t_dept表的所有数据, 和对应的员工信息（右外连接）

# select d.*,e.* from t_emp e right outer join t_dept d on d.id = e.dept_id;

# 使用join()函数实现，isouter=True使用左外连接，左外连接和右外连接可以相互转换，也就是表的位置不同

# join()函数中的第一个参数Employes表示左外连接关联的表是t_emp，主表是t_dept

res2 = session.query(Employes, Departments).join(Employes, isouter=True).all()

# print(res2)

4、自连接查询

说明：自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

连接方式：对于自连接查询，可以是内连接查询，也可以是外连接查询。

注意事项：在自连接查询中，必须要为表起别名，不然不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

说明：同样的在SQlAlchemy中实现自连接查询，也需要为表起别名的方式，起别名使用aliased()函数实现。

示列：

1、查询员工 及其 所属领导的名字

分析：可将t_emp表看作两个表，要求查询每个员工及其对应的领导姓名，可见这是一个交集关系，需要使用内连接。

# select a.name '员工姓名', b.name '领导姓名' from t_emp a join t_emp b on a.managerid = b.id;

# 或

# select a.name '员工姓名', b.name '领导姓名' from t_emp a, t_emp b where a.managerid = b.id;

# 为表Employes创建别名

user = aliased(Employes)

manager = aliased(Employes)

# 使用Inner join 的连接方式

res = session.query(user.name.label("employee_name"), manager.name.label("manager_name")).join(manager, user.managerid == manager.id).all()

print(res)

2、查询所有员工 t_emp a 及其领导的名字 t_emp b, 如果员工没有领导, 也需要查询出来 。

分析：如果员工没有领导, 也需要查询出来，由此可见需要使用外连接，一般使用左外连接

# 为表Employes创建别名

user = aliased(Employes)

manager = aliased(Employes)

# select a.name '员工姓名', b.name '领导姓名' from t_emp a left join t_emp b on a.managerid = b.id;

res2 = session.query(user.name.label("employee_name"), manager.name.label("manager_name")).join(manager, user.managerid == manager.id, isouter=True).all()

print(res2)

5、联合查询

关键字：union

说明：

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。union all 会将全部的数据直接合并在一起。union 会对合并之后的数据去重。

语法：

SELECT 字段列表 FROM 表A ...

UNION [ ALL ]

SELECT 字段列表 FROM 表B ....;

说明：在 SQLAlchemy 中，可以使用 union() 或者 union_all()方法来执行联合查询。

示例：

1、将薪资低于 5000 的员工 , 和 年龄大于 45 岁的员工全部查询出来.

分析：当前对于上述需求，可以直接使用多条件查询，使用逻辑运算符 and 连接即可。也可以通过union/union all来联合查询.

SQL实现如下：

select * from t_emp where salary < 5000

union all

select * from t_emp where age > 45;

ORM实现如下：

query1 = session.query(Employes).filter(Employes.salary < 5000)

query2 = session.query(Employes).filter(Employes.age > 45)

union_query = query1.union(query2).all()

for un in union_query:

print(un.name)

6、子查询

6.1、概述

1、概念

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

如：

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

2、分类

分类依据：根据子查询结果

分为：

标量子查询（子查询结果为单个值）列子查询(子查询结果为一列)行子查询(子查询结果为一行)表子查询(子查询结果为多行多列)

分类依据2：根据子查询位置

分为：

WHERE之后FROM之后SELECT之后

6.2、标量子查询

说明：子查询返回的结果是单个值（数字、字符串、日期等）

常用操作符：= <> > >= <

示列：

1、查询“销售部”所有员工的信息

SQL实现如下：

分析：可先将查询分为两步

1、先查询出销售部的部门id

select id from t_dept where name = '销售部';

2、然后根据查出的部门id再查询销售部的所有员工信息

select e.* from t_emp e where dept_id = (select id from t_dept where name = '销售部');

或者使用关联查询

select e.* from t_emp e join t_dept d on e.dept_id = d.id where d.name='销售部';

ORM实现如下：

使用 .scalar_subquery() 方法明确地产生一个标量子查询注意：代码可能会出现警告但是可以正常执行，因为SQLAlchemy 建议在 in_() 方法中明确传递一个 select() 构造，而不是直接将子查询对象传递给 in_() 方法。

说明：子查询返回的结果是一列（可以是多行）

常用操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

示列：

1、查询 "销售部" 和 "市场部" 的所有员工信息

SQL实现如下：

分析：可先将查询分为两步

1、先在部门表中查询出销售部和市场部的部门id

select id from t_dept where name = '销售部' or name = '市场部';

2、然后根据1查出的结果，使用关键字in查询出对应销售部和市场部的员工信息1

select * from t_emp where dept_id in (select id from t_dept where name = '销售部' or name = '市场部');

ORM实现如下：

subquery = session.query(Departments.id).filter(or_(Departments.name=="销售部", Departments.name=="市场部")).subquery()

res = session.query(Employes).filter(Employes.id.in_(subquery))

2、 查询比“财务部”所有人工资都高的员工信息

SQL实现如下：

分析：可先将查询分为两步

1、先查询出财务部所有人员的工资信息

select salary from t_emp e join t_dept d on e.dept_id = d.id where d.name = '财务部'; 

2、然后根据1查出的结果，使用关键字all查询出比财务部所有人工资都高的员工信息

select * from t_emp  where salary > all (select salary from t_emp e join t_dept d on e.dept_id = d.id where d.name = '财务部');

或

select * from t_emp where salary > all ( select salary from t_emp where dept_id =(select id from t_dept where name = '财务部') );

ORM实现如下：

使用 filter() 方法结合 all_() 函数来实现

subquery2 = session.query(Employes.salary).join(Departments).filter(Departments.name=="财务部").scalar_subquery()

res2 = session.query(Employes).filter(Employes.salary > all_(subquery2)).all()

3、 查询比“研发部”其中任意一人工资都高的员工信息

SQL实现如下：

分析：可先将查询分为两步

1、先查询出研发部所有人员的工资信息

select salary from t_emp where dept_id = (select id from t_dept where name = '研发部');

2、然后根据1查出的结果，使用关键字any查询出比研发部任意一人工资都高的员工信息

select * from t_emp where salary > any (select salary from t_emp where dept_id = (select id from t_dept where name = '研发部'));

ORM实现如下：

使用 filter() 方法结合 any_() 函数来实现

subquery3 = session.query(Employes.salary).join(Departments).filter(Departments.name == "研发部").scalar_subquery()

res3 = session.query(Employes).filter(Employes.salary > any_(subquery3)).all()

6.4、行子查询

说明：子查询返回的结果是一行（可以是多列）

常用的操作符：= 、<> 、IN 、NOT IN

示列：

1、 查询与 "张敏" 的薪资及直属领导相同的员工信息

SQL实现如下：

分析：可先将查询分为两步

1、先查询出张敏的薪资和直属领导的id

select salary,managerid from t_emp where name = '张敏';

2、然后根据1查出的结果，使用 = 查询与"张敏"的薪资及直属领导相同的员工信息

select * from t_emp where (salary,managerid) = (select salary,managerid from t_emp where name = '张敏');

ORM实现如下：

使用 filter() 方法结合 tuple_() 函数来实现

subquery = session.query(Employes.salary, Employes.managerid).filter_by(name="张敏").subquery()code>

res = session.query(Employes).filter(tuple_(Employes.salary,Employes.managerid).in_(subquery)).all()

6.5、表子查询