在同一张表中，从另一列中选择MySQL NOT IN。

Question

我想运行一个mysql查询，从表films中选择所有行，其中title列的值不存在于另一列(collection)的所有值中。

这是一个简化版的带有内容的表：

mysql> select * from films;
+----+--------------+--------------+
| id | title        | collection   |
+----+--------------+--------------+
|  1 | Collection 1 | NULL         |
|  2 | Film 1       | NULL         |
|  3 | Film 2       | Collection 1 |
+----+--------------+--------------+

这是我的查询：

mysql> SELECT * FROM films WHERE title NOT IN (SELECT collection FROM films);
Empty set (0.00 sec)

在这个例子中，我想选择标题为 Film 1 和 Film 2 的行，但我的查询没有返回任何行。

以下是表结构：

CREATE TABLE `films` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL DEFAULT '',
  `collection` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

- jessica

---

你没有返回任何行的原因是因为结果集中没有考虑到NULL。我有一个小小的怀疑，如果你用空字符串替换NULL，这可能会解决你的问题。 - Joshua Burns

@JoshuaBurns 我测试了一下，似乎这不是问题所在。 - Kermit

“Not in (Set containing null)” 总是返回零行记录。 - Martin Smith

这是一个重复的问题：https://dev59.com/EXNA5IYBdhLWcg3wVcX6 - thang

Answer 1

SELECT * 
FROM films 
WHERE title NOT IN (SELECT collection FROM films where collection is not null);

SQLFiddle：http://sqlfiddle.com/#!2/76278/1

- user330315

---

+1 我觉得你在我编辑我的带有“IN”版本的时候发布了这个。 - Taryn

谢谢，我接受你的答案，因为它是第一个正确的答案（也是我要使用的答案）。你能解释一下为什么“is not null”让它起作用吗？ - jessica

不为空是必需的，因为不在的语义规定需要集合中不包含任何空元素。请参见文档http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html，在该文档中它说：*为了遵守SQL标准，IN不仅在左侧表达式为空时返回NULL，而且如果在列表中未找到匹配项并且列表中的一个表达式为空，则也返回NULL*。 - thang

Answer 2

您是否尝试使用 NOT EXISTS：

SELECT * 
FROM films f1
WHERE NOT EXISTS (SELECT collection 
                  FROM films f2
                  WHERE f1.title = f2.collection);

请参见附带演示的SQL Fiddle

如果您想使用IN，那么您需要查找不是NULL的值：

SELECT * 
FROM films 
WHERE title NOT IN (SELECT collection 
                    FROM films
                    WHERE collection is not null);

请查看带演示的SQL Fiddle：http://sqlfiddle.com/#!2/92eb7/18 两者的结果均为：

| ID |  TITLE |   COLLECTION |
------------------------------
|  2 | Film 1 |       (null) |
|  3 | Film 2 | Collection 1 |

你当前查询的问题在于——从@Quassnoi提供的答案中窃取：

无论是IN还是NOT IN都会返回NULL，这对于WHERE子句来说是不可接受的条件。

由于你的子查询返回了null值，所以你需要明确地将其排除掉。

- Taryn

---

@jessica，你要求进一步解释为什么需要从子查询中过滤掉null值--请参见此链接或此链接。甚至可以查看由同一人撰写的这个已回答的问题。 - Taryn

Answer 3

请尝试以下内容：

• SQLFIDDLE DEMO

查询：

select a.id, a.planid
from one a
left join one b
on a.planid <> b.iid
where not (b.iid is null)
group by b.id
;

结果：基于我使用的样本表。

ID      PLANID
t15     1
j18     2

编辑以添加：这里使用OP模式

select b.id, b.title
from opschema b
inner join opschema a
on b.title <> a.collection
or b.collection <> a.title
group by b.id 
;

OP SHCEMA SQLFIDDLE DEMO

ID  TITLE
2   Film 1
3   Film 2

- bonCodigo

---

这个答案不符合原帖的模式。 - Kermit

@njk 我的逻辑是正确的 :) 我还添加了 OP 的模式。 - bonCodigo

@Jessica请也试一下这个，然后评论。 :) - bonCodigo

Answer 4

使用外连接的另一种选项

SELECT f.* 
FROM films f LEFT OUTER JOIN films ff
ON f.title = ff.collection
WHERE ff.collection IS NULL

- EvilBob22

Answer 5

CREATE TABLE IF NOT EXISTS `reservation_tables` (
  `res_table_id` int(10) NOT NULL AUTO_INCREMENT,
  `res_table_name` int(10) NOT NULL,
  `date_time` varchar(20) NOT NULL,
  `partyhall_id` int(10) NOT NULL,
  `flag` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`res_table_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;


INSERT INTO `reservation_tables` (`res_table_id`, `res_table_name`, `date_time`, `partyhall_id`, `flag`) VALUES
(1, 1, '2014-08-17 12:00 am', 7, '1'),
(2, 2, '2014-08-17 12:00 am', 7, '1'),
(3, 3, '2014-08-18 12:00 am', 8, '1'),
(4, 4, '2014-08-18 12:00 am', 8, '1'),
(5, 1, '2014-08-25 12:00 am', 12, '1'),
(6, 2, '2014-08-25 12:00 am', 12, '1'),
(7, 3, '2014-08-20 12:00 am', 23, '1'),
(8, 4, '2014-08-20 12:00 am', 23, '1');

我必须选择可用的表名来匹配日期时间。

例如：选择可用的表名，其中日期时间为2014-08-18 12:00 am。

解决方案查询如下： 我确信这个可以很好地工作。

SELECT distinct res_table_name FROM reservation_tables WHERE `res_table_name` NOT IN   
(SELECT `res_table_name` FROM reservation_tables where `date_time` = '2014-08-17 12:00 am')

- Aham Brammashmi Pathy