如果连接表中没有记录，mysql 结果

Question

我进行了搜索，但找不到答案。如果houses_availability中没有匹配的记录，我需要查看houses_houses中的记录。我知道这与 IS NULL 有关，但我尝试的一切都失败了。请帮忙。

SELECT 
    houses_houses.*, 
    houses_availability.from_date 
FROM 
    houses_houses 
LEFT JOIN 
    houses_availability ON houses_houses.sid=houses_availability.housesid 
WHERE 
    houses_availability.from_date>='2015-09-05' 
AND 
    houses_availability.until_date<'2015-09-11'  
ORDER BY houses_houses.sid LIMIT 0, 9

Answer 1

您检查 WHERE 子句中列出的 LEFT OUTER JOIN 上的 NULL:-

SELECT 
    houses_houses.*, 
    houses_availability.from_date 
FROM houses_houses 
LEFT JOIN houses_availability 
ON houses_houses.sid=houses_availability.housesid 
AND houses_availability.from_date>='2015-09-05' 
AND houses_availability.until_date<'2015-09-11'  
WHERE houses_availability.housesid IS NULL
ORDER BY houses_houses.sid LIMIT 0, 9

值得注意的是，您不应在 WHERE 子句中检查 LEFT OUTER JOINed 表中的任何列，因为这会排除连接后的整行，而不是排除连接前的 LEFT OUTER JOINed 表行。因此，请检查 ON 子句中的日期范围