左外联接测试

 

-----left join写法----------------------------------------------
select a.*, b.*
from b  left join  a --左联接关键字
 on a.id = b.id  --连接条件
 where b.aid is null; -- 其他限制条件
-----------符号写法------------------------------------
select * from a, b where b.aid = a.id(+); 
--show all the info of table b  
-----------exsist写法------------------------------------
select * from a where exists (select aid from b where b.aid = a.id);

1  总结

 

2 延伸

多个表的外连接查询

2.1 表结构

 A　　　　　　　　　　B　　　　　　　　　　　　C

    

2.2 找出表C中 name为C3所对应的 A表数据

select a.name from (
       a right join b ON a.id = b.aid
)right join c ON c.bid = b.id
where c.name ='C3';　　

select *
  from a
 where exists (select *
          from b
         where a.id = b.aid
           and exists (select *
                  from c
                 where c.bid = b.id
                   and c.name = 'c2'));

3 准备工作

1 创建表

  CREATE TABLE "SS_HR"."A"
   (  "NAME" CHAR(64) NOT NULL ENABLE,
  "ID" NUMBER(*,0) NOT NULL ENABLE,
   PRIMARY KEY ("ID")
   )
  TABLESPACE "CICI" ;

  CREATE TABLE "SS_HR"."B"
   (  "NAME" CHAR(64) NOT NULL ENABLE,
  "ID" NUMBER(*,0) NOT NULL ENABLE,
   PRIMARY KEY ("ID")
   )
  TABLESPACE "CICI" ;

  CREATE TABLE "CICI"."C"
   (  "NAME" CHAR(64) NOT NULL ENABLE,
  "ID" NUMBER(*,0) NOT NULL ENABLE,
  BID NUMBER (*,0),
  FOREIGN KEY (BID) REFERENCES   B (ID),
   PRIMARY KEY ("ID")
   )

  TABLESPACE "CICI" ;

 2 添加外键

alter table b add aid  INTEGER;

ALTER TABLE b ADD CONSTRAINT fk_b2_a FOREIGN KEY (aid  ) REFERENCES a(id);

 

 3 添加数值

INSERT INTO a VALUES( 'a1',1);
INSERT INTO a VALUES( 'a2',2);
INSERT INTO a VALUES( 'a3',3);

INSERT INTO b VALUES( 'b1',1,1);
INSERT INTO b(name,id) VALUES( 'b2',2 ); 

INSERT INTO C VALUES ('c1',1,1);
INSERT INTO C VALUES ('c2',2,1);
INSERT INTO C VALUES ('c3',3,2);