user1432980 user1432980 - 15 days ago 7
SQL Question

replace NOT IN with LEFT JOIN

I've got a table structure like this

[condition]
condition_id, (pk)
question_id (fk)

[option]
condition_id, (fk)
ext_id (fk)

[external]
ext_id, (pk)
inst_id (fk)

[instance]
inst_id, (pk)
keeper_id (fk)

[keeper]
keeper_id, (pk)
org_id

[question]
question_id (pk)
org_id

[localization]
question_id (fk, pk),
org_id (pk),
language (pk)
label


I need to get all questions from
question
table with localizations (from
localization
table), that do not exist in
condition
table for certain
ext_id
.

My query is

SELECT
q.question_id as q_id,
l.label as q_value
FROM question q
INNER JOIN localization l
ON l.question_id = q.question_id
INNER JOIN external ex
ON ex.ext_id = 'EXTERNAL_ID'
INNER JOIN instance i
ON i.inst_id = ex.inst_id
INNER JOIN keeper k
ON k.keeper_id = i.keeper_id
WHERE q.org_id IN ('*', k.org_id)
AND l.org_id = '*'
AND l.language = 'EN'
AND q.question_id NOT IN (
SELECT
question_id
FROM condition c
INNER JOIN option o
ON o.condition_id = c.condition_id
WHERE o.ext_id = 'EXTERNAL_ID'
)


But how to replace that subquery with LEFT JOIN?

Answer

Here is one way

SELECT p.question_id AS q_id,
       l.label       AS q_value
FROM   question q
       INNER JOIN localization l
               ON l.question_id = q.question_id
       INNER JOIN EXTERNAL ex
               ON ex.ext_id = 'EXTERNAL_ID'
       INNER JOIN instance i
               ON i.inst_id = ex.inst_id
       INNER JOIN keeper k
               ON k.keeper_id = i.keeper_id
       LEFT JOIN (select distinct c.question_id 
             from  condition c
              JOIN option o
              ON o.condition_id = c.condition_id
                 AND o.ext_id = 'EXTERNAL_ID' ) c 
              ON c.question_id = q.question_id
WHERE  q.org_id IN ( '*', k.org_id )
       AND l.org_id = '*'
       AND l.language = 'EN'
       AND c.question_id IS NULL 

I am not a expert in Postgres but I prefer NOT EXISTS to do this

SELECT p.question_id AS q_id,
       l.label       AS q_value
FROM   question q
       INNER JOIN localization l
               ON l.question_id = q.question_id
       INNER JOIN EXTERNAL ex
               ON ex.ext_id = 'EXTERNAL_ID'
       INNER JOIN instance i
               ON i.inst_id = ex.inst_id
       INNER JOIN keeper k
               ON k.keeper_id = i.keeper_id
WHERE  q.org_id IN ( '*', k.org_id )
       AND l.org_id = '*'
       AND l.language = 'EN'
       AND NOT EXISTS (SELECT 1
                       FROM   condition c
                              INNER JOIN option o
                                      ON o.condition_id = c.condition_id
                       WHERE  o.ext_id = 'EXTERNAL_ID'
                              AND q.question_id = c.question_id)