SQLite-WHERE子句中IS和=（等于）之间的差异。（使用JDBC PreparedStatement）

本文介绍了SQLite-WHERE子句中IS和=（等于）之间的差异。（使用JDBC PreparedStatement）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

编辑：与a_horse_with_no_name交谈我发现SQLite中的 IS有些不同，允许使用 IS在NULL 和值之间进行比较：。这为我消除了很多困惑。谢谢：

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:

我很困惑在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）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

my