Are these two SQL statements equivalent?

Question

They return the same count of rows, but I'm not sure if one is an accident waiting to happen, or one is simply "the preferred method":

SELECT duckbill.id, duckbill.pack_size, duckbill.description, duckbill.platypus_id, duckbill.department, duckbill.subdepartment, duckbill.unit_cost, duckbill.unit_list, duckbill.open_qty, duckbill.UPC_code, duckbill.UPC_pack_size, duckbill.crv_id, duckbill_platypuss.platypus_item 
FROM duckbill 
INNER JOIN duckbill_platypuss ON duckbill.platypus_id = duckbill_platypuss.platypus_id

SELECT duckbill.id, duckbill.pack_size, duckbill.description, duckbill.platypus_id, duckbill.department, duckbill.subdepartment, duckbill.unit_cost, duckbill.unit_list, duckbill.open_qty, duckbill.UPC_code, duckbill.UPC_pack_size, duckbill.crv_id, duckbill_platypuss.platypus_item
FROM duckbill, duckbill_platypuss
WHERE (duckbill.platypus_id = duckbill_platypuss.platypus_id)

They should be equivalent, but I'd recommend using ANSI syntax (`INNER JOIN`) for readability. — NullUserException, Mar 08 '13 at 16:35
mysql parser optimizes queries; so it will be same from practical view point. — palerdot, Mar 08 '13 at 16:37
There is a very [similar question and answer](http://stackoverflow.com/questions/5742196/is-cross-join-a-synonym-for-inner-join-without-on-clause) about this that explains it pretty well. — Mark Wilkins, Mar 08 '13 at 16:41
@MarkWilkins This looks like a duplicate of [this question](http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax). Microsoft [recommends using explicit the `INNER JOIN` version](http://msdn.microsoft.com/en-us/library/dd172122.aspx) — NullUserException, Mar 08 '13 at 16:44
Yes, this app is for Exotic Mammals United (emu). See UHF for more info. — B. Clay Shannon-B. Crow Raven, Mar 08 '13 at 17:21
@NullUserException: Make your comment an answer, and I'll mark it as such. — B. Clay Shannon-B. Crow Raven, Mar 08 '13 at 19:22

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

So the reason there's a difference is due to the order of execution. You may not notice the difference on some table combinations, but on very large tables, you generally want to filter with the JOIN first.

This post gives some good information toward that end it looks like: Order Of Execution of the SQL query

edited May 23 '17 at 10:32

Community

1
1

answered Mar 08 '13 at 16:35

Jason Whitish

1,428
1
23
27

2

-1 I find it highly unlikely that the query optimizer would generate a different plan for these statements. – NullUserException Mar 08 '13 at 16:37
@NullUserException That's fair, given the specific query. I suppose I was thinking of it more generally. Although, really, the point you make in your comment above is the more appropriate one; that comma joins are generally a bad habit to get into regardless. – Jason Whitish Mar 08 '13 at 18:21

Are these two SQL statements equivalent?

1 Answers1