MySQL - 选择那些在另一张表中不存在作为外键的ID行

Question

在我的rails应用程序中，我有两个表 - device_ports 和 circuits。我的目标是获取一个device_ports的列表，其中id没有在circuits表的physical_port_id列中使用。

我以前在其他表上做过类似的事情，但这里我的查询只返回一行，而它应该返回23行 - 对于这个设备有24个设备端口，其中一个正在使用。

select id, name, device_id, multiuse
from device_ports
where (device_id = 6 and multiuse = 1)
or device_ports.id not in (select physical_port_id from circuits)

因此，此查询获取所有多用途端口（因此即使在外键中引用了id，此行仍应返回），并且还应获取设备ID为6但未在电路中引用的所有行，但仅返回多用途行。

查询结果如下：

id  | name   | device_id | multiuse
------------------------------------
268 | test-1 |     6     |    1

我尝试创建一个 SQL Fiddle，但构建似乎超时了。

CREATE TABLE `device_ports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) DEFAULT NULL,
  `name` tinytext,
  `speed` tinytext,
  `multiuse` tinyint(1) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=291 DEFAULT CHARSET=latin1;

INSERT INTO `device_ports` (`id`, `device_id`, `name`, `speed`, `multiuse`, `created_at`, `updated_at`)
*emphasized text*VALUES
(1, 1, 'Test Device Port', '100', 0, NULL, NULL),
(2, 1, 'Test Port 2', '300', 1, NULL, NULL),
(289, 6, 'test-22', '100', 0, NULL, NULL),
(290, 6, 'test-23', '100', 0, NULL, NULL),
(288, 6, 'test-21', '100', 0, NULL, NULL),
(287, 6, 'test-20', '100', 0, NULL, NULL),
(286, 6, 'test-19', '100', 0, NULL, NULL),
(284, 6, 'test-17', '100', 0, NULL, NULL),
(285, 6, 'test-18', '100', 0, NULL, NULL),
(283, 6, 'test-16', '100', 0, NULL, NULL),
(282, 6, 'test-15', '100', 0, NULL, NULL),
(281, 6, 'test-14', '100', 0, NULL, NULL),
(280, 6, 'test-13', '100', 0, NULL, NULL),
(279, 6, 'test-12', '100', 0, NULL, NULL),
(278, 6, 'test-11', '100', 0, NULL, NULL),
(277, 6, 'test-10', '100', 0, NULL, NULL),
(276, 6, 'test-9', '100', 0, NULL, NULL),
(275, 6, 'test-8', '100', 0, NULL, NULL),
(274, 6, 'test-7', '100', 0, NULL, NULL),
(273, 6, 'test-6', '100', 0, NULL, NULL),
(272, 6, 'test-5', '100', 0, NULL, NULL),
(271, 6, 'test-4', '100', 0, NULL, NULL),
(270, 6, 'test-3', '100', 0, NULL, NULL),
(269, 6, 'test-2', '100', 0, NULL, NULL),
(268, 6, 'test-1', '100', 1, NULL, NULL),
(267, 6, 'test-0', '100', 0, NULL, NULL);


CREATE TABLE `circuits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organisation_id` int(11) DEFAULT NULL,
  `physical_port_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=latin1;

INSERT INTO `circuits` (`id`, `organisation_id`, `physical_port_id`)
VALUES (1, 125, 267);

- martincarlin87

---

我在我的端上尝试了上面粘贴的代码，它给了我25条记录。 - plain jane

你的第一张表是 MyISAM，第二张表是 InnoDB.. 我希望这不会有影响.. - plain jane

@Sonali 发现得好...不确定这是否会产生影响，25听起来是正确的，因为这应该会从设备6中给你23个结果和2个来自设备1？我忘记在查询中添加 AND device_id = 6 来过滤它们了。 - martincarlin87

这意味着您的查询是正确的 :) - plain jane

@Sonali 嗯，不同的引擎可能会产生影响。尽管存在差异，但使用Drew的原始答案，我可以接近我想要的结果。 - martincarlin87

我很惊讶于这个事实，即不同的引擎对查询产生如此大的影响... - plain jane

Answer 1

您可以尝试使用LEFT OUTER JOIN：

SELECT DISTINCT d.id, d.name, d.device_id, d.multiuse
 FROM device_ports d
 LEFT OUTER JOIN circuits c ON c.physical_port_id = d.id 
WHERE 
 (c.physical_port_id IS NULL AND d.device_id = 6) 
 OR (d.multiuse = 1 AND d.device_id = 6) 
 ORDER BY d.id 

这个查询有几种技术，可以看一下 NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL 的区别是什么?.

- Drew MacInnis

---

我对您的原始答案很接近，但编辑后只给了我一个行。这是我最新的尝试：SELECT d.id, d.name, d.device_id, d.multiuse FROM device_ports d LEFT OUTER JOIN circuits c ON c.physical_port_id = d.id WHERE (c.physical_port_id IS NULL AND d.device_id = 6) OR (d.multiuse = 1 AND d.device_id = 6) ORDER BY id ASC，几乎完美，唯一的“错误”是，如果引用多用途端口，则会在结果中出现两次。可能只需要按ID分组，但希望查询更加智能一些。 - martincarlin87

好的。我编辑了我的答案，使用了你的WHERE子句。我添加了一个DISTINCT，这样是否解决了你重复的行？ - Drew MacInnis

Answer 2

SELECT p.* 
  FROM device_ports p 
  LEFT 
  JOIN circuits c 
    ON c.physical_port_id = p.id 
 WHERE p.device_id = 6 
   AND multiuse = 1 
   AND c.id IS NULL;

- Strawberry