This is one of those blitz posts I occasionally write.
This post is meant for those who like to just skim through the loads of paperwork that often tends to be boring.
Here’s the drift straight up. You have LEFT JOIN, RIGHT JOIN, and INNER JOIN.
We use the LEFT JOIN most times because by default we seek the data from the table on the left side, (the table calling the join).
SELECT U.*, P.screen_name FROM users U LEFT JOIN profiles P ON (users.id=profiles.user_id) WHERE users.id = 2;
The users table is the one calling the join (on the left) in the above SQL query.
* Use LEFT joins when you want to return data in the left table whether or not there is a matching row in the right table. The above will return the user with id = 2 whether or not there is a profile record in the profiles table.
* Use RIGHT join when you want data from the RIGHT table whether or NOT there is a matching row in the LEFT table. With this RIGHT JOIN, even if there is no user with id =2 in the users table, and there is a row in the profiles table with user_id =2, we will get a result after the query containing the profile information at least.
The third most used join is the INNER JOIN, which is like strict mode, it’s all or nothing.
* Use INNER join when you want records from both tables in every case. The INNER join will return results only if there is a row in the users table that matches a row in the profile table, otherwise you will get no results if any of the tables does not have a corresponding row for the other side of the join either left or right.
There are other types of JOINS like the CROSS JOIN, and combinations of different JOINs like FULL OUTER JOIN…
You get the drift now.
You can see some more information about joins in this external article http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html