nested queries
Moderator: General Moderators
nested queries
Is it really bad to use nested queries in an sql statement?
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
I mean like this,, 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?
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`So, I came to see if there were a better way to do the above query...
does anyone have any ideas?
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
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.