Venn Diagram for Natural Join

Question

I've been trying to understand the concept of sql joins fully, venn diagrams have helped me a lot to do that. I've found them for all kind of joins but not for natural joins.

How would a venn diagram for a natural join look like?

Answer 1

Venn diagrams are not very helpful for understanding natural (inner) join--or any join, natural or not. Most Venn diagrams associated with joins on Stack Overflow and the web are parroted worthless misrepresentations--even in cases where a Venn diagram could be useful.

Here are some valid uses of Venn diagrams for SQL natural join:

We can have an area be a set whose elements are an associated table's column names. Then the left and right circle elements are the left and right table column names, the overlap elements are the common column names (which must appear once in each input table), and the combined elements are the result's column names. But a SQL table can have duplicate column names, and columns are ordered; so this doesn't give the result column list.

We can have an area be a set whose elements are the distinct subrow values under the common columns. Then the left and right circle elements are the left and right table subrow values, and the overlap elements are the result subrow values. But this doesn't give how many times a subrow value appears in a table. In particular, when all columns are common, so the subrow values are the row values, this doesn't give how many times a row value appears in the result.

Neither diagram nor the pair gives what the output rows are in general.

From my answer at CROSS JOIN vs INNER JOIN in SQL :

Re Venn diagrams A Venn diagram with two intersecting circles can illustrate the difference between output rows for INNER, LEFT, RIGHT & FULL JOINs for the same input. And when the ON is unconditionally TRUE, the INNER JOIN result is the same as CROSS JOIN. Also it can illustrate the input & output rows for INTERSECT, UNION & EXCEPT. And when both inputs have the same columns, the INTERSECT result is the same as for standard SQL NATURAL JOIN, and the EXCEPT result is the same as for certain idioms involving LEFT & RIGHT JOIN. But it does not illustrate how (INNER) JOIN works in general. That just seems plausible at first glance. It can identify parts of input and/or output for special cases of ON, PKs (primary keys), FKs (foreign keys) and/or SELECT. All you have to do to see this is to identify what exactly are the elements of the sets represented by the circles. (Which muddled presentations never make clear.) (Remember that in general for joins output rows have different headings from input rows.)

I repeat with emphasis:

But it does not illustrate how (INNER) JOIN works in general.

All you have to do to see this is to identify what exactly are the elements of the sets represented by the circles.

From one of my comments on an answer there re its Venn diagram for inner join:

Figure 1 is a common terrible attempt to explain JOIN. Its [legend] is also complex: It's only for tables as sets & only equijoin & only one [column]; it also represents the input differently than the output. Write it for JOIN in general.

From one of my comments on What is the difference between "INNER JOIN" and "OUTER JOIN"? :

Venn diagrams show elements in sets. Just try to identify exactly what the sets are and what the elements are in these diagrams. The sets aren't the tables and the elements aren't their rows. Also any two tables can be joined, so PKs & FKs are irrelevant. All bogus. You are doing just what thousands of others have done--got a vague impression you (wrongly) assume makes sense.

And another:

Of the answers & comments & their references below only one actually explains how Venn diagrams represent the operators: The circle intersection area represents the set of rows in A JOIN B. The area unique to each circle represents the set of rows you get by taking its table's rows that don't participate in A JOIN B and adding the columns unique to the other table all set to NULL. (And most give a vague bogus correspondence of the circles to A and B.)

So Venn diagrams are relevant for certain cases where tables can reasonably be considered to hold sets of row-valued elements. But in general SQL tables do not hold sets of row-valued elements, while Venn diagrams denote sets.

Re illustrating inner vs outer joins via Venn diagrams:

From my comment on LEFT JOIN vs. LEFT OUTER JOIN in SQL Server :

Re Venn diagrams: If no nulls or duplicate rows are input [or output], so we can take a table to be a set of row-valued values & use normal math =, then the Venn diagrams are OK--taking circles to hold left & right join output tables/sets. But if nulls or duplicate rows are input [or output] then it is so difficult to explain just what the circles are sets of & how those sets relate to input & output tables/bags that Venn diagrams are not helpful.

From my comment on my answer to What is the difference between "INNER JOIN" and "OUTER JOIN"? :

I must admit that, despite my quick phrasing in comments, because SQL involves bags & nulls and SQL culture doesn't have common terminology to name & distinguish between relevant notions, it is non-trivial even to explain clearly how elements of a Venn diagram are 1:1 with output "rows", let alone input "rows". Or what inner or outer joins do, let alone their difference. "value" may or may not include NULL, "row" may be a list of values vs a slot in a table value or variable & "=" may be SQL "=" vs equality.

See also my answer at Inner Join vs Natural Join vs USING clause: are there any advantages? .

PS Often diagrams are called Venn diagrams when they are really Euler diagrams.