Need help with SQL joins


I have the following SQL query.

SELECT c.course_id, c.cat_id, cc.cat_name, c.created_date, c.title, c.description, c.notify, c.copyright, c.icon, c.release_date, c.primary_language,
c.end_date, c.banner FROM %scourses c, %scourse_cats cc, %scourse_enrollment ce WHERE c.cat_id = cc.cat_id AND ce.course_id = c.course_id;

How do I re-factor the same using JOIN?


SELECT c.course_id
     , c.cat_id
     , cc.cat_name
     , c.created_date
     , c.title
     , c.description
     , c.notify
     , c.copyright
     , c.icon
     , c.release_date
     , c.primary_language
     , c.end_date
     , c.banner 
  FROM %scourses c
  JOIN %scourse_cats cc
    ON cc.cat_id = c.cat_id
  JOIN %scourse_enrollment ce 
    ON ce.course_id = c.course_id


Hi Rudy,

Could you tell me which query is more efficient?

Also, what is the difference between inner join, right and left join (in this context)?


both inner joins (comma list style or JOIN syntax style) are equally efficient

as for the difference between inner, left, and right, please use google -- those joins have fundamentally different semantics


So, the query that I provided and the one that you gave are essentially the same, just with different syntax?


yes, except the JOIN syntax is ~way~ better



Thanks. I see the bigger picture now.

P.S. I just got your book. Seems I gotta learn some stuff smile


But isn't ON = more efficient than the WHERE = because of the order it's evaluated?

I might be wrong, I'm just wondering for my own knowledge. smile I thought I remembered reading this somewhere once.


no, i don't think so

use EXPLAIN on your real world examples (using both styles of join) to confirm