MySQL WHERE 子句仅当 NOT NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11971554/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
WHERE Clause only IF NOT NULL
提问by DenisCGN
I need help with my SELECT.
我需要帮助我的SELECT.
I got a field that can be NULLand in it there is stored a foreign-key.
我有一个可以是的字段，NULL其中存储了一个外键。
SELECT * FROM beerImage WHERE beerBRewID = brewID AND beerBrandID = beerID <--- this can be NULL
So if it's NULLnothing happens.
所以如果什么NULL都没有发生。
How can I check if beerIDis NOT NULLso I can use "beerBrandID = beerID"?
如何检查，如果beerID是NOT NULL这样我就可以使用"beerBrandID = beerID"？
采纳答案by FJT
If you want to include records where there's no match, you need an outer join
如果要包含没有匹配项的记录，则需要外部联接
SELECT beer.id AS beerID,
beer.barrelID AS barrelID,
beer.imageID AS imageID,
beer.title AS title,
beer.map AS map,
beer.longitude AS longitude,
beer.latitude AS latitude,
brand.name AS brandName,
brew.type AS brewType,
image.name AS imageName,
variation.name AS variationName
FROM brand, brew, image, beer
LEFT OUTER JOIN variation ON variation.ID = beer.VariationID
WHERE beer.id = %s
AND md5(beer.memberID) = %s
AND beer.review = 1
AND brand.ID = beer.brandID
AND brew.ID = beer.brewID
AND image.ID = beer.imageID
回答by Rolice
You probably need something like this:
你可能需要这样的东西：
First example:
第一个例子：
SELECT * FROM beerImage WHERE beerBRewID = brewID AND (beerID IS NULL OR beerBrandID = beerID)
Second Example:
第二个例子：
SELECT * FROM beerImage WHERE beerBRewID = brewID AND beerID IS NOT NULL AND beerBrandID = beerID
The first example will allow you to show records which have beerID null along with the beers which beerBrandID is equal to beerID (both).
第一个示例将允许您显示 beerID 为 null 的记录以及 beerBrandID 等于 beerID（两者）的啤酒。
The second one will return exactly beers which correspond to beerBrandID (excluding NULL beerIDs).
第二个将准确返回与 beerBrandID 对应的啤酒（不包括 NULL beerID）。
回答by Cataclysm
How about using with CASEstatement in where clause like
如何CASE在 where 子句中使用 with语句，例如
WHERE
CASE WHEN beer.id IS NOT NULL
THEN beer.brand_id = 12345
ELSE TRUE
END
回答by FJT
To check for null / not null, use IS NULL / IS NOT NULL
要检查空/非空，请使用 IS NULL / IS NOT NULL
AND beerID IS NOT NULL
回答by mikikg
You can use "IS NULL" or "IS NOT NULL" MySQL comparison functions.
您可以使用“IS NULL”或“IS NOT NULL”MySQL 比较函数。
Read more about this here: http://dev.mysql.com/doc/refman/5.0/en/working-with-null.htmlhttp://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_is-null
在此处阅读更多相关信息：http: //dev.mysql.com/doc/refman/5.0/en/working-with-null.htmlhttp://dev.mysql.com/doc/refman/5.0/en/comparison -operators.html#operator_is-null
