将not在子查询中转换为联接

Question

我不确定在查询中丢失了什么，在查询中，我试图将not在子查询中转换为join。

下面是我最初的查询，非常适合我：

select 
  battery_id
from 
  battery_price
where 
clinic_id = 2
and battery_id not in 
(
    select battery_id
    from battery_price
    where clinic_id = 4569
) 
;

下面是我正在尝试的查询，由于它没有给出任何空字段，所以它无法工作：

select leftq.battery_id as leftf, rightq.battery_id as rightf
from
    (
    select 
      battery_id
    from 
      battery_price 
    where
        clinic_id = 2
    ) as leftq
left join 
        (
            select battery_id
            from battery_price
            where clinic_id = 4569
        )  as rightq
on rightq.battery_id = leftq.battery_id
;

以下是表模式：

CREATE TABLE `battery_price` (
  `battery_price_id` int(11) NOT NULL AUTO_INCREMENT,
  `clinic_id` int(11) NOT NULL DEFAULT '0',
  `battery_id` int(11) NOT NULL DEFAULT '0',
  `retail_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `actual_cost` decimal(10,2) NOT NULL DEFAULT '0.00',
  `provider_cost` decimal(10,2) NOT NULL DEFAULT '0.00',
  `in_use` tinyint(1) NOT NULL DEFAULT '1',
  `sales_tax_id1` int(11) NOT NULL DEFAULT '0',
  `sales_tax_id2` int(11) NOT NULL DEFAULT '0',
  `sales_tax_id3` int(11) NOT NULL DEFAULT '0',
  `sales_tax_id4` int(11) NOT NULL DEFAULT '0',
  `sales_tax_id5` int(11) NOT NULL DEFAULT '0',
  `sales_tax_category_id` int(11) NOT NULL DEFAULT '0',
  `price_locked` tinyint(1) NOT NULL DEFAULT '1',
  `item_number` varchar(50) NOT NULL DEFAULT '',
  `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_edit_user_id` int(11) DEFAULT '0',
  PRIMARY KEY (`battery_price_id`),
  KEY `battery_id` (`battery_id`),
  KEY `battery_id_2` (`battery_id`),
  KEY `battery_id_3` (`battery_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2639 DEFAULT CHARSET=latin1

Answer 1

试试这个：

select
  t.battery_id
from 
  battery_price t
left outer join battery_price u
  on t.battery_id = u.battery_id
 and u.clinic_id = 4569
where t.clinic_id = 2
and u.battery_id is NULL

Answer 2

虽然我不太清楚为什么要选择NULL列(如果不选择，只需省略逗号之后的第二列)，但我认为这个查询解决了您的问题：

select battery_price.battery_id as leftf, rightq.battery_id as rightf
from battery_price left join 
(
  select battery_id
  from battery_price
  where clinic_id = 4569
) as rightq
on rightq.battery_id = battery_price.battery_id
where battery_price.clinic_id = 2 and rightq.battery_id is null;