sql - 使用带 (+) 运算符的传统连接子句

Question

我真的被来自 JustLee 数据库的 SQL 问题困住了。不想找任何人做我的作业，但不幸的是我被困住了......

Questions: Show all authors and books they have written. Include authors that haven't finished writing their books yet. Include author last name, author first name, isbn, and book title. Use traditional join clause with the (+) operator.

我最好的猜测是:

select lname, fname, isbn, title
from author a, bookauthor ba, books b
where a.authorid(+) = ba.authorid
and ba.isbn(+) = b.isbn
order by 1,2,3,4;

但是我得到一个列定义不明确的错误。任何帮助将非常感激。谢谢!

Answer 1

您需要在 SELECT 列表中限定不明确的列名:

select lname, fname, b.isbn, title
from author a, bookauthor ba, books b
where a.authorid(+) = ba.authorid
and ba.isbn(+) = b.isbn
order by 1,2,3,4;

如果没有别的，最好在 select 中限定所有列，以明确该列来自哪个表。我还建议您切换到现代 JOIN 语法，并在 ORDER 子句中明确列出列:

select a.lname, a.fname, b.isbn, b.title
from author a
LEFT JOIN bookauthor ba
ON a.authorid = ba.authorid
LEFT JOIN books b
and ba.isbn = b.isbn
order by a.lname, a.fname, b.isbn, b.title;