Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

sql - Cannot have a qualifier in the select list while performing a JOIN w/ USING keyword

I am looking at a practice test that doesn't have explanations about the correct answers. The question I'm confused about basically asks why the following SQL statement can never work:

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

The answer it gave was: "The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list"

Can someone elaborate on this? I am pretty stumped.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

It's complaining about the oi qualifier:

SELECT oi.order_id, product_jd, order_date
       ^^^

Oracle does not allow qualifiers in combination with a using join. The clearest way out is using 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 even though there are two fields called order_id, they are both equal:

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...