如何在PostgreSQL中加入两个“未命名表/选择”？

您可以通过将查询插入行来为它们分别命名：

SELECT * FROM
(

--begin your first query
  SELECT id, phone_number
  FROM student
  WHERE id IN
    (SELECT account_id
    FROM loan
    WHERE id IN
        (SELECT loan_id
        FROM fine
        WHERE paid_amount < fine_amount))
  UNION
  SELECT id, phone_number
  FROM administrator
  WHERE id IN
    (SELECT account_id
    FROM loan
    WHERE id IN
        (SELECT loan_id
        FROM fine
        WHERE paid_amount < fine_amount))
--end your first query

) t1
INNER JOIN
(

--begin your second query
  SELECT
    account_id,
    EXTRACT ('day' FROM (NOW()::timestamp - expiry_date::timestamp))
  FROM loan
  WHERE id IN
    (SELECT loan_id
    FROM fine
    WHERE paid_amount < fine_amount)
--end your second query


) t2
ON
t1.id = t2.account_id

您还可以将每个查询转换为命名的CTE：

WITH table1 AS (

  SELECT id, phone_number
  FROM student
  WHERE id IN
    (SELECT account_id
    FROM loan
    WHERE id IN
        (SELECT loan_id
        FROM fine
        WHERE paid_amount < fine_amount))
  UNION
  SELECT id, phone_number
  FROM administrator
  WHERE id IN
    (SELECT account_id
    FROM loan
    WHERE id IN
        (SELECT loan_id
        FROM fine
        WHERE paid_amount < fine_amount))
),

table2 as (
  SELECT
    account_id,
    EXTRACT ('day' FROM (NOW()::timestamp - expiry_date::timestamp))
  FROM loan
  WHERE id IN
    (SELECT loan_id
    FROM fine
    WHERE paid_amount < fine_amount)
)

--begin the query that joins the CTEs
SELECT * FROM table1
JOIN table2
ON table1.id = table2.account_id;

我很惊讶您可以简化此查询，如下所示：

SELECT
  people.*,
  EXTRACT ('day' FROM (NOW()::timestamp - l.expiry_date::timestamp))
FROM
(
  SELECT 'student' as typ, id, phone_number FROM student
  UNION ALL
  SELECT 'admin', id, phone_number FROM administrator
) people

INNER JOIN loan l ON people.id = l.account_id
INNER JOIN fine f ON l.id = f.loan_id
WHERE f.paid_amount < f.fine_amount

您应该养成对查询中放入的所有内容进行别名别名的习惯（除非它是仅过滤一个表的子查询），然后使用别名：

SELECT s.id as studentid, l.id as loanid FROM
  student s
  INNER JOIN loan l ON s.id = l.account_id

原因很多，但是通过使用别名并完全限定所有列引用，可以防止将来有人将新列添加到其中一个表中（其中名称与现有列冲突）时查询中断。通过别名表，您可以多次连接表。例如，一个学生有一个term_address和一个家庭住址：

SELECT * FROM
  student s
  INNER JOIN address ahome on s.home_address_id = ahome.id
  INNER JOIN address aterm on s.term_address_id = aterm.id

这样，您就不需要术语地址表和家庭地址表-地址表存储所有地址，并且您对别名的别名也不同。记录的不同子集参与每个联接

代表数据块的任何内容都可以被别名，无论是表还是子查询，或者可能还有其他一些东西。

SELECT * FROM
  (subquery here) aliasForSubquery
  INNER JOIN
  (anther subquery) aliasForANotherSubquery
  ON
    aliasForSubquery.column = aliasForAnotherSubquery.column

唯一不需要（不需要）别名的是创建IN(...)列表的子查询