如果子查询返回 null，Mysql 返回 null [英] Mysql return null if subquery returns null

问题描述

伙计们，有时我的子查询返回 null 没关系，它应该返回 null，但在这些情况下，我希望我的父选择"返回 null.

Hy guys, sometimes my subquery return null which is ok, it should return null, but in those cases i would like my "parent select" to return null.

这可能吗?

如果是，那又如何?

代码如下:

SELECT 
    `company`.`companyID`,
    `company`.`companyName`, 
    `company`.`companyName`, 
    `company`.`companyEmail`, 
    `company`.`contactEmail`,
    `company`.`companyTel`,                 
    (
        SELECT 
            `package_map`.`szekhely_endDate`
        FROM 
            `package_map` 
        WHERE 
            `package_map`.`companyID` = `company`.`companyID`
        AND 
            `package_map`.`active` = 1
        AND 
            `package_map`.`szekhely_endDate` > NOW()
        ORDER BY 
            `package_map`.`szekhely_endDate` DESC 
        LIMIT 1
    ) as endDate,
CASE 
    WHEN endDate = NULL

FROM 
    `company` 
WHERE 
    `company`.`companyBase` = 'some address' 
AND 
    `company`.`szekhely_check_out` = 0

推荐答案

在两个表之间使用普通的 INNER JOIN.如果 package_map 表中没有匹配的行，则结果中不会有一行.要获取最新的 endDate，请使用 MAX() 函数.

Use an ordinary INNER JOIN between the two tables. If there's no matching rows in the package_map table, there won't be a row in the result. To get the latest endDate, use the MAX() function.

SELECT 
    `company`.`companyID`,
    `company`.`companyName`, 
    `company`.`companyName`, 
    `company`.`companyEmail`, 
    `company`.`contactEmail`,
    `company`.`companyTel`,                 
    MAX(package_map.szekhely_endDate) AS endDate
FROM company
INNER JOIN package_map ON `package_map`.`companyID` = `company`.`companyID`
WHERE 
    `company`.`companyBase` = 'some address' 
AND 
    `company`.`szekhely_check_out` = 0
AND 
    `package_map`.`active` = 1
AND 
    `package_map`.`szekhely_endDate` > NOW()
GROUP BY `company`.`companyID`

这篇关于如果子查询返回 null，Mysql 返回 null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！