当将两个或多个表关联在一起时，如何在查询中获得具有null值的行

Question

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(，空值被忽略。

为什么会发生这种情况？请提供解决方案。非常感谢。

Answer 1

看起来您需要外部联接。

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>