aspose file tools*
The moose likes JDBC and the fly likes SQL Query - Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Query - "in" clause vs "="" Watch "SQL Query - "in" clause vs "="" New topic
Author

SQL Query - "in" clause vs "="

SAM KUMAR
Greenhorn

Joined: Jan 02, 2002
Posts: 25
Hi:

I was wondering whether "IN" clause is faster or "EQUAL" is faster. For ex:

Query #1
SELECT * FROM dept
WHERE dept_name = 'SALES'

Query #2
SELECT * FROM dept
WHERE dept_name in ('SALES')

In some scneario, I need to pass multiple dept_name, in that case I need to use "IN" clause.

I was wondering if there is any performance impact if I use "IN" clause even though I am passing one dept name?

Thanks in advance.

SK
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30776
    
157

Sam,
In terms of performance, both should be the same. I have tried it in db2 and Oracle, so I know that the optimizer does the same thing in those databases.

Having said that, you should use a prepared statement so you gain the efficiency when repeating the same number of in clause parameters.
For example, for one dept, your string would be:
SELECT * FROM dept WHERE dept_name in (?)
For three depts, your string would be:
SELECT * FROM dept WHERE dept_name in (?,?,?)
and so forth.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

I guess it's rdbms-dependent.

And you may replace:



It should be easy to write a performance-test for your rdbms.


http://home.arcor.de/hirnstrom/bewerbung
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30776
    
157

The examples that Stefan posted also perform the same in DB2 and Oracle.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: SQL Query - "in" clause vs "="