Is it better to join two fields together, or to compare them each to the same constant?

Question

For example which is better:

select * from t1, t2 where t1.country='US' and t2.country=t1.country and t1.id=t2.id
or
select * from t1, t2 where t1.country'US' and t2.country='US' and t1.id=t2.id

better as in less work for the database, faster results.

---

Sybase, and there's an index on both tables of country+id

Answer 1

I'd lean towards only including your constant in the code once. There might be a performance advantage one way or the other, but it's probably so small the maintenance advantage of only one parameter trumps it.

Answer 2

The correct answer probably depends on your SQL engine. For MS SQL Server, the first approach is clearly the better because the statistical optimizer is given an additional clue which may help it find a better (more optimal) resolution path.

Answer 3

I think it depends on the library and database engine. Each one will execute the SQL differently, and there's no telling which one will be optimized.

Answer 4

If you ever wish to make the query more general, perhaps substituting a parameter for the target country, then I'd go with your first example, as it requires only a single change. That's less to worry about getting wrong in the future.

Answer 5

I suspect this is going to depend on the tables, the data and the meta-data. I expect I could work up examples that would show results both ways - benchmark!

Answer 6

I don't think there is a global answer to your question. It depends on the specific query. You would have to compare the execution plans for the two queries to see if there are significant differences.

I personally prefer the first form:

select * from t1, t2 where t1.country='US' and t2.country=t1.country and t1.id=t2.id

because if I want to change the literal there is only one change needed.

Answer 7

The extressions should be equivalent with any decent optimizer, but it depends on which database you're using and what indexes are defined on your table.

I would suggest using the EXPLAIN feature to figure out which of expressions is the most optimal.

Answer 8

There are a lot of factors at play here that you've left out. What kind of database is it? Are those tables indexed? How are they indexed? How large are those tables?

(Premature optimization is the root of all evil!)

It could be that if "t1.id" and "t2.id" are indexed, the database engine joins them together based on those fields, and then uses the rest of the WHERE clause to filter out rows.

They could be indexed but incredibly small tables, and both fit in a page of memory. In which case the database engine might just do a full scan of both rather than bother loading up the index.

You just don't know, really, until you try.

Answer 9

I think a better SQL would be:

select * from t1, t2 where t1.id=t2.id and t1.country ='US'

There's no need to use the second comparison to 'US' unless it's possisble that the country in t2 could be different than t1 for the same id.

Answer 10

Rather than use an implicit inner join, I would explicitly join the tables.

Since you want both the id fields and country fields to be the same, and you mentioned that both are indexed (I'm presuming in the same index), I would include both columns in the join so you can make use of an index seek instead of a scan. Finally, add your where clause.

SELECT *
  FROM t1
  JOIN t2 ON t1.id = t2.id AND t1.country = t2.country
 WHERE t1.country = 'US'

Answer 11

I had a situation similar to this and this was the solution I resorted to:

Select * FROM t1 INNER JOIN t2 ON t1.id = t2.id AND t1.country = t2.country AND t1.country = 'US'

I noticed that my query ran faster in this scenario. I made the assumption that joining on the constant saved the engine time because the WHERE clause will execute at the end. Joining and then filtering by 'US' means you still pulled all the other countries from your table and then had to filter out the ones you wanted. This method pulls less records in the end, because it will only find US records.