Join in SQL  is again important topic in database. The good thing about this topic is that we all know about it but even after that we do mistake in explaining it. We know all the definitions, all types of join then also we are not clear about it . The reason I can say is instead of being application minded we restrict ourselves only to the content. If I think about the audience of this article I can assume that it is definitely going to help college students but its also beneficial to people who are working on database in MNCs.

I have seen from my personal experience that people get confused between left outer join and inner join and end up having a bug on their name. So the purpose is that after reading this article people should never have to study about joins in table again.

I can start with the definitions of join and can discuss about types of join but I am not going to do it as I have already said lets try to become application minded. I will try to represent join in visual way so that it will be easy for everyone.

Lets take two table in database and try to understand joins .
 TableA                       

 
 TableB

1. If we write the query :

Select * from TableA a INNER JOIN &nbsp;<br />
	TableB b on a.name = b.name ;

This query will result into the output :

So, as shown in the figure in this case Inner join produces only the set of records that match in both Table A and Table B.

2. If we write the query :

Select * from TableA a FULL OUTER JOIN<br />
	TableB b on a.name = b.name ;

 
The query will result into the output :

 
           
So as shown in this figure in this case Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.
 

3. If we write query :

Select * from TableA a LEFT OUTER JOIN<br />
	TableB b on a.name = b.name ;

 
The query will result into the output :

 

 
So as shown in this figure in this case Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.
 
Now lets discuss some special cases :

1. Suppose I want to get records of only TableA but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.

 
So the query will be like :

Select * from TableA a LEFT OUTER JOIN<br />
	TableB b on a.name = b.name where b.id is NULL;

 
The query will result into the output :

 

 
To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

2. So the query will be like:

Select * from TableA a FULL OUTER JOIN<br />
	TableB b on a.name = b.name where b.id is NULL or a.id is NULL;

 
 
The query will result into the output :

 

 
I hope things should be clear now and you guys don’t get confuse in Joins in future.
Happy Reading .