英文:
I want to optimize the query-sql
问题
以下是已翻译的内容：
由于数据库中有大量数据，查询不起作用，我需要将一个层中的值与另一个层中的值进行比较，找到新层中不在旧层中的那些值。
这是我的查询：
select distinct id_gs,nmbr_tusa
from "NEW_SLOY"."SHILPU"
where date_contract >= '2023-05-01'
and id_gs not in(select id_gs from "OLD_SLOY"."SHILPU")
英文:
since there is a lot of data in the database, the query does not work, I need to compare values from one layer with another, find those values from the new layer that are not in the old one.
THIS IS MY QUERY:
select distinct id_gs,nmbr_tusa
from "NEW_SLOY"."SHILPU"
where date_contract >= '2023-05-01'
and id_gs not in(select id_gs from "OLD_SLOY"."SHILPU")
答案1
得分: 1
不使用子查询，可以使用 left join。
SELECT DISTINCT N.id_gs, N.nmbr_tusa
FROM "NEW_SLOY"."SHILPU" N
LEFT JOIN "OLD_SLOY"."SHILPU" O
ON N.id_gs = O.id_gs
WHERE N.date_contract >= '2023-05-01'
AND O.id_gs IS NULL;
当子查询中有大量行时，它会将主查询中的每一行与子查询中的每一行进行比较，导致大量的比较。
您可以在这里了解更多关于 left join 的信息：
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-left-join/
英文:
Instead of subquery, you can use left join.
SELECT DISTINCT N.id_gs, N.nmbr_tusa
FROM "NEW_SLOY"."SHILPU" N
LEFT JOIN "OLD_SLOY"."SHILPU" O
ON N.id_gs = O.id_gs
WHERE N.date_contract >= '2023-05-01'
AND O.id_gs IS NULL;
When you have a lot of rows in a subquery. It compares every row in a main query with every row in a subquery. It leads to a huge number of comparisons.
you can read more about left join here:
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-left-join/
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论