oracle USING 用法

提问

• using(xx)中可以接多个列吗？
• using(xx)中的列可以接表名或别名吗？
• 在使用using的语句中，select * 可以使用吗？
• 如果表有别名t，select t.* from table1 t , table2 using(xx)，t.* 可以使用吗？
• left join, right join, inner join, full outer join 可以和using一起用吗？

我们看下语法就能知道答案了。

只看答案

• using(xx)中可以接多个列，它是一种等值连接，可以转换为 join ... on (x=y and xx=y)；
• using(xx)中的列不可以接表名或别名等限定语，默认就是指共同列，不能指定表名；
• 在使用using的语句中，select * 可以使用，* 被扩展的顺序为using中的列，左表中的其它列，右表中的其它列
• 如果表有别名t，select t.* from table1 t , table2 using(xx)，t.* 不可以使用，using中的列不能加限定词
• left join, right join, inner join, full outer join 可以和using一起使用，它就是简单的等值连接

inner join 就是 join， outer join有 left, right, full outer join 三种
using的使用条件是两表中列名和类型相同；
natural join更进一步，直接省掉了using，但作用和使用using相似；
inner 和 outer 的区别：内连接只返回匹配行，外连接还返回不满足条件的行；

---

Syntax

USING ( Simple-column-Name [ , Simple-column-Name ]* )

USING clause definition

The USING clause specifies which columns to test for equality when two tables are joined. It can be used instead of an ON clause in the JOIN operations that have an explicit join clause.

If a column in the USING clause is referenced without being qualified by a table name, the column reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a column in the USING clause point to the column in the second (right) table.

When a USING clause is specified, an asterisk (*) in the select list of the query will be expanded to the following list of columns (in this order):

• All the columns in the USING clause
• All the columns of the first (left) table that are not specified in the USING clause
• All the columns of the second (right) table that are not specified in the USING clause

Examples

SELECT * FROM COUNTRIES JOIN CITIES
     USING (COUNTRY);
     
SELECT * FROM COUNTRIES JOIN CITIES
    USING (COUNTRY, COUNTRY_ISO_CODE);
 
 SELECT * FROM oe.order_items oi JOIN oe.orders o
  USING(order_id);

select t.* from hr.employees t, hr.departments d
where t.department_id = d.department_id;

select * from hr.employees t inner join hr.departments d
using(department_id);

select * from hr.employees t left outer join hr.departments d
using(department_id);

select * from hr.employees t right outer join hr.departments d
using(department_id);

select * from hr.employees t full outer join hr.departments d
using(department_id);