sql - 列上的左连接不在两个表之间的关系中

Question

我有 2 个表:

CREATE TABLE plans
    (id int, benefit varchar(5), clip_state int);

INSERT INTO plans
    (id, benefit, clip_state)
VALUES
    (1, 'A', 1),
    (2, 'A', 0),
    (3, 'B', 0),
    (4, 'C', 0);

CREATE TABLE clip_states
    (state varchar(2), clip_state int);

INSERT INTO clip_states
    (state, clip_state)
VALUES
    ('LA', 1),
    ('FL', 0);

请注意，clip_state 为 0 或 1，数据模型允许在 plans 表中查询一个或没有计划2 信息:benefit & state。首先，使用 benefit 条件，我们可以将 plans 表过滤到最多 2 行，一个 clip_state = 0，一个 = 1。然后通过使用 state 并加入 clip_states 表，我们可以通过检查将结果减少到一行(或零):

• 如果 state 在 clip_states 表中，请确保 clip_state 在 2 个表之间匹配。如果不匹配，则不返回任何结果。
• 如果 state 不存在于 clip_states 中，它只匹配 plans 表中具有 clip_state< 的行 = 0。

这是我的查询:

SELECT id, p.clip_state, benefit
FROM plans p
LEFT JOIN clip_states cs ON STATE IN ('LA')
WHERE benefit = 'A' AND
(p.clip_state = cs.clip_state OR (p.clip_state = 0 AND cs.clip_state IS NULL));

如您所见，左联接 很奇怪，因为它没有联接两个表之间的关系。所以，我的问题是:

• 有这样的加入是正常的吗？
• 是否有更好的解决方案(清洁和性能)？

您可以在以下位置查看我的解决方案:http://sqlfiddle.com/#!1/8912d/53

更新1:我已经更新了上面问题的文本。

更新 2:更多信息

1. 如果 state 不在 clip_states 表中，则其 clip_state 隐式等于 0。否则，其 clip_state在表中。
2. 根据两个给定的信息:benefit 和 state，在 plans 表中找到一行，其中 plans.clip_state = 给定 state 的 clip_state。当然，如果不匹配，则不会返回任何行。

Answer 1

一种完全不同的方法:

1. 将所需状态“添加”到 clip_states 表，如果该状态不在表中，则 clip_state 为 0。

2. 在 clip_state 上将结果集内部加入到 plans 中，另外在 state 上过滤。

它可能是这样的:

SELECT p.*
FROM plans p
INNER JOIN (
  SELECT state, clip_state FROM clip_states
  UNION ALL
  (
  SELECT 'LA', 0
  EXCEPT
  SELECT state, 0 FROM clip_states
  )
) cs
ON p.clip_state = cs.clip_state
WHERE p.benefit = 'A'
  AND cs.state = 'LA'
;

这是“实际”查询:http://sqlfiddle.com/#!1/b0feb/7

似乎符合您的要求，除非我又错过了什么。