不存在三表连接

Question

我有三个表，我想用SQL回答以下问题：

“谁只有没有分数的证书？”

例如，在下面的设置中，查询只返回"John“。Joana拥有"AWS认证“，它在分数表(id 57)中。thee拥有"ITIL V3认证“，这并不在评分表中，但她也有”专业Python“，它在您的评分表中。

PERSON
| PERSON_ID | PERSON_NAME |
| --------  | -------------- |
| 12    | John            |
| 23   | Mary            |
| 24   | Joana            |


**CERTIFICATION**
|CERTIFICATION_ID| CERTIFICATION_NAME | PERSON_ID|
| -------- |----------- | -------------- |
| 53 | Java Certification    |    12       |
| 54 | ITIL V3 Certification    |    23       |
| 55 | Professional Python Dev            | 23|
| 56 |GCP Certification            |23   | 
| 57 |AWS Certification            |24   | 

SCORES

|SCORE_ID| CERFITICATION_ID | SCORE_DETAILS |
| -------- |----------- | -------------- |
| 70 |55   | 80%            |
| 71 |56   | 90%            |
| 72 |57   | 95%           |

我只想在SQL中实现这一点，而不需要迭代记录，也不需要使用存储过程。

用于创建这些表并在需要时添加数据的SQL：

create table person(
person_id integer,
person_name varchar(16)
);

create table certification(
CERTIFICATION_ID int,
  CERTIFICATION_NAME varchar(16),
  person_id int
);

create table scores(
  SCORE_ID int,
  CERTIFICATION_ID int,
 SCORE_DETAILS varchar(16));


insert into person(person_id, person_name) values(12, 'John');
insert into person(person_id, person_name) values(23, 'Mary');
insert into person(person_id, person_name) values(24, 'Joana');


insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(53,'A', 12);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(54,'B',23);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(55,'C', 23);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(56,'D', 23);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(57,'E', 24);

insert into scores (SCORE_ID,CERTIFICATION_ID, SCORE_DETAILS) values (70,55,'e');
insert into scores (SCORE_ID,CERTIFICATION_ID, SCORE_DETAILS) values (71,56,'f');
insert into scores (SCORE_ID,CERTIFICATION_ID, SCORE_DETAILS) values (72,57,'g');

Answer 1

您可以使用以下查询

select *
  from person p
 where not exists (select 1
                 from certification c
                 join scores s on s.certification_id = c.certification_id
                where p.person_id = c.person_id
              )

Answer 2

我们可以在下面这样使用

select p.* from person p
 where p.person_id not in (
select c.person_id from 
certification c join scores s on c.CERTIFICATION_ID=s.CERTIFICATION_ID
)

演示链接