当两个或多个表关联在一起时,如何在查询中得到有空值的行?

drop table person;
drop table interest;
drop table relation;


create table person (
      pid int primary key,
      fname varchar2(20),
      age int,
      interest int references interest(intID),
      relation int references relation(relID)
);

create table interest (
      intID int primary key,
      intName VARCHAR2(20)
);

create table relation (
      relID int primary key,
      relName varchar2(20)
);

insert into person values(1, 'Rahul', 18, null, 1);
insert into person values(2, 'Sanjay', 19, 2, null);
insert into person values(3, 'Ramesh', 20, 4, 5);
insert into person values(4, 'Ajay', 17, 3, 4);
insert into person values(5, 'Edward', 18, 1, 2);

insert into interest values(1, 'Cricket');
insert into interest values(2, 'Football');
insert into interest values(3, 'Food');
insert into interest values(4, 'Books');
insert into interest values(5, 'PCGames');

insert into relation values(1, 'Friend');
insert into relation values(2, 'Friend');
insert into relation values(3, 'Sister');
insert into relation values(4, 'Mom');
insert into relation values(5, 'Dad');

select * from person;
select * from interest;
select * from relation;

上面的代码显示了表和它的模式,下面是我写的查询。

select person.fname, interest.intName, relation.relName
from person, interest, relation
where person.interest = interest.intID and relation.relID = person.relation;

这是我得到的输出。(Img1)

而不是我得到的输出,这是我需要的输出。(Img2)

img2是我自己写的,在我的查询中(和img1一样),空值被忽略了。

为什么会出现这种情况?并请提供解决方案.谢谢。

解决方案:

看来你需要的是外连接。

SQL> select p.fname, i.intname, r.relname
  2  from person p left join interest i on p.interest = i.intid
  3                left join relation r on r.relid = p.relation;

FNAME                INTNAME              RELNAME
-------------------- -------------------- --------------------
Rahul                                     Friend
Edward               Cricket              Friend
Ajay                 Food                 Mom
Ramesh               Books                Dad
Sanjay               Football

SQL>

给TA打赏
共{{data.count}}人
人已打赏
解决方案

NodeJS - 控制器没有返回JSON到Route。

2022-4-22 17:09:07

解决方案

如何将Drift bot JS代码与静态React NextJs应用(着陆页)整合?

2022-4-22 17:09:09

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索