Search code examples
sqlperformancejoinsyntactic-sugarequivalent

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

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?

Solution

  • 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.