Cannot have qualifier in select list when running keyword JOIN w / USING

Question

I am considering a practice test that does not explain the correct answers. The question I'm confused with is basically asking why the following SQL statement will never be able to work:

SELECT oi.order_id, product_jd, order_date FROM order_items oi JOIN orders o USING(order_id); 

The answer he gave was: "The statement will not be executed because part of the USING clause column cannot have a qualifier in the SELECT list"

Can someone clarify this? I am very puzzled.

Answer 1

He complains about the oi qualifier:

 SELECT oi.order_id, product_jd, order_date ^^^ 

Oracle does not allow classifiers in conjunction with a using connection. The clearest way out is to use a regular join:

 SELECT oi.order_id, product_jd, order_date FROM order_items oi JOIN orders o ON o.order_id = oi.order_id 

You can also omit the qualifier. The using statement tells Oracle that although there are two fields named order_id , they are both equal:

 SELECT order_id, product_jd, order_date FROM order_items oi JOIN orders o USING(order_id)