Complex ANSI Join

[tabreaz]

I never work with ANSI Join syntax before, but I got the query which is using ANSI syntax. This query references table D twice. I try to convert as below. But I'm not sure if it's matched the criteria.


FROM a
INNER JOIN b
ON a.j_r = b.JD_j_r
INNER JOIN c
ON a.j_r = c.j_r
INNER JOIN d T1
ON a.v_r = T1.v_r
AND a.p_l = T1.P_l
LEFT OUTER JOIN e
ON b.JD_d_s = e.JC_d_s
AND b.JD_j_r = e.JC_j_r
LEFT OUTER JOIN d t2
ON a.a_v_e = t2.v_r
AND a.a_p_e = t2.p_l

-------------->


FROM a, b, c, d t, e
WHERE a.j_r = b.jd_j_r
AND a.j_r = c.j_r
AND t.v_r = a.v_r
AND a.p_l = t.p_l
AND b.jd_j_r = e.jc_j_r
AND b.jd_d_s = e.jc_d_s(+)
AND b.jd_j_r = e.jc_j_r(+)
AND a.v_r(+) = t.v_r
AND a.p_l(+) = t.p_l


I can't test the query, as I don't have access to the database.

[tamilselvan]

An INNER JOIN returns rows when both tables have matching rows on the condition.

An OUTER JOIN returns rows even when matching rows are not found on the other table.

If a table (in your case d) is used in 2 places, then you can't change it to one table.