• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Query - "in" clause vs "="

 
SAM KUMAR
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34229
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Stefan Wagner
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess it's rdbms-dependent.

And you may replace:



It should be easy to write a performance-test for your rdbms.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34229
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The examples that Stefan posted also perform the same in DB2 and Oracle.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic