“左联接”列不在两个表之间的关系中

Question

我有两张桌子：

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，并且数据模型允许从2种信息( benefit & state )中查询plans表中的一个或没有一个计划。首先，使用benefit条件，我们最多可以将plans表过滤为2行，其中1行为clip_state = 0，1行为1。然后，通过使用state并与clip_states表连接，我们可以通过检查将结果降为一行(或零)：

• 如果state位于clip_states表中，请确保两个表之间的clip_state匹配。如果没有匹配，则不会返回结果。
• 如果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

我想我必须使用下面的查询作为解决方案。我已经检查了查询计划，并且看到它比我在问题中的查询要好一步。其想法是先缩小计划，然后加入clip_states表。

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

谢谢大家。

Answer 2

对最新问题的最新答复：

SELECT p.*
FROM   plans p
WHERE  p.benefit = 'A'
AND    NOT  EXISTS (
  SELECT 1
  FROM   clip_states cs
  WHERE  cs.state = 'LA'
  AND    cs.clip_state <> p.clip_state
  )
ORDER  BY p.clip_state
LIMIT  1;

->sqlfiddle

这可能是定义：

1. 为给定的plans查找表benefit中的所有行。
2. 删除clip_state与给定state的表clip_states中的同一列不匹配的行。
3. 从其余的行中，返回clip_state中值最低的行。

还有，因为

clip_state为0或1

您应该使用数据类型boolean而不是int。

Answer 3

一种完全不同的方法：

1. “在clip_states表中添加”clip_state为0的所需状态“，如果该状态尚未在该表中。
2. 内部将结果集加入到plans on clip_state，并在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

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