SQL之多表查询

笛卡尔集

–省略连接条件
–连接条件无效
–所有表
–举例
查询名和表名随便起的，不要在意细节
“despasc_id”在两个表都存在

select Psion.employ_id,Psion.desp_id,Psion.despasc_id,section.despasc_id,
section.desaa_name from Psion,section

正确写法：

select Psion.employ_id,Psion.desp_id,Psion.despasc_id,section.despasc_id,
section.desaa_name from Psion,section where Psion.despasc_id=section.despasc_id

看起来舒服的写法：

select Psion.employ_id,Psion.desp_id,Psion.despasc_id,section.despasc_id,
section.desaa_name from Psion e,section d where e.despasc_id=d.despasc_id

整理后：
其实就是别名

select e.employ_id,e.desp_id,e.despasc_id,section.despasc_id,
d.desaa_name from Psion e,section d where e.despasc_id=d.despasc_id

举例：

//左外连接
select e.employ_id,e.desp_id,f.desp_name from employ e, fors f 
where e.desp_id=f.desp_id(+)

//右外连接
select e.employ_id,e.desp_id,f.desp_name from employ e, fors f 
where e.desp_id（+）=f.desp_id

内连接

select employ_id,desp_id,depament_name from emplpy join depaments
using(desp_id) //这种写法必须列名一致，数据类型一致

//推荐
select employ_id,d.desp_id,depament_name from emplpy e join depaments d
on e.desp_id=d.desp_id 

自连接

—举例
查询公司员工“zhangsan”的manager的信息

//该方法先获取manager_id
select last_name,manager_id
from employ where lower(last_name) ='zhangsan'  //如果不知道字符串大小写 可lower(查询id)
//然后使用
select last_name,salary,email from employ where emply_id=108
//不推荐

–自连接

//推荐
select emp.last_name,manager.last_name,manager.salary,
manager.email 
from employ emp, employ manager
where emp.manager_id=manager.employe_id and lower(emp.last_name)='zhangsan'