多表查询有着许多好玩的小技巧！

USING

USING是一个连接条件的缩写语法：它接收一个用逗号分隔的字段名列表，这些字段的值必须相同且得是连接表所共有的。JOIN...USING会将每一对相等的输入字段输出为一个字段，其后跟着所有其它字段。因此，USING (a, b, c) 等效于ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 。

如果使用了ON，那么在结果里a, b和 c字段都会有两个，而用USING的时候就只会有一个。

NATURAL

NATURAL可理解为“自动化版本”的USING，它会自动形成一个由两个表中同名的字段组成的USING列表（同名字段只出现一次）。

select * from t1 INNER JOIN t2 USING(id);

等价于：

select * from t1 NATURAL INNER JOIN t2;

INNER JOIN 三个表

需要进行括号的嵌套，先将两个表在括号中INNER JOIN一次，再INNER JOIN第三个表。

语法格式可以概括为：

SELECT ... FROM (t1 INNER JOIN t2 USING(field1)) INNER JOIN t3 USING(field2);

或者使用更简洁的NATURAL :

SELECT ... FROM (t1 NATURAL INNER JOIN t2) NATURAL INNER JOIN t3;

作业实例：

编写一个查询显示赚取佣金的所有员工的last_name，department_id，location_id，city。

select e.last_name, d.department_id, l.location_id, l.city from (hr.departments d INNER JOIN hr.locations l USING(location_id)) INNER JOIN hr.employees e USING(department_id) WHERE e.commission_pct is not null;

原理是将l表和d表通过location_id进行连接，将d表和e表通过department_id进行连接。这样从侧面解决了l表和e表没有公共字段的问题。

SELF-JOIN

作业实例：

编写一个查询显示员工 last_name(列标记为 Employee)，employee_id(列标记为 Emp#) 以及他们经理的 last_name(列标记为 Manager)，employee_id(列标记为 Mgr#)。

在本题中，经理和员工是一同放在同一张employees表中的，所不同的有两点：

1. 员工具有manager_id，而经理的manger_id为null；
2. 员工的manager_id为经理的employee_id。

这里采用第二个方式判断即可，在写的时候为同一张表定义两个不同的别名：

select w.last_name as Employee, w.employee_id as "Emp#", m.last_name as Manager, m.employee_id as "Mgr#" from hr.employees w, hr.employees m where w.manager_id = m.employee_id;

注：在select...as...定义别名时，如果别名中含有特殊符号（如注释符号#，则需要使用双引号包裹住所定义的别名，使用单引号是不行的。

巧妙运用AND

select选取的是列，那如果想要综合对行列进行二维的比较，则需要巧妙地运用AND连接符。

当想选择id=1, num>5的数据时，我可以使用：

select id, num from tb where id=1 and num>5;

上面我直接指定了num的值为5，那如果这里num的值我并不知道，而得从表里获取，那我就得使用多表查询的技巧，按如下方式处理：

select a.id, a.num from tb a, tb b where b.id=1 and a.num>b.num;

这是一个精妙与常用的技巧！

其它JOIN

• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行