-----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.idwhere 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);