如何将两个SQL查询结果合并为含独立列的单一结果表？

我有两个SQL查询：
1.

select student.name , subject.name as s1 , teacher.name
from student
inner join teacher
on student.[subj2-t-id] = teacher.[t-id]
inner join subject
on teacher.[s-id] = subject.[s-id]

select student.[std-id] , subject.name as s2 , teacher.[t-id]
from student
inner join teacher
on student.[subj1-t-id] = teacher.[t-id]
inner join subject
on teacher.[s-id] = subject.[s-id]

我希望合并这两个查询的结果，生成一个包含6列的表，其中3列来自第一个查询，另外3列来自第二个查询，两部分列相互独立。

测试数据 #

DECLARE @Department TABLE ([d-id] INT, name NVARCHAR(50))
DECLARE @Subject TABLE ([s-id] INT, name NVARCHAR(50), [d-id] INT)
DECLARE @Teacher TABLE ([t-id] INT, name NVARCHAR(50), [d-id] INT, [s-id] INT)
DECLARE @Student TABLE ([std-id] INT, name NVARCHAR(50), [d-id] INT, [subj1-t-id] INT, [subj2-t-id] INT)

INSERT INTO @Department ([d-id], name) VALUES 
(111, 'English'),
(112, 'IT') 

Subject表数据：

s-id    name       d-id
234    Eng-com      111       
235    Data Str     112       
236      OS         112       
237    Grammer      111 

Teacher表数据：

t-id        name        d-id        s-id
60          Ahmed       112         236       
61          Ali         111         234       
62          Nawaz       111         237       
63          Imran       112         235    

Student表数据：

std-id       name       d-id     subj1-t-id  subj2-t-id
20          Hassan      112         63          61        
21          Momin       112         60          62        
22          Hussain     111         61          63        
23          Najeeb      112         63          62        
24          Qasim       111         62          60  

说明：Student表中subj1-t-id和subj2-t-id是Teacher表t-id的外键；Teacher表中[d-id]和[s-id]分别是Department和Subject表的外键；Subject表中[d-id]是Department表的外键。

你可以通过两种方式实现需求：

方式一：子查询关联 #

将两个查询分别作为子查询，通过Student表的主键std-id进行内连接，把同一学生的两组信息合并到一行：

SELECT 
    q1.student_name, q1.s1, q1.teacher_name_subj2,
    q2.std_id, q2.s2, q2.teacher_id_subj1
FROM (
    -- 第一个查询的子查询，重命名列避免冲突
    SELECT 
        student.[std-id] AS std_id,
        student.name AS student_name, 
        subject.name AS s1, 
        teacher.name AS teacher_name_subj2
    FROM student
    INNER JOIN teacher ON student.[subj2-t-id] = teacher.[t-id]
    INNER JOIN subject ON teacher.[s-id] = subject.[s-id]
) q1
INNER JOIN (
    -- 第二个查询的子查询，重命名列避免冲突
    SELECT 
        student.[std-id] AS std_id,
        student.[std-id], 
        subject.name AS s2, 
        teacher.[t-id] AS teacher_id_subj1
    FROM student
    INNER JOIN teacher ON student.[subj1-t-id] = teacher.[t-id]
    INNER JOIN subject ON teacher.[s-id] = subject.[s-id]
) q2 ON q1.std_id = q2.std_id

方式二：直接多表关联（推荐） #

直接在主查询中多次关联Teacher和Subject表，写法更简洁，性能更优：

SELECT 
    s.name AS student_name,
    subj2.name AS s1,
    t2.name AS teacher_name_subj2,
    s.[std-id] AS std_id,
    subj1.name AS s2,
    t1.[t-id] AS teacher_id_subj1
FROM student s
-- 关联subj1对应的老师和科目
INNER JOIN teacher t1 ON s.[subj1-t-id] = t1.[t-id]
INNER JOIN subject subj1 ON t1.[s-id] = subj1.[s-id]
-- 关联subj2对应的老师和科目
INNER JOIN teacher t2 ON s.[subj2-t-id] = t2.[t-id]
INNER JOIN subject subj2 ON t2.[s-id] = subj2.[s-id]

结果说明 #

执行后会得到每行对应一个学生的完整信息：

• student_name、s1、teacher_name_subj2对应第一个查询的结果（subj2的科目和老师信息）
• std_id、s2、teacher_id_subj1对应第二个查询的结果（subj1的科目和老师信息）

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