我的不同之处在于你可以使用USING子句将自己描绘成一个角落:
CREATE TABLE roster (mgrid INTEGER,empid INTEGER);
CREATE TABLE emp (empid INTEGER,NAME VARCHAR2(20));
INSERT INTO roster VALUES (1,10);
INSERT INTO roster VALUES (1,11);
INSERT INTO roster VALUES (1,12);
INSERT INTO roster VALUES (2,20);
INSERT INTO roster VALUES (2,21);
INSERT INTO emp VALUES (10,'John');
INSERT INTO emp VALUES (11,'Steve');
INSERT INTO emp VALUES (12,'Mary');
INSERT INTO emp VALUES (20,'Ann');
INSERT INTO emp VALUES (21,'George');
INSERT INTO emp VALUES (1,'Pete');
INSERT INTO emp VALUES (2,'Sally');
SELECT r.mgrid,e2.name,e1.empid,e1.name
FROM roster r JOIN emp e1 USING(empid)
JOIN emp e2 ON r.mgrid = e2.empid;
在上面的select中,你得到一个ora-25154,“USING子句的列部分不能有限定符”.
如果删除e1.empid限定符,如下所示:
SELECT r.mgrid,empid,e1.name
FROM roster r JOIN emp e1 USING(empid)
JOIN emp e2 ON r.mgrid = e2.empid;
您收到ORA-00918错误,“列模糊定义”.
你必须使用:
SELECT r.mgrid,e1.name
FROM roster r JOIN emp e1 ON r.empid = e1.empid
JOIN emp e2 ON r.mgrid = e2.empid;
这个例子是设计的,但是当我第一次探索连接语法时,我遇到了真实情况下的这个问题.从那时起我就避开了USING条款.除了几次击键之外,USING子句没有任何优势.