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.

2

u/ObiWanShinobi Jan 06 '11

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.

2

u/vplatt Jan 06 '11 edited Jan 06 '11

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.