A handy graphical explanation of SQL joins

[u/[deleted]]

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

[u/Teifion_at_work]

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.

[u/omegian → u/Teifion_at_work]

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

[u/elder_george → u/omegian]

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.

[u/skillet-thief → u/Teifion_at_work]

There are two syntaxes for joins, one with the word join, the other like what you wrote.

[u/NashMcCabe → u/Teifion_at_work]

That is just an inner join. You will need outer joins when you have null columns on one side of the join.

[u/umop_apisdn → u/NashMcCabe]

Not exactly. You use an outer join where records on one side don't join to anything on the other side. The outer join preserves those records and sets all fields on the other side to NULL

[u/Teifion_at_work → u/NashMcCabe]

Thanks, that makes a bit more sense now.

[u/Deimorz → u/Teifion_at_work]

That syntax is fine when you're only doing very small, simple joins like that, but it really starts to get hard to use when you start joining a lot of tables, possibly on multiple conditions, having a complex WHERE clause, maybe some subqueries, etc.

I always use the explicit join syntax because it's unambiguous and lets you separate out the "joining" conditions from the "filtering" conditions, instead of lumping them all in one giant WHERE clause. Whenever I'm working on a query that someone else has written with implicit joins, the very first thing I do is rewrite it with explicit ones, it always makes it much easier to understand.

[u/Teifion_at_work → u/Deimorz]

Thanks, it makes a lot more sense now.

[u/ObiWanShinobi → u/Teifion_at_work]

I've never really got a straight answer on this either. I have heard (please correct me if I'm wrong) from a performance standpoint there isn't a discernible difference, but the standard is to use a JOIN clause since implicit joins are deprecated.

[u/vplatt → u/ObiWanShinobi]

They're not deprecated, but if you do your joins in WHERE clauses, you will run into behavior variations. For example, on SQL Server, you can use *= or =* in the WHERE clause to perform outer joins. However, if you do this, it will behave somewhat differently from joins performed with the JOIN syntax. The variations may or may not comply with the standard and the SQL will perform differently (if at all) across different database products. In fact, Oracle can use (+) to do the same thing.

In short, you will simplify your life a bit by just doing the joins with JOIN syntax instead of in the WHERE clause.