还包括MySQL SELECT查询中的NULL行 - Also including NULL rows in MySQL SELECT Query

Also including NULL rows in MySQL SELECT Query

本文关键字：NULL 查询 MySQL SELECT 还包括 | 更新日期: 2023-09-27

我们正在尝试从MySQL数据库上的几个表中进行选择。

我们的查询是：

SELECT r.reviews_id, 
       r.customers_name,
       r.date_added,
       rd.reviews_text,
       r.reviews_rating,
       c.customers_email_address
FROM reviews r, reviews_description rd, customers c
WHERE r.customers_id = c.customers_id
  AND r.reviews_id = rd.reviews_id
  AND r.products_id = '74'
  AND r.approved='1'
ORDER BY LENGTH(rd.reviews_text) DESC

然而，表reviews在字段customers_id 上也有NULL条目

命令WHERE r.customers_id = c.customers_id将其限制为仅具有customers_id 的字段

我想知道我们如何也可以选择NULL字段？

我们已经尝试过WHERE (r.customers_id = c.customers_id) OR (r.customers_id IS NULL)，但这不起作用。

提前感谢您的帮助。

不要使用旧的隐式联接语法。使用显式联接-在本例中为left join

select r.reviews_id, r.customers_name, r.date_added, rd.reviews_text, r.reviews_rating, 
       c.customers_email_address 
FROM reviews r
INNER JOIN reviews_description rd ON r.reviews_id = rd.reviews_id
LEFT JOIN customers c ON r.customers_id = c.customers_id 
WHERE r.products_id = '74' 
AND r.approved='1' 
ORDER BY LENGTH(rd.reviews_text) DESC

应该是这样的：

WHERE (r.customers_id = c.customers_id = 0 OR r.customers_id IS NULL)

取而代之的是：

WHERE (r.customers_id = c.customers_id) OR (r.customers_id IS NULL)