我有2张桌子.第一个“产品”(id).第二 – “product_has_option_value”(product_id,option_value_id).
如何获得没有option_value_id的产品ID(例如10)? “product_has_option_value”具有多对多的关系.
好.我想我找到了解决方案:
select p.id
from product p
where p.id not in
(select distinct(product_id)
from product_has_option_value
where option_value_id=543)
解决方法:
你想要什么被称为“反加入”(或“反半连接”).在MySQL中有三种主要的方法可以进行这种查询(还有一些在其他DBMS中,已经实现了EXCEPT运算符):
> NOT IN子查询(这里不需要DISTINCT,但是如果该列可以为空,则可能需要添加检查AND pov.product_id IS NOT NULL):
select p.id
from product as p
where p.id not in
( select pov.product_id
from product_has_option_value as pov
where pov.option_value_id = 543
and pov.product_id is not null
) ;
>使用IS NULL检查LEFT JOIN:
select p.id
from product as p
left join product_has_option_value as pov
on pov.option_value_id = 543
and pov.product_id = p.id
where pov.product_id is null ;
> NOT EXISTS相关子查询:
select p.id
from product as p
where not exists
( select 1
from product_has_option_value as pov
where pov.option_value_id = 543
and pov.product_id = p.id
) ;
标签:mysql,select,if-not-exists
来源: https://codeday.me/bug/20190807/1606092.html