LEFT OUTER JOIN Vs RIGHT OUTER JOIN Vs FULL OUTER JOIN

There seems to be some confusion among some people about LEFT JOIN (Same as LEFT OUTER JOIN) , RIGHT JOIN (Same as RIGHT OUTER JOIN) and FULL JOIN (Same as FULL OUTER JOIN). Let me explain these three with a simple example

Lets say we have two tables called tblCustomer and tblInvoice where as tblInvoice holds a foriegn key for tblCustomer in fCustomerID column. Below are the data exists in our two sample tables;

tblCustomer



tblInvoice

You may be already aware of the most popular JOIN type we use INNER JOIN (Or just JOIN). In INNER JOIN what happens is on the given conditional column data it displays matching rows from both the tables. Below is an example of INNER JOIN results on our two sample tables.

SELECT * FROM tblCustomer c INNER JOIN tblInvoice i ON c.ID = i.fCustomerID 

As you can see from the above results it returns four rows which are having matching values in tblCustomer's ID column and tblInvoice's fCustomerID column. Any other unmatching rows from both the tables are discarded in the result set.

Now let’s see what would be the result of a LEFT OUTER JOIN between these two tables. LEFT and RIGHT outer joins behavior is solely depends on the order we join two tables. In a JOIN first table appear in the JOIN statement is the LEFT table, and the second table is always the RIGHT table.

SELECT * FROM tblCustomer c

LEFT OUTER JOIN tblInvoice i ON i.fCustomerID = c.ID

As you can see six rows are returned. First it returns all the matching rows and additionally it returns any row(s) from the LEFT(tblCustomer) table which does not have a matching value from RIGHT(tblInvoice) table. In our example customer William (4) and Shaki (5) does not have any invoices recorded for them, but they appear additionally in our join since it is a LEFT OUTER JOIN.

RIGHT OUTER JOIN also behaves the same way except the fact that it displays unmatching rows from the RIGHT table. See the example below;

SELECT * FROM tblCustomer c

RIGHT OUTER JOIN tblInvoice i ON c.ID = i.fCustomerID

As you can see here five rows are retuned all the matching rows and additionally a record from tblInvoice which does not have any customerid associated with it.

Now having understood the LEFT and RIGHT OUTER JOINS let’s see how the FULL JOIN or the FULL OUTER JOIN behaves.

In FULL OUTER JOIN it doesn’t matter the order you join your tables, it first returns the matching rows from both the tables and then the LEFT side table rows which don’t have any matching rows with RIGHT side table and finally RIGHT side table rows which don’t have any matching rows with LEFT side table. In other words it is kind of a union of both RIGHT OUTER JOIN and the LEFT OUTER JOIN.

Lets see an example anyway,

SELECT * FROM tblCustomer c

FULL OUTER JOIN tblInvoice i ON c.ID = i.fCustomerID

As you can see seven rows are returned here. It has the results of our both LEFT OUTER JOIN query and RIGHT OUTER JOIN query.

One of the major practical usage of OUTER JOINS are to keep records from one table and get the matching values from other tables if there are any. For example if you want to have all the invoices and also customer name. If you use an INNER JOIN you will easily loose some invoice records which doesn’t have a matching customer id. So if you use the appropriate OUTER JOIN, you can get this done.

To get these concepts in to your blood stream you should practice it more with real world queries, if you have any questions please drop a comment I am always here to answer you. Hope you all have cleared your mind about JOIN types in TSQL with this article.