SQL Optimization Help

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

I have the following SQL:
SQL Code: [ Select ]
"SELECT group_name
FROM " . GROUP_TABLE . "
WHERE `group_id` = (SELECT user_group_id
                   FROM " . USER_TABLE . "
                   WHERE `user_id` = '{$user_id}')"
  1. "SELECT group_name
  2. FROM " . GROUP_TABLE . "
  3. WHERE `group_id` = (SELECT user_group_id
  4.                    FROM " . USER_TABLE . "
  5.                    WHERE `user_id` = '{$user_id}')"

Is it more efficient if I do left join instead of joining two queries like that? It doesn't do anything intensive, but it would be good for me to know later on if I do get to something more intensive?

SQL Code: [ Select ]
"SELECT `group_name`
FROM `" . GROUP_TABLE . "`
LEFT JOIN `" . USER_TABLE . "`
ON `" . GROUP_TABLE . "`.`group_id` = `" . USER_TABLE . "`.`user_group_id`
WHERE `user_id` = '{$user_id}'"
  1. "SELECT `group_name`
  2. FROM `" . GROUP_TABLE . "`
  3. LEFT JOIN `" . USER_TABLE . "`
  4. ON `" . GROUP_TABLE . "`.`group_id` = `" . USER_TABLE . "`.`user_group_id`
  5. WHERE `user_id` = '{$user_id}'"
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Poly
  • Guru
  • Guru
  • User avatar
  • Posts: 1091
  • Loc: Same place you left me.

Post 3+ Months Ago

So not knowing exactly what it is you're attempting to accomplish here:

Between the two options you have posted as long as you're on a modern RDBMS it will interpret both in the same way meaning there should be no statistical difference on speed.

That being said, depending on what data you are trying to pull, it is faster to use an INNER JOIN than an OUTTER JOIN(LEFT JOIN).

An INNER JOIN of A and B gives the result of A intersect B(think inner part of a venn diagram).

An OUTER JOIN of A and B gives the result of A union B(think outer part of a venn diagram).

INNER JOIN gives the intersection of the two tables(the two rows in common):
Code: [ Select ]
SELECT * FROM a INNER JOIN b ON a.a = b.b;
SELECT a.*,b.* FROM a,b WHERE a.a = b.b;

a | b
--+--
3 | 3
4 | 4
  1. SELECT * FROM a INNER JOIN b ON a.a = b.b;
  2. SELECT a.*,b.* FROM a,b WHERE a.a = b.b;
  3. a | b
  4. --+--
  5. 3 | 3
  6. 4 | 4



LEFT OUTER JOIN will return ALL rows in A plus rows common to B.
Code: [ Select ]
SELECT * FROM a LEFT OUTER JOIN b ON a.a = b.b;
SELECT a.*,b.* FROM a,b WHERE a.a = b.b(+);

a | b 
--+-----
1 | null
2 | null
3 |  3
4 |  4
  1. SELECT * FROM a LEFT OUTER JOIN b ON a.a = b.b;
  2. SELECT a.*,b.* FROM a,b WHERE a.a = b.b(+);
  3. a | b 
  4. --+-----
  5. 1 | null
  6. 2 | null
  7. 3 |  3
  8. 4 |  4



As one other note, a FULL OUTER JOIN will return ALL rows from both tables. Rows not common will be NULL for the corresponding table.


Long story short, if you are using table A to locate records in table B, use an INNER JOIN it is far more efficient. This is the vast majority of usage.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

Thanks, that stuff you posted about INNER JOIN and other kinds of joins did help me. I changed my code to INNER JOIN. Thanks for posting such a detailed post.
  • Poly
  • Guru
  • Guru
  • User avatar
  • Posts: 1091
  • Loc: Same place you left me.

Post 3+ Months Ago

Not a problem, glad it helped!

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: Liamw411 and 43 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.