Oracle选择性连接查询：员工表与部门表关联逻辑实现

表结构与数据 #

EMPLOYEE表 #

CREATE TABLE EMPLOYEE
   (    EMP_ID NUMBER, 
    NAME VARCHAR2(100 BYTE), 
    SALARY NUMBER, 
    DEP_ID NUMBER, 
    ORG_ID NUMBER
   );

insert into employee values (1, 'Mark', 6000, 44, 5);
insert into employee values (2, 'John', 4000, 46, 5);
insert into employee values (2, 'John', 4500, 2, 5);
insert into employee values (3, 'Terry', 5000, 44, 5);
insert into employee values (4, 'Carol', 15000, 48, 5);
insert into employee values (4, 'Carol', 15500, 4, 5);
insert into employee values (5, 'Lewis', 18000, 46, 5);

DEPARTMENT表 #

CREATE TABLE DEPARTMENT
   (    DEP_ID NUMBER, 
    DEP_NAME VARCHAR2(100 BYTE), 
    ORG_ID NUMBER
   );

insert into DEPARTMENT values (null, 'NYD', 5);
insert into DEPARTMENT values (2, 'HR', 5);
insert into DEPARTMENT values (4, 'Analyst', 5); 

业务逻辑说明 #

EMPLOYEE表中，部分员工存在两条记录：一条包含有效DepartmentID（即该ID存在于DEPARTMENT表中），一条包含无效DepartmentID；其余员工仅存在一条含无效DepartmentID的记录。

查询需求 #

将EMPLOYEE表与DEPARTMENT表关联，实现以下逻辑：

• 若员工有两条记录，仅选取含有效DepartmentID的记录；
• 若员工仅存在一条含无效DepartmentID的记录，则关联DEP_ID为null的默认部门名称（NYD）。

期望查询结果 #

| EMP_ID  |   NAME    | SALARY | DEP_NAME |
|---------|-----------|--------|----------|
|    1    |   Mark    |  6000  |   NYD    |
|    2    |   John    |  4500  |    HR    |
|    3    |   Terry   |  5000  |   NYD    |
|    4    |   Carol   |  15500 | Analyst  |
|    5    |   Lewis   |  18000 |   NYD    |

完善后的SQL语句 #

WITH ranked_employees AS (
    SELECT 
        emp.*,
        -- 给有效部门ID的记录标记优先级0，无效的标记1，同一员工取优先级最高的记录
        ROW_NUMBER() OVER (PARTITION BY emp.EMP_ID 
                           ORDER BY CASE WHEN EXISTS (SELECT 1 FROM DEPARTMENT d WHERE d.DEP_ID = emp.DEP_ID) 
                                         THEN 0 ELSE 1 END) AS rn
    FROM EMPLOYEE emp
)
SELECT 
    re.EMP_ID,
    re.NAME,
    re.SALARY,
    -- 优先取匹配到的部门名称，无匹配则取同ORG_ID下的默认部门NYD
    COALESCE(d.DEP_NAME, (SELECT DEP_NAME FROM DEPARTMENT WHERE DEP_ID IS NULL AND ORG_ID = re.ORG_ID)) AS DEP_NAME
FROM ranked_employees re
LEFT JOIN DEPARTMENT d 
    ON re.DEP_ID = d.DEP_ID 
    AND re.ORG_ID = d.ORG_ID -- 确保同组织下的部门关联
WHERE re.rn = 1; -- 每个员工仅取优先级最高的一条记录

逻辑说明 #

1. 筛选有效员工记录：通过窗口函数ROW_NUMBER()按员工ID分组，将存在于DEPARTMENT表的有效部门ID记录排序在前（rn=1），确保每个员工只保留有效记录（若存在），否则保留原记录。
2. 关联部门表：使用LEFT JOIN关联部门表，匹配部门ID和组织ID；当无匹配时，通过COALESCE函数获取同组织下的默认部门名称（NYD）。

内容的提问来源于stack exchange，提问作者satya prakash Panigrahi