SQL Server 使用INNER JOIN以OR取代WHERE子句

8ehkhllq 于 2022-12-10 发布在其他

关注(0)|答案(3)|浏览(141)

I have the following query: that is trying to get pt_ids that have certain diagnosis codes OR certain outcome_codes. I have accounted for these conditions by including the specific codes I'd like in the WHERE clause. However, I would like to know if I can also do this using in INNER JOIN.

SELECT DISTINCT
       pt_id
FROM #df
WHERE flag <> 1
      AND (diagnosis IN
          (
              SELECT Code
              FROM #df_codes
              WHERE code_id = '20'
          )
      OR outcome_code IN
         (
             SELECT Code
             FROM #df_codes
             WHERE code_id = '25'
         ));

The above code is taking quite a while to run and I am wondering if writing it in the following way 1) is appropriate/viable and 2) will leader to a faster run time. I am essentially trying to join #df with #df_codes on either Code = diagnosis OR code = outcome_code

My Solution

SELECT DISTINCT
       pt_id
FROM #df a
JOIN (select * from #df_codes where code_id = '20')b ON a.diagnosis = b.Code OR 
(select * from #df_codes where code_id = '25')c ON a.outcome_code = c.Code
WHERE flag <> 1

sql-server

来源：https://stackoverflow.com/questions/74661428/replacing-where-clause-with-or-by-using-inner-join

3条答案

按热度按时间

vd2z7a6w1#

I would do it like this:

SELECT DISTINCT pt_id
FROM #df
INNER JOIN #df_codes ON (
        #df.diagnosis = #df_codes.code
        AND #df_codes.code_id = '20'
        )
    OR (
        #df.outcome_code = #df_codes.code
        AND #df_codes.code_id = '25'
        )
WHERE flag <> 1

赞(0）回复(0）举报 2022-12-10

kr98yfug2#

Just a rough suggestion to create small temp tables and join it back to main table. I would suggest use the CTE or original table query and do following:

SELECT Code
into #x
FROM #df_codes
WHERE code_id = '20'

SELECT Code
into #y
FROM #df_codes
WHERE code_id = '25'

SELECT
pt_id
FROM #df
join #x x on x.Code = diagnosis 
WHERE flag <> 1
Union
SELECT
pt_id
FROM #df
join #y y on y.Code = diagnosis 
WHERE flag <> 1

Just replace #df by a CTE that way you can use the existing indexes.

赞(0）回复(0）举报 2022-12-10

gwo2fgha3#

请改用EXISTS：

SELECT DISTINCT
       d.pt_id
  FROM #df  d
 WHERE flag <> 1
   AND EXISTS (SELECT *
                 FROM #df_codes c
                WHERE (c.code = d.diagnosis AND c.code_id = '20')
                   OR (c.code = d.outcome_code AND c.code_id = '25');

如果#df只列出了一个病人--那么你不需要DISTINCT。如果你尝试使用一个连接，而一个病人同时有两个，那么你会得到2行--然后使用DISTINCT来消除“重复”。

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 使用INNER JOIN以OR取代WHERE子句

3条答案

相关问题

热门标签

最新问答