Is FROM x JOIN y ON x.a = y.b equivalent to FROM x,y WHERE x.a=y.b?

Question

I noticed on some test data that queries of the form

SELECT *
FROM a
JOIN b ON a.x = b.y
WHERE [more conditions...];

were returning the same data as queries of the form

SELECT *
FROM a,b
WHERE a.x = b.y
AND [more conditions...];

Intuitively they seem equivalent, but I'm not confident that this is the case.

I prefer the query that uses JOIN as it separates the general structure of the query from the specific business logic of the query. i.e. The conditions in the WHERE section of the query using JOIN are ones that could be parameterized.

In which other ways, if any, are these queries not identical?

this may help you http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Pரதீப், Oct 20 '14 at 15:12
Yep, in most RDBMSs they will not only return the same records, but will have the exact same execution path as well. — JNevill, Oct 20 '14 at 15:13
@PatrickHofman I was playing around with an ISAM table in MySQL but I'm interested in answers across all applicable platforms and engines. — vowel-house-might, Oct 20 '14 at 15:16
@Pradeep thanks, I tried to find similar questions but didn't know to use terms such as "explicit/implicit joins". — vowel-house-might, Oct 20 '14 at 15:17
@AndrewMartin FYI you search lyk cartesian product vs inner join this too — Pரதீப், Oct 20 '14 at 15:19

score 1 · Answer 1 · answered Oct 20 '14 at 15:16

These queries are logically identical. The logical differences between the new JOIN form (SQL-92) and the older , form are in how the outer join expressions work.

Where they are not identical is in code quality. The SQL-89 form was superceeded by the SQL-92 over 20 years ago, and the newer form is much preferred for its clarity, better standards adoption for outer joins and greater expressive power for outer joins.

score 1 · Answer 2 · answered Oct 20 '14 at 15:17

The general answer is yes.

However, you have to consider that with the complexity of your SQL statements, the complexity to parse them and determine their execution plan in the database will increase, making it more likely that ANSI style joins and regular joins don't have the same execution plan, performance and output.

This is certainly the case in Oracle, which has some serious bugs still in their ANSI join parsing (have a bugs few pending with Oracle support).

score 1 · Accepted Answer · answered Oct 20 '14 at 15:22

Both forms are equivalent but another reason why

SELECT *
FROM a
JOIN b ON a.x = b.y
WHERE [more conditions...];

is often preferred is that it offers more flexibility if you need to filter things a bit differently. Imagine for instance that x may be null. You have just to change the type of join to left join.

score 0 · Answer 4 · answered Oct 20 '14 at 15:16

0

Yes, those two ways function in similar manner, according to the book: Fundamentals of database systems / Ramez Elmasri, Shamkant B. Navathe. Online book link

answered Oct 20 '14 at 15:16

jyrkim

2,849
1
24
33

Is FROM x JOIN y ON x.a = y.b equivalent to FROM x,y WHERE x.a=y.b?

4 Answers4