'what does this error mean "column used in NATURAL join cannot have qualifier"

After executing the code below, i get an ORA-25155 error

SELECT p.prod_id, prod_name, prod_list_price,quantity_sold,cust_last_name
FROM products p NATURAL JOIN sales s NATURAL JOIN customer c
WHERE prod_id = 148;
sqloracle11g


Solution 1:[1]

As suggested on a comment, this ORA-25155 error suggests that you're qualifying the column name "p.prod_id" instead of just "prod_id". That is what is triggering your error.

Try this fully working SQL Fiddle code. Your query should be:

SELECT prod_id, prod_name, prod_list_price,quantity_sold,cust_last_name
  FROM products
       NATURAL JOIN sales
       NATURAL JOIN customer
 WHERE prod_id = 148;

A NATURAL JOIN over multiple tables will join one pair of tables, then join the third table to the result and so forth. NATURAL JOIN syntax has no explicit join predicate, so the qualifiers aren't used there.

Solution 2:[2]

natural join in Oracle joins two tables on the columns in each table that have common names. Hence after the join, you shouldn't refer to a column which is common between the tables that have been naturally join with an alias, as the alias is redundant.

This is similar to the way using() works in a join clause.

Solution 3:[3]

The right command is:

SELECT * FROM products p NATURAL JOIN sales s NATURAL JOIN customer c

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Yaroslav
Solution 2 muhmud
Solution 3 Mudit Gupta

Related Questions