Subquery  VS  join SQL

Konstantin Krolikov
Konstantin Krolikov used Ask the Experts™
on
hello experts, i just want to know what is the best way to preform query with multiple table (2-3)
also, i read alot over the internet about the subject and some say subquery, some say join, so i want to know once and for all, witch case join is faster and witch case subquery is faseter. or there is only one that is the fastest..

i only care about the performance (cache, memory...), i don't care about how hard it is to read or if there is any logical in this, only preformance

basically i want to know in witch case who's better and if it has somthing with the database size, number of rows, engine. -> everything.. :)

i use MySQL database..

by the way, i use in my varchar variables in UTF-8 encoding and not ascii most of the time..

thanks agin :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale BurrellDirector

Commented:
There is no way to predict which is faster as it depends on the tables and indexs of your database.

Personally if I want a single field from a second table I use a sub-query and if I want more than 1 field from the table I use a join.

You would probably only notice the difference performance wise if querying a large number of records.

Author

Commented:
ok, but take a look at this example:
SELECT worlds.player_id, players.money FROM players, worlds WHERE players.id = worlds.id;

or i should use do this query with join.

Assuming that query return more then 2 rows..
nishant joshiTechnology Development Consultant

Commented:
SELECT player_id, players.money FROM players p
 WHERE Exists(select worlds from worlds where id=p.player_id)

Open in new window



check this will be fast than any other query for few rows.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Dale BurrellDirector

Commented:
That query by itself doesn't appear to need the table Worlds - I assume you would actually use it by selecting a World you want to get a list of Players for? And in most cases your Worlds list would return a World ID which you could query directly. I also assume world ID and player ID are not equal, but then I don't know what you are trying to do.

Author

Commented:
yea, you right, but let say that i have a big table more then 100k rows, and the condition require subquery or join, what should i use.
and if there were 10k rows..

by the way, i have that query
UPDATE `players` SET `money` = `money` + (SELECT `price` FROM `worlds` WHERE
 `ID` IN(SELECT `ID` FROM `players_world` WHERE `started_ended` = b'00' AND 
 `last_update` < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 SECOND)) > 0) 
WHERE `ID` IN 
(SELECT `player` FROM `players_world` WHERE `started_ended` = b'00' AND 
`last_update` < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 SECOND));

Open in new window

and after the update query i delete the rows with another query...
DELETE FROM `players_world` WHERE `ID` IN (SELECT `ID` FROM `worlds`)
 AND `last_update` < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 SECOND) AND 
`started_ended` = b'00';

Open in new window


do you think that i should use join, this is not that big, however it does get bigger with time.. it could be large table..
suggestions?
Director
Commented:
Well I would use a join for the update because you need the same information twice - I suspect it will make very little difference performance wise, but I don't believe in repeating code unless I have to.

Author

Commented:
yeah you most likely right.
one last question:
is this considered to join?
update i
set i.feed_id = c.feed_id
from invc_item i, calls c
where i.feed_id = 0
and c.invc_item_id = i.invc_item_id
and c.feed_id > 0

Open in new window

i saw it in this Link
is it really work as  join??
is it how i do join in update/delete query, because i only know on select queries..

thank you alot :) you really helped me!

Commented:
Yup it is a join (no points please). Here you are first cross joining both the query then putting where condition for two fields to be same from both the tables. This in turn changes the query to inner join.

Author

Commented:
no points??

Commented:
I mean its just a clarification. While accepting solution, please don't give me any points. I think other experts have earned the full points . So.

Author

Commented:
i think you helped me and you should get that points, you are the only one that halped me throw this so i think you should get the points, but i'll wait another day to hear other experts..
Dale BurrellDirector

Commented:
While I fully think TempDBA is deserving of points his answer here is not to the original question - points should be awarded to answers of your original question not additional questions.

No only is your join a cross join that becomes and inner join, but its typically faster than an inner join as well! Bonus!

I might be completely wrong here as my experience is in SQL Server, so in SQL Server with a joined update you cannot update using the alias, although you can use the alias in the rest of the query, so the update would be something like

update invc_item set
  feed_id = c.feed_id
from invc_item i, calls c
where i.feed_id = 0
and c.invc_item_id = i.invc_item_id
and c.feed_id > 0

Open in new window


And again in SQL Server you cannot join on the delete statement so you would need the sub-query.

Double check those last 2 statements as it may be different for MySql

Author

Commented:
ohhh, sorry i jsut now look and saw that different use wrote that answer, i was sure it was you (dale_burrell).. sorry.. :)

to my original question: so basically join is better if i have a lot or rows with conditional that require 2 or more tables.
and if i need the same information in 2 or more different tables.. right?

thanks alot
Dale BurrellDirector

Commented:
Something like that :) there aren't many absolutes when it comes to SQL - there is always more than one way to solve a problem, and there are always times when the obvious solution doesn't work. You can't expect to learn it all overnight, experience plays a bit part. For now if it works fast and does what you want then you you've probably done it right.

Author

Commented:
thanks a lot :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial