MySQL JOIN vs MySQL Shorthand JOIN(,)

Question

Method 1

SELECT o.*, 
       bc.*, 
       s.name  AS plan_name, 
       p.image AS course_image, 
       p.id    AS course_id, 
       p.name  AS course_name, 
       p.id, 
       p.level 
FROM   wg_guru_order o 
       JOIN wg_guru_buy_courses bc 
         ON o.id = bc.order_id 
       JOIN wg_guru_subplan s 
         ON bc.plan_id = s.id 
       JOIN wg_guru_program p 
         ON bc.course_id = p.id 
WHERE  o.status = 'Paid' 
       AND bc.userid = 451 
        OR p.catid = 26 
           AND p.published = 1

EXPLAIN Command output

Method 2

 SELECT o.*,
       bc.*,
       s.name  AS plan_name,
       p.image AS course_image,
       p.id    AS course_id,
       p.name  AS course_name,
       p.id,
       p.level
FROM   wg_guru_order o,
       wg_guru_buy_courses bc,
       wg_guru_subplan s,
       wg_guru_program p
WHERE  o.status = 'Paid'
       AND o.id = bc.order_id
       AND bc.userid = 451
       AND bc.plan_id = s.id
       AND bc.course_id = p.id
        OR p.catid = 26
           AND p.published = 1 

EXPLAIN Command output

Above two are the same which happens to be working fine for me. Only difference is method 1 query is written using JOIN claues & ON, method 2 is written using JOIN shorthand method.

Problem is method 1 return 3 rows, method 2 return 1543 rows. How can the same query returns different results ?

Given that I just want to understand why is it. I'm not concern about the results set. All I wonder is how can the same query written in two ways outputs different types of result sets?

Answer 1

Your problem isn't the CROSS JOIN vs the INNER JOIN, but rather your WHERE criteria needs parenthesis around the appropriate OR clauses.

The Method 2 should have a WHERE clause like this, to be equivalent to Method 1:

WHERE ( o.id = bc.order_id
    AND bc.plan_id = s.id
    AND bc.course_id = p.id
    )
  AND ( o.status = 'Paid' AND bc.userid = 451
     OR p.catid = 26 AND p.published = 1
      )

Answer 2

Always prefer the first option.

Both options are standard, but the second option is from the older SQL-89 standard. The first option is the new SQL-92 standard. The older standard is deprecated, and some databases are beginning to follow through on that deprecation. For example, Sql Server 2012 dropped support for doing out outer joins via the old syntax. It's still fully supported by MySQL, but you shouldn't really count on it staying that way for the life of your application.

Going beyond this, those are not the same queries. You need to add some parentheses to the WHERE clause to control how your OR condition is interpreted. This is why the queries return different sets of rows. As written, it's as if your where clauses look like this:

Method 1:

WHERE  (o.status = 'Paid' AND bc.userid = 451)
    OR (p.catid = 26 AND p.published = 1)

Method 2:

WHERE 
   (o.status = 'Paid'
      AND o.id = bc.order_id
      AND bc.userid = 451
      AND bc.plan_id = s.id
      AND bc.course_id = p.id
   ) OR ( p.catid = 26 AND p.published = 1)

Note the groupings... that's probably not what you intended. I expect you intended something more like this:

WHERE  o.status = 'Paid'
  AND  o.id = bc.order_id
  AND  bc.course_id = p.id
  AND  bc.plan_id = s.id
  AND (bc.userid = 451 OR p.catid = 26)
  AND  p.published = 1

Right now neither of your queries works this way, but I think it's closer to what you were really trying to do. You need the write the query with the parentheses in right places to get the results you want.