连接

[sql]代码库

 create table author(
  aid char(4),
  aname varchar2(10),
  sex char(1)
 );
 --drop table author;
 create table aword(
  adate date,
  aid char(4)
 );
 --drop table prize_winner;
 insert into author values('1','join','1');
 insert into author values('2','karl','0');
 insert into author values('3','foul','0');
 insert into author values('4','jell','1');
 insert into aword
    select sysdate,'1' from dual union
    select sysdate-1,'4' from dual;
 select * from aword;
 select * from author where aid not in(select aid from aword);
 select * from author a where not exists(
    select 1 from aword b where b.aid=a.aid);
select a.* from author a left join aword b on a.aid=b.aid
    where b.aid is null;
--内连接
select * from author inner join aword on author.aid=aword.aid;
select * from aword inner join author on author.aid=aword.aid;
--左外连接
select * from author left join  aword on author.aid=aword.aid;
select * from aword left join author on author.aid=aword.aid;
--右外连接
select * from author right join aword on author.aid=aword.aid;
select * from aword right join author on author.aid=aword.aid;
--完全连接
select * from author full join aword on aword.aid=author.aid;