01-多表查询

SQL语句的多表查询方式：

　　若两个表有同名的列，必须用表的别名来进行引用，否则出错；

　　连接N个表，还少需要N-1个条件；

 

　　cross join 子句使连接的表的产生叉集，叉集和笛卡尔集是相同的；

　　natural join 子句会以两表中具有相同的列为条件创建等值连接，返回结果；

　　　　如果是列名相同，数据类型不同，则产生错误；

　　using : 在使用natural join创建等值连接时，使用using语句指定等值连接中相同的列；

 　　   　　不要给选中的列中加上表名前缀或别名；

　　JOIN 和 USING 子句经常同时使用；

　　 ON 子句 + 连接条件 ：使语句具有更高的易读性；

 

sql99语法：

    --左外连接（left outer join ...on）

    --右外连接(right outer join ...on)

    --满外连接(full outer join ...on)

outer可省略；

 

例如：按照department_(员工表)和departments(部门表) 的信息。

方式一(通用型):SELECT ... FROM ... WHERE

　　SELECT e.last_name,e.department_id,d.department_name

　　FROM employees e,departments d

　　where e.department_id = d.department_id

 

方式二：SELECT ... FROM ... NATURAL JOIN ...

　　有局限性：会自动连接两个表中相同的列(可能有多个:department_id和manager_id)

　　SELECT last_name,department_id,department_name

　　FROM employees

　　NATURAL JOIN departments

 

方式三：SELECT ... JOIN ... USING ...

　　有局限性：好于方式二，但若多表的连接列列名不同，此法不合适

　　SELECT last_name,department_id,department_name

　　FROM employees

　　JOIN departments

　　USING(department_id)

 

方式四：SELECT ... FROM ... JOIN ... ON ...

　　常用方式，较方式一，更易实现外联接(左、右、满)

　　SELECT last_name,e.department_id,department_name

　　FROM employees e

　　JOIN departments d

　　ON e.department_id = d.department_id

 

eg：

--显示所有员工的姓名，部门号和部门名称。
--方式一
select last_name,e.department_id,department_name
from employees e,departments d
where e.department_id = d.department_id(+);

--方法二：
select last_name,e.department_id,department_name
from employees e left outer join departments d
on e.department_id = d.department_id;

--查询90号部门员工的job_id和90号部门的location_id
select distinct job_id,location_id
from employees e left outer join departments d
on e.department_id = d.department_id
where d.department_id = 90;

--选择所有有奖金的员工的last_name , department_name , location_id , city
select last_name,department_name,d.location_id,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where e.commission_pct is not null;

--选择city在Toronto工作的员工的last_name , job_id , department_id , department_name 
select last_name , job_id , e.department_id , department_name
from employees e ,departments d,locations l
where e.department_id = d.department_id and l.city = 'Toronto' and d.location_id = l.location_id;

--选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
--employees    Emp#    manager    Mgr#
--kochhar      101       king      100
select e1.last_name "employees",e1.employee_id "Emp#",e2.last_name"Manger",e2.employee_id "Mgr#"
from employees e1,employees e2
where e1.manager_id = e2.employee_id(+);