The understanding of joins is broken. To be continued. Attempting Alternative Visualization

    Many of you have read the previous article about how incorrect visualization to explain how JOINs work can be confusing in some cases. Venn circles cannot fully illustrate certain points, for example, if the values ​​in the table are repeated.

    In preparation for recording the sixth issue of the Zinc Prod podcast (where we agreed to discuss the article), it seems I managed to find one interesting version of the visualization. In addition, a similar option was also offered in the comments on the original article.


    Everyone is invited under the cut


    So, visualization. As we found out in the comments to the previous article, join is more a Cartesian product than an intersection. If you look at how the Cartesian product is illustrated, you can see that it is often a rectangular table, where the first relation goes along one axis and the second one along the other. Thus, the elements of the table will be all combinations of everything.


    It is difficult to abstractly draw it, so it will have to be an example.


    Let's say we have two tables. In one of them


    id
    --
    1
    1
    6
    5

    In another:


    id
    --
    1
    1
    2
    3
    5

    Immediately disclaimer: I called the field the word "id" just for short. Many in the last article were indignant, as it is - id repeated, disgrace. Do not worry too much, well,
    imagine, for example, that this is a table with daily statistics, where for each day and each user there is data on visiting a site. In general, not the point.


    So, we want to find out what happens with the various table joins. Let's start with CROSS JOIN:


    CROSS JOIN


    SELECT t1.id, t2.id
    FROM t1 
        CROSS JOIN t2

    CROSS JOIN is all all possible combinations which can be received from two tables.


    This can be visualized as follows: on the x axis - one table, on the y axis - another, all the cells inside (highlighted in orange) are the result



    INNER JOIN


    INNER JOIN (or just JOIN) is the same CROSS JOIN that has only those elements that satisfy the condition written in the "ON" construct. Pay attention to the situation when records are duplicated - there will be four results with units.


    SELECT t1.id, t2.id
    FROM t1 
        INNER JOIN t2
            ON t1.id = t2.id


    LEFT JOIN


    LEFT OUTER JOIN (or just LEFT JOIN) is the same as INNER JOIN, but in addition we add null for rows from the first table, for which nothing was found in the second


    SELECT t1.id, t2.id
    FROM t1
        LEFT JOIN t2
            ON t1.id = t2.id


    RIGHT JOIN


    RIGHT OUTER JOIN (or RIGHT JOIN) is the same as LEFT JOIN, just the opposite. Those. this is INNER JOIN + null for rows from the second table, for which nothing was found in the first


    SELECT t1.id, t2.id
    FROM t1
        RIGHT JOIN t2
            ON t1.id = t2.id


    → You can play with requests here


    conclusions


    It seems to be a simple visualization. Although there are limitations: it shows the case when equality is written in ON, and not something tricky (any Boolean expression). In addition, the case where null is among the table values ​​has not been considered. Those. this is still some simplification, but it seems to have turned out better and more accurate than the Venn circles.


    Subscribe to our Zinc Prod podcast , where we discuss databases, software development, and other interesting things.


    Also popular now: