mysql查询语句in查不出为空的数据如何解决？

Question

前端传参fine_username为A时，展示所有数据。现在这样写organization为空的数据过滤掉了。

select * from l where 1=1 ${if(len(organization) == 0,"","and organization = '" + organization + "'")}
and organization in (select distinct organization from m where 1=1 ${if(or(len(fine_username) == 0,fine_username = “A”),””,”and login = '" + fine_username + "'")})

Answer 1

这里有几种解决方法可以让查询语句查出为空的数据：

1.在 IN 子查询中使用 UNION ALL 来将 NULL 值与其他值结合起来。这样，即使 IN 子查询中没有匹配的行，也会返回一个 NULL 值。

SELECT * FROM l WHERE 1 = 1 ${if(len(organization) == 0,"","and organization = '" + organization + "'")}
AND organization IN (
    SELECT distinct organization FROM m WHERE 1 = 1 
    ${if(or(len(fine_username) == 0,fine_username = “A”),””,”and login = '" + fine_username + "'")}
    UNION ALL
    SELECT NULL
)

2.使用 LEFT JOIN 代替 IN 查询。这样会返回所有左表中的行，而右表中可能不存在对应的行。

SELECT l.*
FROM l 
LEFT JOIN m ON l.organization = m.organization
WHERE 1 = 1 ${if(len(organization) == 0,"","and l.organization = '" + organization + "'")}
    ${if(or(len(fine_username) == 0,fine_username = “A”),””,”and m.login = '" + fine_username + "'")}
GROUP BY l.organization

3.使用 OUTER JOIN,比如 LEFT JOIN , RIGHT JOIN or FULL OUTER JOIN

SELECT * FROM l 
LEFT JOIN (SELECT distinct organization FROM m WHERE 1=1 ${if(or(len(fine_username) == 0,fine_username = “A”),””,”and login = '" + fine_username + "'")})
     as m on l.organization = m.organization
WHERE 1=1 ${if(len(organization) == 0,"","and l.organization = '" + organization + "'")}

请注意，在所有这些方法中，要确保至少有一个可以匹配 NULL 值的行，以便能够返回所有为空的数据。
您可以使用 IS NULL 或 IS NOT NULL 来判断表中是否存在空值。例如，在 LEFT JOIN 查询中，您可以在查询结果中查看哪些行右表中没有对应的行。

SELECT l.*
FROM l 
LEFT JOIN m ON l.organization = m.organization
WHERE 1 = 1 ${if(len(organization) == 0,"","and l.organization = '" + organization + "'")}
    ${if(or(len(fine_username) == 0,fine_username = “A”),””,”and m.login = '" + fine_username + "'")}
    AND m.organization IS NULL

另外,你也可以使用 COALESCE 或 IFNULL 来处理空值。 例如，您可以在表达式中使用这些函数来将空值转换为默认值：

SELECT * FROM l 
WHERE 1=1 ${if(len(organization) == 0,"","and l.organization = '" + organization + "'")}
    ${if(or(len(fine_username) == 0,fine_username = “A”),””,”and COALESCE((select distinct organization from m where login = '" + fine_username + "'),'not exists') = l.organization")}

COALESCE 和 IFNULL 都可以将多个参数中的第一个非空值作为其结果返回。在这种情况下，您可以在表达式中使用这些函数来处理空值。

最后,根据实际需求来选择你喜欢的方式,希望这些方法能帮到您。对您有用请及时采纳。