Different between mySQL queries INNER JOIN

Question

I have two queries:

$query = mysql_query("SELECT ord.orderID, customers.CustomerName,       
                      FROM ord, customers
                      WHERE customers.CustomerSalary=ord.orderID");

and

$query = mysql_query("SELECT ord.orderID, customers.CustomerName, 
                      FROM ord
                      INNER JOIN customers
                      ON customers.CustomerSalary=ord.orderID");

This queries return the same result. What is different between them.

There is no difference, the first one is old school style and called implicit join and the 2nd one is more readable and widely used called explicit join. — Abhik Chakraborty, Apr 01 '15 at 08:29

score 0 · Answer 1 · edited Apr 01 '15 at 09:24

Both are same. The first query is basically inner join on the back-end but the way you have written second query, you can use left join, right join etc with that query. Both will perform Cartesian Product on the back-end and filter out the results based on the check.

You will need to use the second query format in order to use left join, right join etc.

Another approach commonly used is known as nested queries. They are pretty faster than joins since there is no Cartesian product on the back-end.

score 0 · Accepted Answer · answered Apr 01 '15 at 08:37

$query = mysql_query("SELECT ord.orderID, customers.CustomerName, 
                      FROM ord
                      INNER JOIN customers
                      ON customers.CustomerSalary=ord.orderID");

This query is optimised than the other one as Inner join get applied first and then where clause will be applied.

Other benifit I see that is you can change Inner Outer join.

Clean code.

Different between mySQL queries INNER JOIN

2 Answers2