JDBC on SQL Server, executeQuery() cannot find the result with null

Question

I use jdbc to retrieve data from SQL Server, and ANSI_NULLS is off . So if I run

select * from cj_log where evt = null

I can get the result.

But when I put it in a statement like this

Statement st = DBConnection.getConnection().createStatement();
String sql = "select * from CJ_LOG where EVT=null";
ResultSet rs = st.executeQuery(sql);

The result set is empty. What is the problem here?

What happens when you use select * from CJ_LOG where EVT IS NULL ? — p.campbell
– p.campbell, Commented Mar 2, 2011 at 23:07
ANSI_NULLS is a per-connection setting. If DBConnection.getConnection() is not ensuring that ANSI_NULLS is OFF, it is likely that ANSI_NULLS is in fact ON (the default). — ig0774
– ig0774, Commented Mar 3, 2011 at 2:50

Ben · Accepted Answer · 2011-03-02 23:08:10Z

2

You need to say

where EVT is null

Nothing is ever equal to null, even null.

Think of it like this: Null means "don't know".

You are asking "is something I don't know equal to something I don't know"

The answer is "I don't know". So you don't get any rows.

answered Mar 2, 2011 at 23:08

Ben

35.8k6 gold badges81 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ig0774 Over a year ago

While you are absolutely right in terms of the SQL standard, if, as the question indicated, the SQL Server is actually running with ANSI_NULLs off, select * from CJ_LOG where EVT = NULL is actually equivalent to select * from CJ_LOG where EVT IS NULL. See here.

Ben Over a year ago

Point. Bottom line is "turn ANSI_NULLS on and leave it on". :-)

Raistlin Over a year ago

Sorry for the late response. Thank you all for the help.

Collectives™ on Stack Overflow

JDBC on SQL Server, executeQuery() cannot find the result with null

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related