Oracle数据库(二)多表联合查询

1、执行使用两个表的select语句：
1）例句：
select products.name,product_types.name
from products,product_types
where products.product_type_id = product_types.product_type_id ;
2)使用表别名进行数据查询
为了去除反复输入表别名这一冗余的动作，则可以使用表别名：
select p.name , pt.name
from products p , product_types pt
where p.product_type_id = pt.product_type_id ;
3)笛卡尔积
例句：select p.product_type_id , pt.product_type_id from products p , product_types pt ;
返回结果：60 rows selected.
因为products_types 和 products表分别包含了 5 行和 12 行记录，因此结果就是 5 * 12 = 60 行。
2、执行使用多于两个表的select语句
1）下列的这个公式可以计算出在 where 子句中需要的连接的个数：
查询中使用到的总的表的个数减去 1
假设需要查询下列的信息：
·已经购买过产品的顾客
·顾客的姓名
·顾客购买的产品名
·产品类型名
为了查看这些信息，需要查询 customers , purchases , products , product_types 这四个表格，而且需要关联这四个表的
外键关系 ，需要的关联如下所示：
（1）要获得曾经购买过产品的顾客，需要使用 purchases 和 customers 表中的 customer_id 列将这两个表连接起来。
（2）要活的顾客购买的产品，需要使用 purchases 和 products 表中的 product_id 列将这两个表连接起来
（3）要获得产品类型名，需要使用 products 和 product_types 表中的 product_type_id 列将这两个表连接起来。
select c.first_name , c.last_name , p.name as product, pt.name as type
from customers c , purchases pr , products p , product_types pt
where c.customer_id = pr.customer_id
and p.product_id = pr.product_id
and p.product_type_id = pt.product_type_id ;
3、理解连接条件和连接类型
1）根据连接中使用的操作符的不同，连接条件可以分为两类，：
· 等连接（equijion）：在连接中使用等于操作符（=）
· 在连接中使用除等号之外的其他的操作符，例如 < , > , BETWEEN 等等 。。
2）除了连接条件的区分之外，连接本身也有三种不同的类型：
· 内连接（inner jion）：只有当连接中的列包含满足连接条件的值时才会返回一行。这就是说，如果某一行的连接条件
中的一列是空值，那么这行就不会返回。到现在为止所有看到的例子都是内连接。
· 外连接（outer jion）：即使连接条件中的一列包含空值也会返回一行。
· 自连接（self jion）：返回连接到同一个表中的行。
3）理解不等连接：
不等连接在连接中使用除等于操作符之外的其他的操作符，包括： > , < , >= , <= , LIKE , IN 和 BETWEEN 。

	select e.first_name || ' ' || e.last_name as name, e.title , e.salary , sg.salary_grade_id
	from employees e , salary_grades sg 
	where e.salary between sg.low_salary and high_salary ;
4）理解外连接：
	即使连接中的列包含一个空值，在连接也会返回一行。在连接条件中的可以使用外连接操作符来执行一个外连接：外连接操作符是使用
	圆括号括起来的加号：（+） 。
	select p.name as firstname, pt.name as secondname
	from products p , product_types pt 
	where p.product_type_id = pt.product_type_id (+) ;
	======================================================================
	select p.name as firstname, pt.name as secondname
	from products p , product_types pt 
	where p.product_type_id (+) = pt.product_type_id ;
	======================================================================
	&&虽然可以将外连接操作符放在连接操作符的任意一边，但是通常都应该将其放在想检索的行中包含空值的列相反的一边。
5）左外连接和右外连接
	（1）左外连接：
		select p.name , pt.name
		from products p , product_types pt
		where p.product_type_id = pt.product_type_id (+) ;
		** 在做外连接中，左外连接操作符实际上是在等于操作符的右边。
		左外连接的实际就是保留左边的表查询出来的数据，右边的保留空值状态。
	（2）右外连接：
		select p.name , pt.name
		from products p , product_types pt
		where p.product_type_id (+) = pt.product_type_id ;
		** 在做外连接中，右外连接操作符实际上是在等于操作符的左边。
		右外连接的实际就是保留右边的表查询出来的数据，左边的保留空值状态。
	（3）外连接操作符的使用限制：
		· 只能在连接的一端使用外连接操作符，而不能在两端同时使用外连接操作符。
		· 不能同时使用外连接条件和IN操作
		· 不能同时使用一个外连接条件和另外一个使用 OR 操作符的连接条件 ；
		PS：
			select p.name , pt.name
			from products p , product_types pt
			where p.product_type_id = pt.product_type_id (+)
			or p.product_type_id = 1 ;
		***这样会报错 
	6）理解自连接
		自连接是对同一个表进行的连接。要执行一个自连接，必须使用不同的表别名来表示在查询中每次对表的引用。现在考虑一个例子：
		store模式包含了一个名为employees的表，其中保存了员工的信息。如果员工有管理者，那么manager_id列包含了员工的管理者
		的employee_id。
		select wm.first_name || ' ' || wm.last_name || ' works for ' || em.first_name || ' ' || em.last_name as explain
		from employees em , employees wm
		where em.employee_id = wm.manager_id ;
		可以使用NVL()函数来说明Jame Smith为股东工作（记住，他是CEO，因此他想公司的股东汇报）：
		select wm.last_name || ' works for ' || 
		NVL(em.last_name , 'the shareholders')
		from employees em , employees wm 
		where em.employee_id = wm.manager_id ;

