What is more efficient, an in clause or an or clause, and does the index still get used?

Question

Suppose that I have a customers table which has an account_number and a billing_account_number, if I link to another table which I need to test for either, I could use the following or clause:

select c.name
  from customers c
 ,left join credit_terms ct on account = c.account_number
                            or account = c.billing_account

However, I have found that the following works equally

select c.name
  from customers c
 ,left join credit_terms ct on account in (c.account_number, c.billing_account)

Now suppose that credit_terms.account is indexed, would that index get used in both cases? Are both statements just as equal? Is there any cost associated with one or the other?

I do apologise for being naive though I am fairly new to moderate levels of SQL.

I've just identified this could be a duplicate of http://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause, though I'm interested to know if the values are not constant values. — Brett Ryan, Sep 17 '12 at 14:50
@ypercube, no not necessarily. This was just a very basic query that I was using to convey a simple case that I have come up a lot. Usually there is a lot more fields and a lot of joins, though the account and billing account are often used in this way. — Brett Ryan, Sep 17 '12 at 15:32

score 1 · Accepted Answer · answered Oct 01 '12 at 12:34

OpenEdge uses a cost based optimizer so the particular query plan will be influenced by the statistics relevant to the query -- it may, or may not, use the indexes that you expect depending on what the optimizer knows about the data.

This knowledgebase article explains OpenEdge's SQL query plan:

http://progresscustomersupport-survey.force.com/ProgressKB/articles/Article/P62658?retURL=%2Fapex%2FProgressKBHome&popup=false

You must also periodically update SQL statistics for the optimizer to do a good job:

http://progresscustomersupport-survey.force.com/ProgressKB/articles/Article/20992?retURL=%2Fapex%2Fprogresskbsearch&popup=false

score 0 · Answer 2 · answered Sep 17 '12 at 14:52

I have no knowledge of openedge, but I do not think either one will use indexes on account. The 2 of them are basically the same.

In case you want to use the index I would do something like this:

select c.name
  from customers c
  left join credit_terms ct on ct.account = c.account_number
union
select c.name
  from customers c
  left join credit_terms ct on ct.account = c.billing_account

Then again, this is somehow speculating, as I say again I have no knowledge of openedge.

What is more efficient, an in clause or an or clause, and does the index still get used?

2 Answers2