在mysql中使用outer join连接子表

Question

我有一个关于外部连接的新手问题。我有5张表，结构如下。

employee (employee_id, name, address)
employee_benefits (employee_id, benefit_id, join_date)
insurance (insurance_id, name)
insurance_benefits (insurance_id, benefit_id)
benefit (benefit_id, title)

对于选定的员工，我需要选择福利与员工可用的全部或部分福利集匹配的所有保险。示例：

benefit(
    bn1, 1st benefit;
    bn2, 2nd benefit;
    bn3, 3rd benefit;
    bn4, 4th benefit; 
)

employee(
    123, bill jones, 123 main st;
    321, alex baldwin, 222 state st;
)

employee_benefits(
    123, bn1;
    123, bn2;
    123, bn4; 
    321, bn3;
    321, bn4;
)

insurance(
    ins1, 1st insurance;
    ins2, 2nd insurance;
    ins3, 3rd insurance
)

insurance_benefits(
    ins1, bn1;
    ins1, bn2;
    ins2, bn1;
    ins2, bn3;
    ins2, bn4;
    ins3, bn2;
    ins3, bn4;
)

给123号员工的。我需要选择具有完整福利列表或员工拥有的福利子集的所有保险，但不包括具有不是由员工持有的福利的实例。在上面的示例中，它将是ins1和ins3。

有没有一种方法可以使用外部连接来完成上述任务？

Answer 1

您可以使用外连接来实现这一点，但我认为对子查询使用IN-clauses会更直观。

并非由employee 123持有的福利

SELECT benefit_id
  FROM benefit
 WHERE benefit_id NOT IN
        ( SELECT benefit_id
            FROM employee_benefits
           WHERE employee_id = 123
        )
;

提供上述任何福利的保险计划：

SELECT DISTINCT insurance_id
  FROM insurance_benefits
 WHERE benefit_id NOT IN
        ( SELECT benefit_id
            FROM employee_benefits
           WHERE employee_id = 123
        )
;

不提供上述任何福利的保险计划：

SELECT insurance_id
  FROM insurance
 WHERE insurance_id NOT IN
        ( SELECT DISTINCT insurance_id
            FROM insurance_benefits
           WHERE benefit_id NOT IN
                  ( SELECT benefit_id
                      FROM employee_benefits
                     WHERE employee_id = 123
                  )
        )
;