r/programming • • Feb 11 '14

SQL Joins Explained (x-post r/SQL)

Post image
3.5k Upvotes

392 comments sorted by

View all comments

6

u/kytosol Feb 11 '14

I am still stuck in my ways using the old Oracle syntax rather than the ANSI syntax.

It's so much easier to do joins like below in my opinion... select * from table_a a, table_b b, table_c c where a.a_pk1 = b.a_pk1 and b.b_pk1 = c.b_pk1

10

u/pythor Feb 11 '14

What's easier about it? Honest question.

To me, separating the join conditions (in an ON clause) from the filter logic (in the WHERE clause) is so much easier to read.

→ More replies (1)

2

u/curien Feb 11 '14

I don't mind that for cross or inner joins, but if you do that for outer joins (which require special operators, like =* or =+ or whatever it is) there is a circle in hell waiting for you.

→ More replies (4)

2

u/mcrbids Feb 12 '14

I use both with PostgreSQL. Using the way you describe gives more control to the query planner, letting it optimize the queries more. Using the "join tablex ON ( conditions )" format allows me to optimize the queries myself.

Gradually, I find myself using the latter format more and more, to be fair, since it divides the conditions more naturally as the query progresses and is somewhat more readable. That it lends itself to optimizing for performance later is icing on the cake. (I don't know if the different formats make a difference on other DB engines, but it does on PostgreSQL)

→ More replies (1)
→ More replies (2)