不包括列值的连接记录？

Question

例如，如果我有球体和盒子，球可以在很多盒子里，盒子可以被锁定，我如何选择没有被锁定的球？

balls
  id  name
  ==  ====
  1   in neither
  2   in unlocked
  3   in locked
  4   in both

boxes
  id  locked
  ==  ======
  1   0
  2   1

boxings
  ball_id box_id
  ======= ======
  2       1
  3       2
  4       1
  4       2

我已经提出了这个使用左联接，但它是返回“在两个”的球，我想排除。

SELECT balls.* 
FROM balls 
LEFT OUTER JOIN boxings ON boxings.ball_id = balls.id 
LEFT OUTER JOIN boxes ON boxes.id = boxings.box_id 
WHERE (boxings.box_id IS NULL or boxes.locked = 0)
  AND boxes.id NOT IN (
    SELECT id FROM boxes WHERE locked = 1
    )

预期结果：

id  name
==  ====
1   in neither
2   in unlocked

SQL：http://sqlfiddle.com/#!9/c26ab/4

Answer 1

我猜你的意思是:选择永远不会被锁在盒子里的球。

如果是这样，就会想到一个not exists查询：

select b.*
from balls b
where not exists (select 1
                  from boxings bxb join
                       boxes bo
                       on bxb.box_id = bo.id
                  where bxb.ball_id = b.id and bo.locked = 1
                 );

Answer 2

最直接的方法可能是使用NOT EXISTS。

select id, name 
from balls 
where not exists (
  select NULL 
  from boxings 
  inner join boxes on boxes.id = boxings.box_id 
  where boxes.locked = 1
  and boxings.ball_id = balls.id
)

Answer 3

我发现有趣的是，已经发布的两个答案都采用了相同的稍微奇怪的方法，将一些联接条件放在WHERE子句中：

inner join boxes on boxes.id = boxings.box_id
where boxes.locked = 1

最奇怪的是，我确信 clauses。

无论如何，反连接方法有一个不同的变体：

SELECT *
  FROM balls
 WHERE id NOT IN ( SELECT ball_id
                     FROM boxings
                          NATURAL JOIN 
                          ( SELECT id AS box_id FROM boxes WHERE locked = 1 )
                          NATURAL JOIN 
                          ( SELECT id AS ball_id FROM boxings ) );