Why Do We Have Left, Right, and Inner Joins in SQL?

English: This diagram is generated by "Re...

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).

E.g:

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

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s