Communities for your favorite technologies.  Explore all Collectives

INNER JOIN ON vs WHERE clause

submited by
Style Pass
2024-10-30 08:30:03

Communities for your favorite technologies. Explore all Collectives

Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

Using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.

Others have pointed out that INNER JOIN helps human readability, and that's a top priority, I agree. Let me try to explain why the join syntax is more readable.

The SELECT clause tells us what we're getting back; the FROM clause tells us where we're getting it from, and the WHERE clause tells us which ones we're getting.

Any query elements that control the tables - where we're getting stuff from - semantically belong to the FROM clause (and of course, that's where JOIN elements go). Putting joining-elements into the WHERE clause conflates the which and the where-from, that's why the JOIN syntax is preferred.

Leave a Comment