Need help with SQL joins

Databases
1

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?

2 
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
INNER
  JOIN %scourse_cats cc
    ON cc.cat_id = c.cat_id
INNER
  JOIN %scourse_enrollment ce 
    ON ce.course_id = c.course_id
3

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)?

4

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

5

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

6

yes, except the JOIN syntax is ~way~ better

:slight_smile:

7

Thanks. I see the bigger picture now.

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

8

But isn’t ON table1.pk = table2.fk more efficient than the WHERE table1.pk = table2.fk because of the order it’s evaluated?

I might be wrong, I’m just wondering for my own knowledge. :slight_smile: I thought I remembered reading this somewhere once.

9

no, i don’t think so

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

:slight_smile: