List:General Discussion« Previous MessageNext Message »
From:Morten Primdahl Date:August 6 2008 12:18pm
Subject:Picking the better query (join vs subselect)
View as plain text  
Hi guys,

I have 2 tables "cars" and "parts" where car has many parts. I need a  
query to return some fields from the cars table as well as a field  
from multiple parts records. I've come to the following approaches,  
and would like to understand which is the better, and why, or if  
there's a 3rd and even better approach:

Approach 1 - subselects:

SELECT cars.id,
        (SELECT parts.value FROM parts WHERE car_id = cars.id AND id =  
3) AS part_3,
        (SELECT parts.value FROM parts WHERE car_id = cars.id AND id =  
4) AS part_4
FROM cars
WHERE id = 2;

Approach 2 - joins:

SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4
FROM cars
INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id
INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id
WHERE parts_3.id = 3
AND   parts_4.id = 4
AND   cars.id = 2;

I've tried to find out if joins are preferred over subselects, but am  
not able to come to a definite conclusion. I read that correlated  
subselects are bad, and I should go for the join, but I know the id of  
the record in the outer query and can hard code that into the  
subselect (if that makes a difference).

The EXPLAIN result only differs in that the select_type is SIMPLE in  
the JOIN approach, but PRIMARY/DEPENDENT SUBQUERY in the subselect  
approach.

Any tips much appreciated, the full example below.

Br,

Morten


CREATE TABLE cars (
   id integer,
   make varchar(32)
);

CREATE TABLE parts (
   id integer,
   car_id integer,
   value varchar(64)
);

INSERT INTO cars (id, make) VALUES (1, 'Ford');
INSERT INTO cars (id, make) VALUES (2, 'Honda');

INSERT INTO parts (id, car_id, value) VALUES (1, 1, 'Wheel');
INSERT INTO parts (id, car_id, value) VALUES (2, 1, 'Tire');
INSERT INTO parts (id, car_id, value) VALUES (3, 2, 'Wheel');
INSERT INTO parts (id, car_id, value) VALUES (4, 2, 'Tire');
INSERT INTO parts (id, car_id, value) VALUES (5, 2, 'Dice');

SELECT cars.id,
        (SELECT parts.value FROM parts WHERE car_id = 2 AND id = 3) AS  
part_3,
        (SELECT parts.value FROM parts WHERE car_id = 2 AND id = 4) AS  
part_4
FROM cars
WHERE id = 2;

SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4
FROM cars
INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id
INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id
WHERE parts_3.id = 3
AND   parts_4.id = 4
AND   cars.id = 2;

Thread
Picking the better query (join vs subselect)Morten Primdahl6 Aug
  • Re: Picking the better query (join vs subselect)Rob Wultsch6 Aug
    • Re: Picking the better query (join vs subselect)Waynn Lue7 Aug
      • Re: Picking the better query (join vs subselect)walter harms8 Aug