sql - 将完全外部连接的条件添加到选择

Question

假设我有表 employees (employee_id, employee_name, department_id) 和 departments (department_id, department_name)。我希望在两个表上进行完全外部联接，与 department_id 恰好是 on 条件。它应始终被填充(使用员工表中的值或部门表中的值——取不为空的值)。

SELECT
   employee_name,
   department_name
FROM
   employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id;

我如何将 department_id 整合到上面的查询中？语法在 postgresql 中。 (我知道我可以加入它 - 但实际上有关 department_id 的信息应该在查询处理期间可用。)

Answer 1

试试这个...

SELECT
   employee_name,
   department_name,
   COALESCE (e.department_id, d.department_id)
FROM
   employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id;