Changing In to Exists in SQL – with DISTINCT

April 1, 2022

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.

>Solution :

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.