多表连接
select table1.aaa,table2.bbb from table1
–cross join table2
–natural join table2
–join table2 using (xxx)
–join table on table1.table1_id = table2.table2 _id
–left/right/full (outer) join table2 on table1.table1_id = table2.table2 _id
内连接(等值连接)
select e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id = select e.last_name,d.department_name from employees e join departments d on e.department_id=d.department_id
外连接 left/right join 表名 on
left join 表名 on select e.last_name,d.department_name from employees e left join departments d on e.department_id=d.department_id = 使用(+), 带(+)的为补充表 select e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id(+)
自连接
select worker.last_name ||' works for '|| manager.last_name from employees worker,employees manager where worker.manager_id = manager.employee_id
自然连接 natural 表名 join
以两个表中名字相同的列为条件创建等值连接
如果有相同名字的列数据类型不同,返回一个错误
select * from employees natural join departments = select * from employees e,departments d where e.department_id=d.department_id and d.manager_id=e.manager_id using指定等值连接用到的列 join 表名 using , natural join 与 using 是互相排斥的 在引用列不要使用表名或别名 select * from employees join departments using (department_id) = select * from employees e,departments d where e.department_id=d.department_id select * from employees join departments using (department_id) where department_id = 10 select * from employees join departments d using (department_id) where d.department_id = 10 --错误的
交叉连接 cross 表名 join
两个表的交叉乘积,该连接和两个表之间的笛卡尔乘积是一样的
select last_name,department_name from employees cross join departments = select last_name,department_name from employees,departments
全外连接 full join 表名 on
返回两个表中的行,即使不匹配的行也会返回
select * from employees e full join departments d on e.department_id=d.department_id