IMPLICIT and EXPLICIT join

Question

im trying to rewrite the following code:

W_WHERE := ' PD.NIF(+) = p.NIF and pd.num_colegiado(+) = p.num_colegiado AND PD.FECHA_INICIO(+) <= SYSDATE 
       AND NVL(PD.FECHA_FIN(+), SYSDATE) >= SYSDATE AND D.ID_DIRECCION(+) = PD.ID_DIRECCION AND p.num_colegiado  = coleg.num_colegiado';

into normal JOIN notation, could anybody help me ?

PS. PD is for PERSONA_DIRECCION table and P is for PERSONA table

You can't just change the `where` part, you need to change the whole query to use `join` syntax instead of a list of tables in the `from`. — Alex Poole, Feb 03 '15 at 10:35
possible duplicate of [Left Outer Join using + sign in Oracle 11g](http://stackoverflow.com/questions/6559261/left-outer-join-using-sign-in-oracle-11g) — Jérôme Radix, Feb 03 '15 at 10:47

score 2 · Answer 1 · edited May 23 '17 at 10:26

2

There is nothing implicit here. In Oracle, "(+) =" is a "normal JOIN notation" (as you said) for outer join. If you don't want outer join, just remove the (+).

See this SO answer for the explanation.

edited May 23 '17 at 10:26

Community

1
1

answered Feb 03 '15 at 10:41

Jérôme Radix

10,285
4
34
40

Oh, there is. In implicit join syntax the tables are comma-separated in the FROM clause and *implicitly* cross-joined. Then records get dismissed in the WHERE clause. Oracle invented the (+) syntax to make one-side outer joins possible in such notation. However, the implicit join syntax was replaced in Standard SQL with explicit join syntax more than 20 years ago and Oracle adopted this more than 10 years ago. AMB does good to replace that out-dated syntax. – Thorsten Kettner Feb 03 '15 at 11:12
1

That "out-dated syntax" is a de-facto standard for more than 20 years. It will never be removed. – Jérôme Radix Feb 03 '15 at 11:17

score 2 · Accepted Answer · answered Feb 03 '15 at 11:03

Explicit joins get their name from specifying explicitly what kind of join you use on the table (CROSS JOIN, INNER JOIN, LEFT OUTER JOIN etc.)

So you will have to re-write the query such that you replace the comma-separated tables in your FROM clause with explicit joins (INNER JOIN and LEFT JOIN here). Then move your join criteria to the ON clause in question:

select ...
from colleg
inner join p on p.num_colegiado  = coleg.num_colegiado
left join pd on pd.nif = p.nif and 
                pd.num_colegiado = p.num_colegiado and 
                pd.fecha_inicio <= sysdate and
                nvl(pd.fecha_fin, sysdate) >= sysdate
left join d on d.id_direccion = pd.id_direccion;

IMPLICIT and EXPLICIT join

2 Answers2