将SQL子查询转换为JOIN：改写含多子查询的SELECT语句

Here's how you can rewrite your query using INNER JOINs instead of nested subqueries—this approach is often more readable and can be better optimized by database engines:

SELECT DISTINCT c.SuperGroupNm, c.CostCenterNbr
FROM vw_dimCostCenter c
INNER JOIN vw_dimworker w 
    ON CAST(c.CostCenterNbr AS VARCHAR(10)) = w.WorkerCostCenterCd
INNER JOIN vw_DimOrganizationHierarchy o 
    ON w.WorkerKey = o.OrganizationHierarchyManagerWorkerKey
WHERE 
    c.CostCenterStatusTxt = 'Active'
    AND w.WorkerStatusCd IN ('A', 'L')
    AND LOWER(o.OrganizationHierarchyUnitLevel...) -- Add your full truncated condition here

Important Notes: #

• I included DISTINCT because joins can sometimes return duplicate rows (unlike the IN clause which automatically deduplicates results). If you’re certain your joins won’t produce duplicates, you can safely remove it.
• The innermost scalar subquery (w.WorkerKey = (SELECT o...)) is replaced with a direct join between vw_dimworker and vw_DimOrganizationHierarchy. This assumes the original subquery returns exactly one matching row per worker; if it could return multiple rows, you might need to adjust (e.g., use EXISTS or aggregate to get a single key).
• Don’t forget to complete the truncated condition on o.OrganizationHierarchyUnitLevel in the WHERE clause to match your original logic.

This rewrite preserves all the original filtering and selection logic while using joins for better performance and readability.

内容的提问来源于stack exchange，提问作者Gargoyle