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

As you are not fetching columns from role you'd better not include it in the FROM clause at all. Use this:

SELECT *
FROM   person
WHERE  person.roleid IN (SELECT id FROM role WHERE id = @Roleid)

This way the optimizer sees only one table in the FROM clause and can quickly figure out the cardinality of the resultset (that is the number of rows in the resultset is <= the number of rows in table person).

When you throw two tables with a JOIN the optimizer has to look in the ON clause to figure out if these tables are equi-joined and whether unique indexes exist on the joined columns. If the predicate in the ON clause is complicated one (multiple ANDs and ORs) or simply wrong (sometimes very wrong) the optimizer might choose sub-optimal join strategy.

Obviously this particular sample is very contrived, because you can filter persons by roleid = @Roleid directly (no join or sub-query) but the considerations above are valid if you had to filter on other columns in role (@Rolename for instance).

Answer 2

I'd go with the first one, but remember when done testing the code you should explicitly select each table, instead of doing select *

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

Would there be any performance hit/gain by using this query?

SELECT person.* FROM person,role WHERE person.roleid=role.id AND role.id=@RoleID

Answer 5

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 6

I prefer #1, I believe that it expresses the intent of the statement better. That you are joining the two tables based on the roleid & role.id and that you are filtering based on @Roleid

SELECT person.* 
FROM person INNER JOIN role ON person.roleid = role.id 
Where role.id = @Roleid

Answer 7

Both queries are identical. During query processing, SQL server applies the WHERE filter immediately after applying the join condition filter, so you'll wind up with the same things filtered either way.

Answer 8

Sqlserver probably has an equivalent of the "explain plan" statement that Oracle, PostgreSQL and MySQL all support in one form or another. It can be very useful in telling you how the query parser and optimizer is going to treat your query.

Answer 9

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 10

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.