While we at mysqljoin.com want to provide simply and straightforward tutorials which are easy to understand, we love in depth documentation as well. And since relational databases are very complex, a documentation can be much more technical than our tutorials are.

We just came across such a documentation, a very detailed explanation of SQL join types. We felt that we have to share this with our readers. It’s a very nice follow up reading for everybody who’s currently learning about MySQL joins. Beside many interesting facts you’ll find probably the most interesting visualisation of joined tables we’ve ever seen as well as a visualized classification schemes for SQL joins.

There are different classification schemes and different criteria according to what joins are classified. As a result there is a bit mess in the process of understanding them. …

Generally speaking, every result set that is created by a join is a certain combination of rows which are stored in MySQL tables. Even if you join more than two tables, MySQL always combines two result sets at a time. These result sets can be visualized with the help of so called Venn Diagramms, a mathematical representation of sets. Taking a look at them sometimes makes it easier to understand what’s actually happening. In addition, the human brain can store information like the different MySQL join operations or the related MySQL join syntax if it can connect them with some kind of picture. That’s the reason why we provide a graphical representation (a Venn Diagramm) for every MySQL join tutorial. You can access them by browsing all available “join tutorials“. …

If you used to write MySQL joins for MySQL versions < 5.0 or upgrade your server from MySQL 4 to MySQL >= 5.0 you maybe run into a problem when you execute the following query:

SELECT *
FROM mytable1, mytable2
INNER JOIN mytable3
ON mytable1.mycolumnname = mytable3.mycolumnname
WHERE mytable1.id = mytable2.id;

#1054 – Unknown column ‘mytable.mycolumnname’ in ‘on clause’

Even though you made sure that the column exists, the problem persists. It can be a very annoying and time-consuming task to track this kind of error down to it’s cause: MySQL starting from version 5.0 tries to be more compliant to ANSI SQL. The tables are beeing joined in a different order. The solution to this problem is actually very simple. Surround the tables in the FROM clause with round brackets:

SELECT *
FROM (mytable1, …