Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 10-10-2009
Member
 
Join Date: Mar 2009
Posts: 1,360
Difference between 2 SQL queries

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.
Reply With Quote
  #2  
Old 10-10-2009
Member
 
Join Date: Nov 2008
Posts: 996
Re: Difference between 2 SQL queries

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!
Reply With Quote
  #3  
Old 10-10-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: Difference between 2 SQL queries

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.
Reply With Quote
  #4  
Old 10-10-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: Difference between 2 SQL queries

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.
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Difference between 2 SQL queries"
Thread Thread Starter Forum Replies Last Post
Queries regarding Sandboxie Talitha Technology & Internet 3 26-12-2010 06:08 AM
Optimizing queries in MySQL Caden Fernandes Software Development 4 05-03-2010 07:14 PM
Access queries on AS400 Ujagar Software Development 5 13-02-2010 09:23 AM
How to Run MySQL Queries with PHP? Rob Dizzle Software Development 4 05-02-2010 05:40 AM
Queries About Motherboard Aakarshan.d Motherboard Processor & RAM 4 04-03-2009 08:37 AM


All times are GMT +5.5. The time now is 04:57 AM.