如何将PostgreSQL中的内连接查询转换为子查询实现相同结果？

我目前用以下内连接语句关联Student、Gender、Race三张表，查询学生姓名、性别、种族信息：

select (first_name || ' ' ||last_name) as "Student Name", gender, race from student
inner join gender on student.gender_id = gender.gender_id 
inner join race on student.race_id = race.race_id

现在想知道如何在PostgreSQL中改用子查询实现相同的查询结果。

表结构与测试数据 #

创建表和插入测试数据的SQL如下：

create table Student(Student_ID int primary key, first_name varchar(30), last_name varchar(30), gender_id int, race_id int, constraint gender_id_fk foreign key(gender_id) references gender(gender_id), constraint race_id_fk foreign key(race_id) references race(race_id));
create table Gender(Gender_ID int primary key, Gender varchar(30));
create table Race(Race_ID int primary key, Race varchar(30));

insert into Gender values(1, 'Male');
insert into Gender values(2, 'Female');
insert into Gender values(3, 'Other');

insert into Race values(1, 'White');
insert into Race values(2, 'Black');
insert into Race values(3, 'Asian');
insert into Race values(4, 'Native American');
insert into Race values(5, 'Other');

insert into Student values(1, 'John', 'Smith', 1, 1);
insert into Student values(2, 'Sara', 'Lee', 2, 3);
insert into Student values(3, 'Sam', 'Crawford', 3, 5);
insert into Student values(4, 'Cindy', 'Williams', 2, 2);

三张表的关联关系为：Student表通过gender_id外键关联Gender表的主键，通过race_id外键关联Race表的主键。期望输出结果包含学生全名、性别、种族三列，与内连接查询的结果完全一致。

子查询实现方式 #

方式1：SELECT子句中使用标量子查询

针对Student表的每一行，分别通过子查询从Gender和Race表中获取对应的名称：

select 
    (first_name || ' ' || last_name) as "Student Name",
    (select gender from gender where gender_id = student.gender_id) as gender,
    (select race from race where race_id = student.race_id) as race
from student;

由于外键约束保证了每个gender_id和race_id在对应表中都有匹配项，标量子查询会返回唯一值，效果和内连接完全一致。

方式2：FROM子句中使用派生表（子查询）

先通过子查询对各表做预处理生成派生表，再进行关联查询：

select 
    s.student_name as "Student Name",
    g.gender,
    r.race
from (
    select 
        (first_name || ' ' || last_name) as student_name,
        gender_id,
        race_id
    from student
) s
join (select gender_id, gender from gender) g on s.gender_id = g.gender_id
join (select race_id, race from race) r on s.race_id = r.race_id;

这里用子查询包装原表后再执行连接逻辑，最终输出结果和原内连接语句完全相同。

内容的提问来源于stack exchange，提问作者Sam