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

Question

Hi all,

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

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.

Answer 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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).