I found a really good article explaining the difference between the different types of joins in SQL Server. It is important to have these different types to be able to selectively retrieve the information you are looking for in the appropriate ways. I have found that during interviews it is important sometimes to demonstrate this knowledge at a basic level as a sort of proof of understanding on several occasions. http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/
I have found the following sql helpful in understanding the joins in real life sort of situations.
-- I have created a database called test in my server for testing purposes, this statement selects that database into context
use test;
-- The following two statements create nearly identical tables with an identity column ( auto increment integer ) and a textual column ( 64 character )
create table TABLE1 (id integer identity, value nvarchar (64));
create table TABLE2 (id integer identity, value nvarchar (64));
-- the following statements insert data in table 1
INSERT INTO TABLE1 ( value) VALUES('one');
INSERT INTO TABLE1 ( value) VALUES('two');
INSERT INTO TABLE1 ( value) VALUES('three');
INSERT INTO TABLE1 ( value) VALUES('four');
-- the following statements insert data in table 2
INSERT INTO TABLE2 ( value) VALUES('one');
INSERT INTO TABLE2 ( value) VALUES('three');
INSERT INTO TABLE2 ( value) VALUES('five');
INSERT INTO TABLE2 ( value) VALUES('seven');
-- note that in the above inserts there are collisions or the same data in some of the column values
-- also note that the id columns or fields will NOT be used as part of the ON clause
-- NOTE: LEFT JOIN is synonymous with LEFT OUTER JOIN whereas RIGHT JOIN is synonymous with RIGHT OUTER JOIN
-- the OUTER keyword signifies including records that do not exist in the table
select * FROM TABLE1 t1 LEFT join table2 t2 ON t1.value=t2.value;
select * FROM TABLE1 t1 RIGHT join table2 t2 ON t1.value=t2.value;
-- NOTE: using TABLE2 as the first table in the query does have an effect on the result
select * FROM TABLE2 t2 LEFT join table1 t1 ON t1.value=t2.value;
select * FROM TABLE2 t2 RIGHT join table1 t1 ON t1.value=t2.value;
-- NOTE: INNER join is a way to have include mutually inclusive results
select * FROM TABLE1 t1 INNER join table2 t2 ON t1.value=t2.value;
-- NOTE: OUTER join is a way to include the combination of a LEFT and RIGHT join together so that
select * FROM TABLE1 t1 FULL join table2 t2 ON t1.value=t2.value;
Please pay particular attention towards the end of the article on the link posted above as it suggests usage of queries without sub-queries to get data from a table that does not exist in the other table. All of these things play an important role in terms of query performance and understanding of how SQL Server retrieves data.
