Difference between two sql queries (using join and without join)

Question

I have two set of sql queries. Both produces the desired result for me but I am not sure which one is efficient and how. Please anybody can explain it to me?

Query 1:

SELECT 
od.id, od.order_id, c.firstname, od.category, od.quantity, 
od.price, o.order_date, u.username 
FROM 
orders o inner join order_detail od on o.id=od.order_id 
join customer c on c.customerid = o.customer_id 
join users u on u.userid = o.issued_by;

Query 2:

 SELECT 
 od.id, od.order_id, c.firstname, od.category, od.quantity,
 od.price, o.order_date, u.username 
 FROM
 order_detail od, customer c, orders o,
 users u WHERE o.id = od.order_id 
 AND o.customer_id = c.customerid 
 AND u.userid = o.issued_by;

score 2 · Accepted Answer · answered Sep 02 '14 at 05:35

The first query is the better one with the appropriate syntax. The second query is an "old school" version and should not be used.

In term of performance there is an important difference between both queries, the first one will filter the result directly during the jointure clauses. The second query will get the entire data before applying the WHERE clause on them.

Don't hesitate, choose the first version without any doubt.

Hope that this will help you.

Difference between two sql queries (using join and without join)

1 Answers1