//表关联
内联
外联:// outer join, outer 可省略,只能用on子句常用表达:select a.x,b.y
from a,b
where a.i = b.i //假如省略关联条件也运行不报错, 但是输出的结果是笛卡尔积,不符合SQL1999标准
natural join: select a.x,b.y
from a natural join b //自动找到列名相同的关联(如果列名相同数据类型不同报错), 基本不会用到. 与using互斥
inner join://inner可省略
后接using子句://只能单列,如果在SQL 语句的另一个位置使用了同一列,则不要对其设置别名。
select a.x,b.y
from a join b using (i)
后接on子句:// 一直join下去可以表示多表连接.
select a.x,b.y
from a join b on a.i=b.i and a.c = b.d join e on e.f = a.z
//还可用and应用附加条件,例子见之后full join
left outer join //保留左表的值,一般使用left
*oracle独有表达*select * from a,b where a.i = b.i(+) //表示左联接, tips 想象(+)代替不存在的空值列
right outer join
full outer join// = left right 的并集
自联接select e.first_name,d.department_id
from (select * from departments where department_id = 50) d full join employees e
on d.department_id = e.department_id
等价
select e.first_name,d.department_id
from departments d full join employees e
on d.department_id = e.department_id and department_id = 50不等价
select e.first_name,d.department_id
from departments d full join employees e
on d.department_id = e.department_idwhere department_id = 50
非等值连接
select a.x,b.y
from a join b
on a.x between b.m and b.n
交叉联接(笛卡尔积)