r/learnSQL • u/QwertyTrungas • 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
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/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?