Difference between 2 SQL queries

**GeforceUser** · 10-10-2009

I have started learning SQL recently and I want to know about the difference between the 2 queries. Suppose I have a table "comments" which contains the fields: id, name, user_id and a table "users" which contains: id, name, rank

Code:

SELECT "comments" .* 
FROM "comments" INNER JOIN "users" ON "users".id = "comments".user_id
WHERE "users".rank = 'admin'

Code:

SELECT "comments" .* 
FROM "comments" 
WHERE "comments".user_id IN (SELECT id
FROM users 
WHERE "users".rank = 'admin');

In fact, I wonder if the making of a join does not load unnecessary fields but I also wonder if the making of a nested query is not a waste of time.

**CodGuru** · 10-10-2009

This is strictly equivalent I suppose. After all the second solution is very rough and needs to make 2 queries in reality. It is also a matter of standard. When you generates queries a little more complex, you will use the joints because it is cleaner, clearer, more editable, etc. Compare the two methods if you have a dozen tables to join. In the first method, you have 10 lines of INNER JOIN. In the second, nested queries 10. Disgusting!

**Zecho** · 10-10-2009

This is not a question of readability and "clean". In this case, it is mainly a question of performance.

In the case of the second query: the DBMS will first run the subquery. Then the first one. Then, for each record in your table "comments", it will test whether the user.id is in the game to register the user table.

While with the first query, the DBMS contains indexes that enable it to quickly find the information you requested, in one motion without further testing to make the user.rank = "admin". It will be much faster, and it will even allow the DBMS to keep statistics that will be used later to better optimize their time working for equivalent queries.

**opaper** · 10-10-2009

The execution plan is identical with the two requests from Sybase 15 point of view. Tested at the moment, on the other side I do not know if it's not optimized at the time of execution. And I'm sure with a bit more complexity, you can do everything in a much better way.