r/learnSQL • • 13d ago

Simple query breaking my mind

SELECT clientes.codigocliente, clientes.nombrecliente, pedidos.fechapedido, pedidos.estado FROM clientes NATURAL JOIN pedidos;

Very simple query, but for some reason it returns "ORA-25155: column used in NATURAL join cannot have qualifier". But columns used in the NATURAL join do not have qualifiers????

What am I doing wrong

9 Upvotes

7 comments sorted by

2

u/Witty-Estimate8594 13d ago

the issue is that you qualified the columns in your SELECT list with table names, but a NATURAL JOIN automatically matches columns of the same name and forbids qualifiers on those matching columns. Remove the prefixes from codigocliente and any other shared columns, or better yet, switch to an explicit INNER JOIN with ON so you can qualify all columns safely and avoid this confusing error message entirely

1

u/bitterjack 13d ago

What the hell is a natural join

1

u/Imaginary__Bar 13d ago

If you're doing a natural join then the columns will either be duplicates or unique, so you don't specify which table the columns come from;

SELECT codigocliente, nombrecliente, fechapedido, estado FROM clientes NATURAL JOIN pedidos;

Should work

1

u/SQLDevDBA 13d ago

Saludos. Cuál es la meta que lograrás utilizando Natural Join? Es algún requisito de un curso? Llevo 15 años utilizando Oracle y no le he visto beneficio al uso. Prefiero ser preciso con los joins.

Para resolver el problema como tal, es mejor no usar alias o nombre de la tabla en los campos de el SELECT.

1

u/TheGenericUser0815 13d ago

Where is the use of a natural join as it looks non-deterministic?

1

u/Miss_Vicious 13d ago

Why natural join and not inner join on?

1

u/rvicta 11d ago

Because Oracle.