Oracle数据库之SQL语句关联查询

--===========表连接=================

drop table a purge;
drop table b purge;
create table a(
 c1 number(4),
 c2 varchar(4)
);
create table b(
 c3 number(4),
 c4 varchar2(10),
 c5 number(4) -- 关联a表
);
insert into a values ( 1,'a');
insert into a values ( 2, '2a');
insert into a values ( 3,'3a');
insert into b values ( 4,'b',1);
insert into b values ( 5, '2b',1);
insert into b values ( 6, '3b',2);
commit;

 

SQL> select * from a;
        C1             C2
        ------    - ----
         1             a
         2             2a
         3             3a
SQL> select * from b;
        C3        C4      C5
        ----- -------  -------
         4           b         1
         5          2b        1
         6          3b        2

 
         
--=========   笛卡尔乘积 :第一个表中的所有行与第二个表中的所有行相连接
-- 笛卡尔集的产生条件：
--省略连接条件
--连接条件无效

 select aa.c1,aa.c2 ,bb.c3,bb.c4,bb.c5 from a aa , b bb ;    -- 结果: 3 * 3 = 9;     
 select aa.c1,aa.c2, bb.c3 ,bb.c4 from a aa cross join  b bb;-- (SQL 99的写法)

 
 --查询每个员工的工号，姓名，工资，部门名和工作地点

select deptno,loc from emp,dept where emp.deptno=dept.deptno order by emp.deptno; -- error deptno 不知道是那个表的列

 
-- 查询时列名前，加表名或表别名前辍（如果字段在两个表中是唯一的可以不加）

select e.deptno,d.loc from emp e,dept d where e.deptno=d.deptno order by e.deptno;--ok
select c1 ,c2 ,c3, c4 from a, b where a.c1 = b.c5;-- ok

 

--借此 提下where字句的执行顺序, where 子句的执行顺序 ： 右到左 ,但更深层次的执行顺序是oracle的CBD控制的

Select 'ok' From Dual Where 1 / 0 = 1 And 1 = 2; -- ok
Select 'ok' From Dual Where 1 = 2 And 1 / 0 = 1; -- error

 

--从数据显示方式来讲有：内连接和外连接。
--内连接：只返回满足连接条件的数据。
--外连接：除了返回满足连接条的行以外，还返回左（右）表中，不满足条件的行，称为左（右）连接

--内连接

   

select empno,ename,sal,dname,loc from emp,dept  where emp.deptno=dept.deptno;  --(Oracle 8i 及以前的写法)
 select aa.c1,aa.c2, bb.c3,bb.c4 ,bb.c4 from a aa, b bb where aa.c1 = bb.c5;

 
 --测试语句: a中的c5, b中的c6都不存在,执行下面的语句出现 bb.c6不存在
  

  select aa.c1,aa.c2, bb.c3,bb.c4 ,bb.c4 from a aa, b bb where aa.c5 = bb.c6;

   
--内连接(等值连接)的另一种写法：不支持 as 即使：a as aa 是错误的
   

select aa.c1,aa.c2,aa.b1 , bb.c3,bb.c4 from a1 aa join  b1 bb on aa.b1 = bb.c3;-- ok (SQL 99的写法)
 select aa.c1,aa.c2,aa.b1 , bb.c3,bb.c4 from a1 as  aa join  b1 as  bb on aa.b1 = bb.c3;--(不支持 as )

 
--内连接的执行顺序:
-- on t1.c1 = t2.c2; t1驱动，t2匹配(t1匹配，t2驱动结果一样)
--1.从t1读取一条记录，如c1的值r1
--2.根据c1的值到t2表中进行匹配记录,需要编列t2表(第一条记录开始).
----如t2的值r2等于r1.表示两条记录能否匹配上。t1的r1和t2的r2组合起来.
----作为结果集的一条记录，否则检测t2的下一条记录.

--3.按照方法2一次将t2表所有的记录检测一遍，只要匹配就放入结果集中.

--4.从t1表中读取第二条记录，依次重复2,3.产生最终的结果集.
-- 如果内连接有多个记录需要匹配 ，用and连接   on t1.c1 = t2.c1 and t1.c2 = t2.c2;   


    --左外连接      

SELECT table1.column, table2.column   FROM table1, table2 WHERE table1.column = table2.column(+);

 
-- 说明:我们认为 a是驱动表, b是匹配表

select  aa.c1,aa.c2,bb.c3 ,bb.c4 from a aa left join b bb on aa.c1 = bb.c5;--(SQL 99的写法)

 
--执行顺序
--1.从a读取一条记录，如c1的值r1
--2.根据c1的值到b表中进行匹配记录,需要编列b表(第一条记录开始).
--- 如果c5的值r2等于r1.表示两条记录能否匹配上。a的c1,c2 和 b的c3,c4 组合起来.作为结果---集的一条记录，否则检测b的下一条记录.
---按照方法2一次将b表所有的记录检测一遍，只要匹配就放入结果集中.

-- 3.如果 a.的r1记录在b中找不到任何匹配的记录，b表中模拟一条null记录 与a 表中的r1组合起来，放入结果集中.

-- 4.从a表中读取第二条记录，依次重复2,3.产生最终的结果集.


--右外连接

 

--   " + " 在那边 那边就是匹配表.即aa是匹配表

SELECT table1.column, table2.column    FROM table1, table2 WHERE table1.column(+) = table2.column;
select aa.c1,aa.c2,bb.c3,bb.c4 from a aa, b bb where aa.c1(+) = bb.c5; -- (Oracle 8i 及以前的写法)

 

select aa.c1,aa.c2,bb.c3 ,bb.c4 from a aa right join b bb on aa.c1 = bb.c5;--(SQL 99的写法)

--执行分析同上


 --全连接（满连接）：结果集= 内连接的结果 + 两个表中不匹配的记录

 select  aa.c1,aa.c2,bb.c3 ,bb.c4 from a aa  full  join b bb on aa.c1 = bb.c5;--(SQL 99的写法)

-- Notice:
-- 如果想增加过滤条件 用  on  xxx  and  xx  = xx;

 select  aa.c1,aa.c2,bb.c3 ,bb.c4 from a aa  lef join b bb on aa.c1 = bb.c5 and bb.c3 = 6;

 
 --执行顺序:
-- 1.先进行on, and 进行过滤.
-- 2. join , on 连接.
-- 3.select结果集

-- 如果想对最终的结果增加过滤,用where字句

select  aa.c1,aa.c2,bb.c3 ,bb.c4 from a aa  lef join b bb on aa.c1 = bb.c5  where aa.c1 != 1;

 --执行顺序:
-- 1.先进行on进行过滤.
-- 2. join , on 连接.
-- 3.where过滤
-- 4select结果集

--综合

select  aa.c1,aa.c2,bb.c3 ,bb.c4 from a aa  lef join b bb on aa.c1 = bb.c5  and bb.c3 = 5 where aa.c1 != 1;

 
 --执行顺序:
-- 1.先进行on,and 进行过滤.
-- 2. join , on 连接.
-- 3.where过滤
-- 4select结果集

 

 

参考：乐沙弥的世界