SQL 联接两个表并显示具有空值的表

Question

我有两个表，我已经完成了内部连接，我想显示那些具有价格、sale_price、stock_unit字段为空或我没有插入这些价格、sale_price stock_unit的行。

$live_query = "select shower.id,  shower.name, shower.firstimage,shower_pricing.price,  shower_pricing.list_id, shower_pricing.sale_price,shower_pricing.discount,shower_pricing.stock_unit
from shower
inner join shower_pricing
on shower.id=shower_pricing.id 
where shower_pricing.price,shower_pricing.sale_price,shower_pricing.stock_unit is null";

谢谢。

Answer 1

SQL NUL的特殊，你必须做WHERE field IS NULL，因为NULL 不能等于任何东西，包括它自己(即：NULL = NULL 总是假的(。

Answer 2

听起来你需要一个左外联接，而不是一个内联接。

select 
    shower.id, shower.name, shower.firstimage,
    shower_pricing.price, shower_pricing.list_id, 
    shower_pricing.sale_price,shower_pricing.discount,
    shower_pricing.stock_unit
from shower left outer join shower_pricing 
    on shower.id=shower_pricing.id 
where shower_pricing.price is null 
    or shower_pricing.sale_price is null 
    or shower_pricing.stock_unit is null

Answer 3

查询所有在淋浴中没有等效shower_pricing的记录，并查询那些在shower_pricing中确实有条目但给定属性(即价格、sale_price stock_unit字段(的记录是两种不同的情况。

shower_pricing中没有条目的项目

$live_query = "select shower.id,  shower.name, shower.firstimage,shower_pricing.price,  
shower_pricing.list_id, shower_pricing.sale_price,shower_pricing.discount,
shower_pricing.stock_unit
from shower
left join shower_pricing
on shower.id=shower_pricing.id 
where shower_pricing.id is null;

具有条目但给定属性 null 的项目

$live_query = "select shower.id,  shower.name, shower.firstimage,shower_pricing.price,  
shower_pricing.list_id, shower_pricing.sale_price,shower_pricing.discount,
shower_pricing.stock_unit
from shower
inner join shower_pricing
on shower.id=shower_pricing.id 
where shower_pricing.price is null OR shower_pricing.sale_price is null 
OR shower_pricing.stock_unit is null;

Answer 4

如果您希望所有这些都为 NULL，则 Query 应如下所示，

$live_query = "select shower.id,  shower.name, shower.firstimage,shower_pricing.price,  shower_pricing.list_id, shower_pricing.sale_price,shower_pricing.discount,shower_pricing.stock_unit
from shower
inner join shower_pricing
on shower.id=shower_pricing.id 
where shower_pricing.price IS NULL AND shower_pricing.sale_price IS NULL AND shower_pricing.stock_unit IS NULL";

如果您希望任何值为 null，则查询将如下所示：

$live_query = "select shower.id,  shower.name, shower.firstimage,shower_pricing.price,  shower_pricing.list_id, shower_pricing.sale_price,shower_pricing.discount,shower_pricing.stock_unit
from shower
inner join shower_pricing
on shower.id=shower_pricing.id 
where shower_pricing.price IS NULL OR shower_pricing.sale_price IS NULL OR shower_pricing.stock_unit IS NULL";

Answer 5

试试这个：

SELECT s.id, s.name, s.firstimage, sp.price, sp.list_id, sp.sale_price, sp.discount, sp.stock_unit
FROM shower s 
INNER JOIN shower_pricing sp ON s.id = sp.id 
WHERE (sp.price IS NULL OR sp.price = '') 
  AND (sp.sale_price IS NULL OR sp.sale_price = '') 
  AND (sp.stock_unit IS NULL OR sp.stock_unit = '')