两种左连接查询方式哪种好？

Question

ta表字段：id、bid、flag、name

tb表字段：id、desc

现在要查询出flag=1的所有name及desc

方式1
SELECT a.name, b.desc FROM ta as a LEFT JOIN tb as b ON a.bid=b.id WHERE a.flag=1

方式2
SELECT a.name, b.desc FROM ( SELECT bid,name FROM ta WHERE flag=1) as a LEFT JOIN tb as b ON a.bid=b.id

这两种方式各有什么优缺点？

Answer 1

1、表结构分析：根据表结构，ta中存在tb表的id字段，姑且认为id为主键，那说明tb表为主表，而ta表为从表。如果表结构设计没有问题，那ta表中的bid字段（外键字段）应该建立有索引
2、数据量分析：
2.1、看是什么数据库，如果是PGSQL，两张表各10万以内，如果是MySQL，两张表各1万以内等等（前提是表的字段数少），怎么写都无所谓，全表扫描的效率差别可能都不大
2.2、如果两张表各100万以上，那必须看数据分布以及索引建立情况了
２.2.1、如果如上面 1 所述，在ta表中的bid中建立了索引，那应该这样写才能充分利用索引
　　SELECT ta.name, tb.desc FROM tb JOIN ta ON tb.id = ta.bid AND ta.flag = 1;
　　注意：这个是内连接，而非左连接了（下同）
2.2.2、如果 ta 表中的 flag = 1 的数据量非常少，比如仅1000以内，那使用子查询的效率可能会更高：
　　SELECT ta.name, tb.desc FROM ( SELECT bid, name FROM ta WHERE flag = 1 ) as ta JOIN tb ON tb.id = ta.bid;

2.2.3、如果表设计不是 1 中所述的样子，就是反人类的反向设计，并且bid上即没有索引，其中的值还可能为空，那只能使用左连接了
　　结合需求，根据楼主用左连接说明 ta 表中为全量数据，tb 表中可能有缺失数据，则ta表只能为主表，尽量减少主表的记录数量，能够尽可能多的避免在表连接阶段对tb表的扫描，因此采用方式2的子连接的方式更为高效，当然，还是要看flag = 1的数据量，如果超过了ta表数据量的30%~40%或者数据量超过10万行（不同数据库有所不同），可能对内存资源的要求就要高一些。

3、单从SQL结构上分析
3.1、方式一的处理过程是先进行表关联，然后再对关联后的结果集进行flag = 1的条件过滤。这种方式在flag = 1的数据量占ta表的大多数数据量时可能更高效
3.2、方式二的处理过程是先对ta表的flag = 1的条件进行过滤，再将得到的结果集与tb表进行关联。这种方式在flag = 1的数据量越少，越高效，数据量越多，越接近方式一，当flag = 1接近ta表的全量时，看数据库，可能比方式一要稍微低效一些