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'