Page 1 of 1

nested queries

Posted: Tue Mar 28, 2006 8:18 am
by bimo
Is it really bad to use nested queries in an sql statement?

Posted: Tue Mar 28, 2006 8:41 am
by kendall
Bimo,

uhm...I dont think so...i think the reason why they made it nested queries is because it would be more effecient in terms of processing resources?

correct me if im wrong though

Kendall

Posted: Tue Mar 28, 2006 8:53 am
by John Cartwright
What do you mean nested queries? Subqueries? or looping?

Posted: Tue Mar 28, 2006 7:09 pm
by bimo
I mean like this,

Code: Select all

select  
distinct v.`video_id`,  
v.`video_thumb`,  
p.`name`,  
p.`orig_from_loc_id` as `home`,  
v.`location_id`,  
(select distinct cit.`name` from cities`cit` where cit.`city_id` = l.`city_id` and l.`location_id` = v.`location_id`) as `city`,  
(select cou.`name` from countries`cou` where cou.`country_id` = l.`country_id` and l.`location_id` = v.`location_id`) as `country`,  
v.`date_rec` from videos`v`,  
people`p`, cities`cit`,  
countries`cou`,  
locations`l`  
where  
p.`person_id` = v.`person_id`  
AND p.`name` like '%james%'  
AND (select cit.`city_id` from cities`cit` where cit.`name` = "new orleans") = (select l.`city_id` from locations`l` where v.`location_id` = l.`location_id`)  
AND p.`build` = 3  
AND p.`skin_color_id` = 2  
AND p.`hair_length_id` = 2  
AND p.`hair_color_id` = 5  
order by v.`date_rec`
, with a query in the select and in the where clause. I just felt like I had heard somewhere (a long time ago) that nested queries weren't great...

So, I came to see if there were a better way to do the above query...

does anyone have any ideas?

Posted: Wed Mar 29, 2006 2:24 am
by RobertGonzalez
These types of queries are called sub-queries and from what I have heard, they are an invaluable tool for DB admins. Most other DB servers have utilized this functionality but MySQL did not implement it until 4.1 or 4.2. There have been a number of times when, had I the option, I would have used a subquery instead of a longer, code oriented approach.

One thing I have heard though is to make sure your tables are optiized and properly indexed to speed up the query process. I have heard, under heavy server load and on large tables, that subqueries can appear to be slower than regular single level queries.

Posted: Wed Mar 29, 2006 6:39 am
by bimo
Thanks