Question

sql server 把子查询换成联接查寻

select a.STATION_CODE code,a.STATION_NAME shi
from STATION_BASE_INFO a where a.STATION_CODE
not in(select b.STATION_CODE from T_AUDIT_PROJECT_RESULT b)
and a.LSPQ ='H006'

各位大神，怎么把上面那条子查询改成联接查询

Answer 1

表联接查询:
SELECT
a.STATION_CODE CODE,
a.STATION_NAME shi
FROM
STATION_BASE_INFO a LEFT OUTER JOIN T_AUDIT_PROJECT_RESULT b ON a.STATION_CODE = b.STATION_CODE
WHERE
AND a.LSPQ = 'H006' AND b.STATION_CODE IS NULL;
EXISTS 方式(可以使用索引):
SELECT
a.STATION_CODE CODE,
a.STATION_NAME shi
FROM
STATION_BASE_INFO a
WHERE NOT EXISTS (
SELECT
b.STATION_CODE
FROM
T_AUDIT_PROJECT_RESULT b
WHERE
b.STATION_CODE = a.STATION_CODE
)
AND a.LSPQ = 'H006'