问题
在presto中有ods.loan_info、ods_cps.loan_info表，限定项目编号project_id为WS10141100650001，两张表都要借据号due_bill_no、项目编号project_id，需要查询出ods.loan_info表中在ods_cps.loan_info表中paid_out_type不为'ASSET_BUYBACK'的借据。
方法
在Presto中，要查询ods.loan_info表中那些在ods_cps.loan_info表中paid_out_type不为'ASSET_BUYBACK'的借据号（due_bill_no），我们可以使用几种不同的方法。以下是几种可能的SQL查询方法及其优劣分析：
方法一：使用LEFT JOIN和IS NULL
SELECT DISTINCT ods.due_bill_no
FROM ods.loan_info ods
LEFT JOIN ods_cps.loan_info cps ON ods.due_bill_no = cps.due_bill_no AND ods.project_id = cps.project_id
WHERE cps.paid_out_type != 'ASSET_BUYBACK' OR cps.paid_out_type IS NULL
AND ods.project_id = 'WS10141100650001';
优劣分析：
- 优势：此查询易于理解，使用了标准的SQL语法。
- 劣势：当
ods_cps.loan_info表中没有与ods.loan_info表匹配的记录时（即due_bill_no不存在于ods_cps.loan_info表中），paid_out_type会被视为NULL，这可能导致结果中包含了一些实际上在ods_cps.loan_info表中没有对应记录的借据号。为了解决这个问题，我们在WHERE子句中添加了cps.paid_out_type IS NULL条件，但这可能会使查询逻辑稍微复杂一些。
方法二：使用NOT EXISTS
SELECT DISTINCT ods.due_bill_no
FROM ods.loan_info ods
WHERE ods.project_id = 'WS10141100650001'
AND NOT EXISTS (
SELECT 1
FROM ods_cps.loan_info cps
WHERE ods.due_bill_no = cps.due_bill_no
AND ods.project_id = cps.project_id
AND cps.paid_out_type = 'ASSET_BUYBACK'
);
优劣分析：
- 优势：此查询直接针对需求进行编写，逻辑清晰。使用
NOT EXISTS可以确保只选择那些在ods_cps.loan_info表中没有paid_out_type为'ASSET_BUYBACK'记录的借据号。 - 劣势：相比LEFT JOIN，NOT EXISTS可能在某些情况下性能稍差，尤其是当子查询返回大量数据时。
方法三：使用NOT IN结合子查询
SELECT DISTINCT ods.due_bill_no
FROM ods.loan_info ods
WHERE ods.project_id = 'WS10141100650001'
AND ods.due_bill_no NOT IN (
SELECT cps.due_bill_no
FROM ods_cps.loan_info cps
WHERE cps.project_id = 'WS10141100650001'
AND cps.paid_out_type = 'ASSET_BUYBACK'
);
优劣分析：
- 优势：此查询简洁明了，易于理解。使用NOT IN可以直接筛选出那些不在子查询结果集中的借据号。
- 劣势：当子查询返回大量数据时，NOT IN的性能可能会受到影响。另外，需要注意的是，如果子查询返回NULL，则整个NOT IN条件都会被视为FALSE，这可能导致意外的结果。但是，由于我们在这里使用了确定的项目编号作为筛选条件，所以这种情况不太可能发生。
总结
在选择使用哪种方法时，需要考虑数据的规模、查询的性能要求以及个人的编程习惯。在大多数情况下，NOT EXISTS和NOT IN都能得到正确的结果，但它们的性能可能会因数据量的大小和数据库的具体实现而有所不同。LEFT JOIN方法虽然易于理解，但可能需要额外的逻辑来处理NULL值的情况。
性能分析
在Presto（或大多数关系型数据库管理系统）中，查询性能的最佳方法通常取决于多种因素，包括表的大小、索引的存在、查询的复杂性、数据分布以及数据库管理系统的内部优化。然而，我们可以基于一般的数据库查询优化原则来评估这三种方法的性能特点。
方法一：使用LEFT JOIN和IS NULL
性能分析：
- 当
ods_cps.loan_info表非常大时，LEFT JOIN操作可能会很慢，因为它需要扫描整个表来查找匹配的记录。 - 使用
IS NULL来筛选在ods_cps.loan_info中没有匹配的行可能会进一步降低性能，因为它需要在JOIN操作之后进行额外的过滤。 - 如果
ods.loan_info和ods_cps.loan_info表都有针对due_bill_no和project_id的索引，那么JOIN操作可能会更快。
方法二：使用NOT EXISTS
性能分析：
- NOT EXISTS通常比LEFT JOIN和NOT IN更快，尤其是当子查询返回的结果集较小时。
- 数据库优化器可以更有效地处理NOT EXISTS子查询，因为它只需要在找到第一个匹配项时停止搜索。
- 如果
ods_cps.loan_info表上的due_bill_no和project_id有索引，那么性能通常会更好。
方法三：使用NOT IN结合子查询
性能分析：
- NOT IN的性能取决于子查询返回的结果集大小。如果结果集很大，那么NOT IN可能会很慢，因为它需要比较外部查询中的每一行与子查询返回的所有行。
- 另一方面，如果子查询返回的结果集很小，并且子查询本身很快，那么NOT IN可能会是一个好的选择。
- 与NOT EXISTS类似，索引对于NOT IN的性能也至关重要。
总结
- 在大多数情况下，NOT EXISTS通常是一个较好的选择，因为它允许数据库优化器更有效地执行查询，尤其是在子查询返回结果集较小时。
- 如果子查询返回的结果集可能很大，并且需要快速响应，那么应该考虑使用索引来优化查询，并可能需要在NOT EXISTS和NOT IN之间做出选择，具体取决于实际的查询计划和性能测试结果。
- LEFT JOIN在某些情况下可能是合适的，特别是当需要返回两个表中匹配和不匹配的行时，但在只关心不匹配行的情况下，它可能不是最优选择。
最佳实践
- 在选择查询方法之前，应该分析表的大小、索引的存在以及查询的复杂性。
- 在实际执行查询之前，考虑使用数据库的性能分析工具（如EXPLAIN PLAN）来查看查询的执行计划。
- 根据查询计划和性能测试结果来做出决策，选择最适合的查询方法。
- 如果可能的话，尝试在数据库上运行实际的基准测试，以确定哪种方法在实际情况下性能最佳。