4、使用SQL/92语法执行连接：
1）使用 SQL/86 和 SQL/92 来分别执行两个表的内连接：
· 使用 SQL/86 标准的语法来执行一个内连接：
select p.name , pt.name
from products p , product_types pt
where p.product_type_id = pt.product_type_id ;
· SQL/92 引入了 inner jion 和 on 子句来执行内连接。
select p.name , pt.name
from products p inner join product_types pt
on p.product_type_id = pt.product_type_id ;
· 不等连接操作符和 on 子句可以同时使用。
select e.first_name , e.last_name , e.title , e.salary , sg.salary_grade_id
from employees e , salary_grades sg
where e.salary between sg.low_salary and sg.high_salary ;
2）使用 using 关键字简化连接
使用 using 关键字对连接子句进行优化的条件：
· 查询必须是等连接的，即在条件子句中使用的连接条件符号是 “ = ” 。
· 等连接中的列必须是同名，即 “ = ” 左右两边的列名必须是相同的
下面对上面的例子进行简化：
select p.name , pt.name
from products p inner join product_types pt
using(product_type_id) ;
如果希望查看 product_type_id 的值 ， 在 select 子句中只能指定该列明 ， 不能使用表名或者表别名
select p.name , pt.name , product_type_id
from products p inner join product_types pt
using(product_type_id) ;
***
在 using 子句中引用列时不要使用表别名或者别名，否则就会出现错误 。
3）使用SQL/92执行多于两个以上表格的连接：
（1）对customers , purchases , products 和 product_types 进行检索：
select c.first_name || ’ ’ || c.last_name as name , p.name as product , pt.name as type
from customers c , products p , product_types pt , purchases ps
where c.customer_id = ps.customer_id
and p.product_id = ps.product_id
and p.product_type_id = pt.product_type_id ;
(2) 使用 using 关键字对上述查询句子进行改写：
select c.first_name || ’ ’ || c.last_name as name , p.name as product , pt.name as type
from customers c inner join purchases
using(customer_id)
inner join products p
using(product_id)
inner join product_types pt
using(product_type_id) ;
4)使用SQL/92执行多列的内连接：
如果连接中使用了两个表中的多个列，那么就可以在 on 子句中使用 and 操作符注意列出这些列 。
PS:
select …
from table1 inner join table2
on table1.column1 = table2.column1
and table1.column2 = table2.column2 ;
**对上面的句子可使用 using 关键字进行进一步的简化：
select …
from table1 inner join table2
using(column1 , column2) ;
5、使用SQL/92执行外连接：
1）在进行外连接的时候，可以使用（+）这个外连接操作符进行外连接，SQL/92使用下列的语法进行外连接：
from table1 (left , right , full) outer join table2
其中
· table1 和 table2 指定了希望连接的表
· left 说明了希望执行左外连接
· right 说明了希望执行右外连接
· full 说明希望执行全外连接
2）使用 SQL/92 执行左外连接
select p.name , pt.name
from products p , product_types pt
where p.product_type_id(+) = pt.product_type_id ;
PS:对上面的例子进行改写：
select p.name , pt.name
from products p full outer join product_types pt
on p.product_type_id = pt.product_type_id ;
6、使用 SQL/92 执行自连接：
select em.last_name || ’ works for ’ || nvl(wm.last_name , ‘CEO’) as “work realtion”
from employees em , employees wm
where em.manager_id = wm.employee_id ;
**使用SQL/92的标准语法的 inner join 和关键字 on 对这个查询进行重写：
select em.last_name || ’ works for ’ || wm.last_name
from employees em inner join employees wm
on em.manager_id = wm.employee_id ;
7、使用SQL/92的连接语法，能够很好的避免产生笛卡尔积，因为在对表进行连接的时候，通常都必须提供一个 on 或者 using 子句。
如果确实想使用笛卡尔积，SQL/92标准要求必须在查询中使用 cross join 关键字进行显示的声明 。
PS：
下面的例子中，就是用 cross join 关键字在 product_types 和 products 表之间生成一个笛卡尔积：
select * from product_types cross join products ;