sql - 如何根据第一个 SELECT 的结果添加第二个 SELECT？

Question

我有一个带有连接的复杂 SELECT 查询，但为了简化它看起来像这样:

SELECT name, surname FROM table1;

The resulting output is:

name     surname
john     smith
jacob    smath
judy     smooth

我还有另一个复杂的查询，假设它选择薪水并使用姓名作为 where 参数:

SELECT salary FROM table2 where name = "John" and surname = "Smith"

对于每个名字和姓氏组合，它只返回一个值 - salary。

我想以某种方式组合这些查询，以便将第二个选择连接到第一个选择，如下所示:

name     surname   salary
john     smith     100
jacob    smath     50
judy     smooth    80

我试过类似的东西(伪代码):

SELECT name, surname FROM table1
as data
full outer join(
   SELECT salary FROM table2 where name = data.name and surname = data.surname
)

但是它说:

There is an entry for table "data" but it cannot bereferenced from this part of the query.

我该怎么做？

Answer 1

您可以使用 IN 子句根据 table1 的查询结果从 table2 中选择数据:

SELECT *
FROM table2
WHERE (name, surname) IN (SELECT name, surname FROM table1);

或者加入得到合并的结果:

SELECT *
FROM table2 t2
JOIN (SELECT name, surname FROM table1) t1 USING (name, surname);