SQLite-WHERE子句中IS和=（等于）之间的差异。 （使用JDBC PreparedStatement） [英] SQLite - Difference between IS and = (equals) in WHERE clause. (using JDBC PreparedStatement)
问题描述
编辑：与a_horse_with_no_name交谈我发现SQLite中的 IS有些不同，允许使用 IS在NULL 和值之间进行比较： stackoverflow.com/a/9102445/1470058 。这为我消除了很多困惑。谢谢：
talking with a_horse_with_no_name I found that "IS" is a little bit different in SQLite allowing comparisons between NULL and values using "IS": stackoverflow.com/a/9102445/1470058. This clears up a lot of confusion for me. Thanks:
IS和IS NOT运算符的作用类似于=和！=，除非其中一个或两个操作数
为NULL 。在这种情况下，如果两个操作数均为NULL，
，则IS运算符的值为1（真），而IS NOT运算符
的值为0（假）。如果一个操作数为NULL而另一个操作数为非
，则IS运算符的计算结果为0（假），而IS NOT运算符为
1（真）。 IS或IS NOT表达式无法将
的值评估为NULL。运算符IS和IS的优先级与=相同。
The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.
我很困惑在SQLite中是关键字 IS。
I am confused about the keyword "IS" in SQLite.
我正在开发一个项目，该项目要求我使用Java准备好的Statements。我遇到过两种WHERE子句：
I'm working on a project that requires me to use Java's prepared Statements. I've come across two types of WHERE clauses:
SELECT * FROM table WHERE column = ?
和
SELECT * FROM table WHERE column IS NULL
我的问题是：等于符号 =还是单词 IS？ Google搜索显示，大多数人使用=进行价值比较，使用IS进行与空值的比较。但是，我尝试了一些我自己的SQLite查询。
My question is there a difference a major between the equals sign "=" or the word "IS"? Google searches show that most people use = for value comparison and IS for comparing to null. However I attempted a few SQLite queries of my own.
- IS将返回 列IS NULL 和 列IS值。
- =将返回 列=值的预期结果，但不是表示 列= NULL 。
- "IS" will return results as expected for "column IS NULL" and for "column IS value".
- "=" will return results as expected for "column = value" but not for "column = NULL".
我的问题是在两种情况下都可以使用 IS而不会出现意外结果吗？我想为一个对列的约束可能为null或不为null的查询做一个准备好的语句。我希望我一直有道理。
My question is can I use "IS" for both situations without unexpected results? I would like to make one prepared statement for a single query who's constraint on a column may or may not be null. I hope I have been making sense.
为了简化我所说的一切，我可以使用以下Java代码而不会因使用 IS而产生意外影响吗？
To simplify everything I said, can I use the following Java code without unexpected repercussions from using "IS":
private static final String queryProjectSql = "SELECT * FROM fields WHERE project IS ?";
// later in a method
sqlStatement = connection.prepareStatement(queryProjectSql);
sqlStatement.setString(1, project); //Project may be a String or null
谢谢
推荐答案
IS关键字必须与NULL值一起使用。 （例如，IS NULL或IS NOT NULL）。
The "IS" keyword is strictly to be used with NULL value. (eg. IS NULL or IS NOT NULL).
如果要查找匹配项，则需要使用=。
If you are looking for a match you need to use =.
如果您担心要处理空列值，则应使用isnull（）函数包装所关注的列。
If you are concerned about handling null column values then you should wrap the columns of concern with the isnull() function.
SELECT Col1
, Col2
, isnull(Col3,'') AS Col3
FROM myTable
Where col1 = 'somevalue'
这篇关于SQLite-WHERE子句中IS和=（等于）之间的差异。 （使用JDBC PreparedStatement）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
