MySQL - 选择字段不相等的行

Question

我的 SQL 查询有一个小问题:“TableA”的字段“TableA.b”包含“TableB”的 ID。我想从“TableB”中选择 ID 不等于任何字段“TableA.b”的所有行。换句话说，我需要 TableB 中未被 TableA 中的任何行引用的每一行 field 。我尝试了这样的查询:

SELECT DISTINCT TableB.* FROM TableA, TableB Where TableA.b != TableB.ID

但结果包含同样由否定返回的行，即两个字段具有相同的值。有任何想法吗？

Answer 1

您需要的是LEFT (or RIGHT) JOIN .

SELECT TableB.* FROM TableA 
  LEFT JOIN TableB on TableA.b = TableB.ID
    WHERE TableA.b IS NULL

虽然可以对子查询执行与其他一些答案中相同的操作。连接通常是 faster .

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things. Today, MySQL Server and many other modern database systems offer a wide range of outer join types.