I was reading this post from Jeff Atwood about visually showing the different types of SQL joins. It got me thinking about the different MySQL joins and I wanted to learn more about them. So this post is my rehashing of the different type of joins. Enjoy!
I’ll be using tables like the following for my explanations:
Most people have heard of an inner join before. In fact, i’d go as far as saying it’s the most commonly used join. This joins together all the rows specified into one big result set.
// This will return all of the columns from both tables SELECT * FROM tableA INNER JOIN tableB ON tableA.ID = tableB.productID; // This will return just the 3 fields specified SELECT a.ID, a.Name, b.ProductID FROM tableA AS a INNER JOIN tableB AS b ON a.ID = b.productID;
A cross join will take two tables and mesh them together in every possible combination.
The output of this query would be:
SELECT * FROM People CROSS JOIN Places;
On the surface the Cross Join doesn’t seem very useful. I had to Google around quite a bit to find use cases for it. As best I can tell it is useful when you have a standard set of values in table 2 that pertain to all of the values from table 1, such as color options or times.
Normally the query optimizer will figure out the quickest path to join tables together. This may end up being that it parses the last table joined and working back to the first table. Straight joins are just a way to force MySQL to read the tables left to right.
SELECT People.Name, Places.Location FROM People STRAIGHT JOIN Places ON People.ID = Places.ID;
Left Join (or Left Outer Join)
Returns all of the rows from the left table along with the matching rows from the right table. If there is no match it will return null for the column.
SELECT People.Name, Places.Location FROM People LEFT JOIN Places ON People.ID = Places.ID;
Right Join (or Right Outer Join)
The opposite of the Left Join, it returns all of the rows from the right table along with the matching rows from the left table.
SELECT People.Name, Places.Location FROM People RIGHT JOIN Places ON People.ID = Places.ID;
A natural join is when MySQL looks for columns with the same name to join on. For instance:
SELECT * FROM Addresses NATURAL JOIN States;
Natural Left/Right Join
Same as a normal left/right join, only it used the magic of the natural join to join on columns with the same name.
The most useful in my opinion seem to be inner, left, and right joining. Inner gives you combined results excluding null values, and left and right allow you to search for rows that are null in certain spots easily.
Natural joining is a bit too open for my liking. I prefer to explicitly list the columns to join on.
While I understand cross joining, I don’t think it will be useful in my day to day work at this point. It seems like it has a pretty specific use, and that use isn’t very often.