Visualizing the JOIN command in SQL

Many websites I visited when trying to figure out how JOIN commands work in SQL had Venn Diagrams to illustrate how they worked:

Since I found it challenging to understand exactly what is happening when you JOIN tables in SQL, I made some diagrams of what the different types of JOIN commands do to the tables based on my notes from today.

Inner Join

This type is like an intersection in a venn diagram.

Here is my example using psedo tables:

You can see that an inner join only returns the stuff the two tables have in common.

Full Join

This type is like a union in a venn diagram.

Here is my example using psedo tables:

You can see that a full join returns all the unique columns from both tables.

Left Join & Right Join

A left join will give you everything in the first table, plus the data connected to the column(s) that you a joining on. Here is my example using psedo tables:

A right join will give you everything in the second table, plus the data connected to the column(s) that you a joining on. Here is my example using psedo tables:

You can see here that the order than you join tables is important (the right and left join of the two tables gave us very different shapes).

Cross Join

This type of join doesn’t translate well into a venn diagram picture. It is a new table that contains all the data from the first table with all the combination of the elements in the table that is being joined. Here is my example using pseudo tables:

These examples don’t cover edge cases, but hopefully it’s a good way to think about the big picture.