首页 学习区

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

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_idnull
  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:只要其中一个表中存在匹配,则返回行



文章评论

目录