How to rewrite this query to remove Oracle's ( ) with LEFT/RIGHT INNER/OUTER JOIN keywords in P-CodePudding

I have the following original query in Oracle,

select ...
FROM nvision_trainees_t nv, plans_t p, lookup_t idp_look
WHERE     nv.ned_id = p.trainee_ned_id( )
      AND p.idp_type_id = idp_look.id( )
      AND (   
              (p.id IS NULL AND nv.organizationalstat IN ('EMPLOYEE', 'FELLOW')) 
              OR 
               p.id IS NOT NULL
          );

This query was migrated to Postgres where ( ) does not work. I had to rewrite it accordingly. Since it is on the right side of the equality, it implies RIGHT OUTER JOIN. So I rewrote the query as below, but it's wrong. I think the reason is, the last condition became part of the RIGHT OUTER JOIN whereas originally it was part of an inner join. It doesn't give the same results.

select ...
from nvision_trainees_t nv 
right outer join plans_t p on p.trainee_ned_id = nv.ned_id 
right outer join lookup_t idp_look on 
            idp_look.id = p.idp_type_id 
            /* I feel that this is wrong somehow, it became part of the RIGHT OUTER JOIN condition */
            and ((p.id is null and nv.organizationalstat in ('EMPLOYEE', 'FELLOW'))
                or p.id is not null);

Any tips on how to correctly rewrite the original query with LEFT/RIGHT INNER/OUTER JOINs?

UPDATE After I tried JasonTrue's suggestion of separating the bottom clause as its own WHERE, it worked with the original LEFT JOIN, not the re-written RIGHT JOIN, for some reason. In other words, this worked, does anyone know why? Was I on the wrong track with doing a RIGHT OUTER JOIN for the right-hand ( )?

  from nvision_trainees_t nv   
  left outer join plans_t p on p.trainee_ned_id = nv.ned_id 
  left outer join lookup_t idp_look on idp_look.id = p.idp_type_id 
  where((p.id is null and nv.organizationalstat in ('EMPLOYEE', 'FELLOW'))
            or p.id is not null);

CodePudding user response：

(Added by OP: The right-hand ( ) is indeed a LEFT JOIN, so that should not have been changed to RIGHT JOIN. Instead, the problem is as follows)

In your Oracle example, your final "AND" clause isn't tied to the JOIN operation. In your Postgres one, you make the final AND clause part of your join condition.

I thin you only want the final clause as part of your query filter, not part of the join criteria. Here's an example:

select ...
from nvision_trainees_t nv 
LEFT outer join plans_t p on p.trainee_ned_id = nv.ned_id 
LEFT outer join lookup_t idp_look on 
            idp_look.id = p.idp_type_id 
WHERE      ((p.id is null and nv.organizationalstat in ('EMPLOYEE', 'FELLOW'))
                or p.id is not null);

I haven't tested this, but in ANSI SQL the WHERE clauses typically come after the joins, so I think it's equivalent to your original query.