Changing In to Exists in SQL - with DISTINCT

Question

Having this one:

code IN 
(
SELECT  DISTINCT aaa.code
FROM ORGHEADER AS aaa
LEFT JOIN ORGRELATEDPARTY AS bbb
    ON aaa.oh_pk = bbb.parent
WHERE aaa.oh_pk NOT IN 
(
SELECT  fu.parent 
FROM ORGRELATEDPARTY  fu
WHERE fu.partytype = 'MNG'
)
)

Reading this one: Changing IN to EXISTS in SQL

Tried to change it into "Exists", but produced this and it did not work:

code EXISTS
(
SELECT  *
FROM ORGHEADER AS aaa
LEFT JOIN ORGRELATEDPARTY AS bbb
    ON aaa.oh_pk = bbb.pr_oh_parent
WHERE aaa.oh_pk NOT IN 
(
SELECT  fu.parent 
FROM ORGRELATEDPARTY  fu
WHERE fu.pr_partytype = 'MNG'
)
WHERE code = DISTINCT aaa.oh_code
)

The error is 3706: Syntax error: expected something between '=' and 'DISTINCT' keyword.

`DISTINCT` is not a function, it's a _set quantifier_, and a part of `SELECT DISTINCT`. Skip those extra parentheses and simply write `SELECT DISTINCT aaa.code FROM ...`, to make code clearer. — jarlh, Apr 01 '22 at 12:17
However, `... IN (SELECT DISTINCT ...` makes no difference at all. Simply do `... IN (SELECT ...`. — jarlh, Apr 01 '22 at 12:18
Your EXISTS subquery needs to be _correlated_, i.e. include a condition referencing the outer table. — jarlh, Apr 01 '22 at 12:19
IN in `a IN (subquery)` is a *binary* operator, resulting in a boolean outcome . `EXISTS( subquery)` just yields a bolean outcome — wildplasser, Apr 01 '22 at 12:21
@wildplasser - hm, so technically it cannot be done? Or only the nested `IN` can be converted to `EXISTS`? — Vityata, Apr 01 '22 at 12:23
Yes, it can be converted, but you'll need to rewrite the subquery, too. — wildplasser, Apr 01 '22 at 12:24
Add your some sample tables, explain what you're trying to do, and we might be able to show you the proper way of doing this. But as explained by several before me, there are several errors in this code including how you're using Exists and Distinct. — Stoff, Apr 01 '22 at 12:24

forpas · Accepted Answer · 2022-04-01T12:33:22.047

You have already redundant code.
Inside the IN subquery you are selecting a column from ORGHEADER, so the LEFT join only adds noise since it returns in any case all rows from ORGHEADER.

Assuming that code belongs to a table aliased as t you can write the code with EXISTS like this:

WHERE EXISTS (
  SELECT 1
  FROM ORGHEADER AS aaa
  WHERE aaa.oh_code = t.code
    AND aaa.oh_pk NOT IN (
      SELECT parent 
      FROM ORGRELATEDPARTY  
      WHERE pr_partytype = 'MNG'
    )
)

Also, NOT IN will not work if the column parent of the table ORGRELATEDPARTY may return nulls.

Changing In to Exists in SQL - with DISTINCT

1 Answers1