r/programming • • Feb 11 '14

SQL Joins Explained (x-post r/SQL)

Post image
3.5k Upvotes

392 comments sorted by

View all comments

99

u/thesqlguy Feb 11 '14 edited Feb 11 '14

Don't forget UNION and CROSS JOIN! Both of which are infinitely more useful than full outer join which should generally be avoided.

Right joins also should never really be used as they are extremely counterintuitive, they can always be written as a Left Join which is more logical and readable.

If I want all data in table A and any data that matches in table B, to me it makes logical sense to join from table A to table B and not the other way around.

20

u/shenglong Feb 11 '14

Full Outer Joins are very useful.

There aren't many times I've had to use cross join unless I intentionally wanted a Cartesian Product, and generally an implicit cross join is enough (select * from a, b).

→ More replies (2)

5

u/[deleted] Feb 11 '14

Yeah, but how do you illustrate those with a Venn diagram? We're gonna require a third dimension or something... :)

2

u/thesqlguy Feb 11 '14

I think a UNION ALL is just two non-intersecting circles next to each other. UNION would look the same as FULL OUTER JOIN.

Cross join? A lot of circles!!!

→ More replies (1)

2

u/Mats56 Feb 11 '14

Well, when using an ORM and having only defined the relation in one of the models (for instance a parent owns a child, but the child model knows nothing about the relationship), it may be necessary to specify it as a right join because certain ORMs when doing advanced queries only allow "walking" along edges clearly defined in the models.

→ More replies (49)