Which of these select statements is "better," and why?

Question

I have 2 table person and role. I have to all the persons based on role.

select person.* from person inner join role on
person.roleid = role.id Where role.id = @Roleid

or

select person.* from person inner join role on
person.roleid = role.id AND role.id = @Roleid

Which one of the above two solutions is better and Why?

Answer 1

The first is better because it's logically coherent. The scoping condition isn't relevant to the join, so making it a part of the join is a kludge, and in this case an unhelpful one.

Answer 2

There is no difference in the relational algebra. Criteria from the where and inner joins like this are interchangeable. I use both depending on the readability and situation.

In this particular case, you could also use:

select person.* from person WHERE person.roleid = @Roleid

The only difference being that it does not require that a row exist in the role table (but I assume you have referential integrity for that) and it will not return multiple rows if roleid is not unique (which it almost certainly is in most scenarios I could foresee).

Answer 3

Your best bet is to try these queries out and run them through MS Sql's execution plan. I did this and the results look like this:

Execution plan showing identical performance of queries http://img223.imageshack.us/img223/6491/querycompare.png

As you can see, the performance is the same (granted, running it on your db may produce different results.) So, the best query is the one that follows the consistent convention you use for writing queries.

Answer 4

SQL Server should evaluate those queries identically. Personally, I would use the AND. I like to keep all of the criteria for a joined table in the join itself so that it's all together and easy to find.