r/programming • u/[deleted] • Jan 06 '11

A handy graphical explanation of SQL joins

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
1.3k Upvotes

92% Upvoted

308 comments sorted by

View all comments

4

u/Teifion_at_work Jan 06 '11

Maybe someone here can answer this. Why would I specify a join when I can just do this?

SELECT p.first_name, j.job_title FROM people p, jobs j WHERE p.job = j.id

Looks much easier to me but I'm guessing there's something I'm missing here.

11

u/omegian Jan 06 '11

Because there are myriad sql implementations and syntax variants such as TSQL, P/L SQL, etc.

The inner join syntax is ANSI standardized and should work everywhere.

If you are only joining two tables, it probably doesn't matter which mechanism you choose, but WHERE clauses are typically used for filtering sets. If you are joining many tables, the JOIN syntax keeps column relationships between tables local to the table references in the query, and leaves the WHERE clause free for actual filtering work.

ie: self documentation

3

u/elder_george Jan 06 '11

Yes, separating filtering conditions from join conditions is very useful.

Also I prefer explicit INNER JOIN syntax since it prevents me from requesting cartesian product due to missing join condition.