from Hacker News

SQL Join Types Explained in Visuals

by notoriousarun on 7/7/21, 12:07 PM with 53 comments

  • by twic on 7/7/21, 2:49 PM

    This seems like a terrible explanation.

    Firstly, joins are substantially orthogonal to keys. You can join two relations with no keys at all. Keys might help you make sure that there are going to be results to your join, but they are not required, and i wouldn't start with them.

    Secondly, union is not a join. If mentioned at all, it should be in an addendum about other ways to combine tables.

    Thirdly, left and right joins are specific kinds of outer join. Surely it's much clearer to describe "left, right, and full outer joins" than "left, right, and outer joins".

    Fourthly, surely the most logical way to explain joins is the progressive refinement cross -> inner -> equi -> natural -> full outer -> left/right outer. A cross join is a Cartesian product, simple to explain. Restrict that with a predicate and you have an inner join (i'd use an example where the predicate is not equality). Introduce ON as a synonym for WHERE in inner joins. Make the predicate equality on a set of the columns and it's an equijoin. Introduce USING as a shorthand for ON in equijoins. Make that set all the corresponding columns and it's a natural join. Now include all the rows that didn't match and it's a full outer join. Note that WHERE wouldn't work here. Now just the rows on one side or the other and it's a left/right outer join.

  • by radiospiel on 7/7/21, 1:32 PM

    I really don't get the confusion around JOINs...

    Any JOIN is what the author is calling a "CROSS JOIN", with "ON" (which is optional) basically being a WHERE condition, and "LEFT", "RIGHT", "INNER", "OUTER" words only describing whether or not to drop a result if the left/right side of the join would be a NULL reference.

    And, of course, a "UNION JOIN" is not a join at all, because nothing is joined.

  • by magicalhippo on 7/7/21, 2:55 PM

    I taught myself SQL on a need-to-know basis. One thing that blew my mind and really helped making certain non-trivial queries more manageable was when I realized I could join subqueries. It never occurred to me to try, until one day I did and it worked.

    A contrived example for illustration:

        select oh.oid, oh.order_ref, ol.num_lines, ol.total_value
    from order_head oh
    join (
      select oid, count() as num_lines, sum(value) as total_value 
      from order_lines
      where status = 'ready'
      group by oid
    ) ol on ol.oid = oh.oid
    where total_value > 500
    For certain queries this has led to huge speed-ups, by allowing rows to be filtered sooner rather than post-massive-join. For others it makes it easier to integrate with libraries, as aggregate fields like in the example can be filtered using the main where clause rather than in a having clause.

  • by Diggsey on 7/7/21, 1:03 PM

    It's a nice visualization, but it assumes the keys on both sides are unique. Usually the key on at least one side of a join is not unique, and sometimes it's not unique on either side. The behaviour in these cases is an important part of the behaviour of the overall join.

  • by contravariant on 7/7/21, 12:44 PM

    It's a bit dangerous to visualize Union like that, people might confuse it with concatenation which it's not.

    You'll want to use "UNION ALL" if your intent is to concatenate the two sets of rows, and not to calculate the (deduplicated) union. If only because it's much faster if your database doesn't have to check for duplicates.

  • by ProAm on 7/7/21, 2:32 PM

    I've always liked Jeff Atwoods visual explanation of joins [1]

    [1] https://blog.codinghorror.com/a-visual-explanation-of-sql-jo...

  • by major505 on 7/7/21, 3:06 PM

  • by hactually on 7/7/21, 12:46 PM

    So much better than the usual Venn diagram mess.

    Props to the author

  • by flowerlad on 7/7/21, 1:40 PM

    Not discussed in this article: 1-many, 1-1 and many-many relationships. Also: Fan traps and chasm traps. These issues are important to understand, to avoid serious mistakes.

  • by 725686 on 7/7/21, 2:51 PM

    Just google "sql joins diagram" and you will be rewarded by a quick one image visual representation of each type of join. Here is one in stackoverflow: https://stackoverflow.com/questions/13997365/sql-joins-as-ve...

  • by statmapt on 7/7/21, 1:21 PM

    That is a smooth site. I like how you can suggest edits to their articles right in google docs.

    What framework/service is being used to build it?

  • by b0tch7 on 7/8/21, 5:59 AM

    Off Topic slightly and perhaps deserves a "Ask HN:" post - but I'd love any recommendations for best resources to quickly get to "medium proficiency" with SQL. Thanks!

  • by AtillaBosma on 7/7/21, 12:47 PM

    Good, clear explanation of these SQL joins. Works well as a quick way to mentally visualize what each join would do in your own implementation.

    Thanks for the learning resource.

  • by ohashi on 7/7/21, 12:48 PM

    Nice visualization. Never heard/used cross join. What would be use case for something like that in practice?

  • by jackfoxy on 7/7/21, 1:56 PM

    I like this one page visual for explaining joins much more succinctly https://www.codeproject.com/KB/database/Visual_SQL_Joins/Vis...

  • by killingtime74 on 7/7/21, 10:46 PM

    Missed self join