nested queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

nested queries

Post by bimo »

Is it really bad to use nested queries in an sql statement?
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

What do you mean nested queries? Subqueries? or looping?
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

Thanks
Post Reply