Joins used to retrieve data from two or more tables based on common columns between the tables.

Types of Joins

1. INNER JOIN
2. LEFT JOIN or LEFT OUTER JOIN
3. RIGHT JOIN or RIGHT OUTER JOIN
4. FULL  JOIN or FULL OUTER JOIN
5. CROSS JOIN

To perform join lets create tables and insert some data 

CREATE TABLE TblCourse
(
    CourseCode VARCHAR(15) NOT NULL PRIMARY KEY,
    CourseName VARCHAR(50)
)

GO

INSERT INTO TblCourse(CourseCode,CourseName)
SELECT 'BCA','Bachelor of Computer Applications' UNION 
SELECT 'BPharma','Bachelor of Pharmacy' UNION 
SELECT 'MCA','Master of Computer Applications' UNION 
SELECT 'B.Arch','Bachelor of Architecture'  

GO

CREATE TABLE TblStudent
(
    SRNo VARCHAR(15) NOT NULL PRIMARY KEY,
    StudentName VARCHAR(50),
    FatherName VARCHAR(50),
    CourseCode VARCHAR(15)
	constraint tblStudent_CourseCode_FK FOREIGN KEY (CourseCode) references tblCourse(CourseCode)
)

GO

INSERT INTO TblStudent (SRNo, StudentName, FatherName, CourseCode)
SELECT 'BCA001', 'Kapil Singh', 'Rahul Kumar', 'BCA' UNION
SELECT 'BCA002', 'Ram Kumar', 'Subham Kumar', 'BCA' UNION
SELECT 'BPharma001', 'Om Prakash', 'Sachin Kumar', 'MCA' UNION
SELECT 'MCA001', 'Kamal Kumar', 'Sachin Kumar', 'MCA' UNION
SELECT 'MCA002', 'Pradeep Saraswat', 'Satish Saraswat', 'MCA' UNION
SELECT 'TEMP001', 'Raj Awasthi', 'Vipin Awasthi', NULL UNION
SELECT 'TEMP002', 'Swati', 'Gaurav Kumar', NULL
Result:-

JOIN or INNER JOIN :- It returns matching rows from two or more tables. The matching is done based on the common columns of tables.

Question:- Write a query to return SRNo, StudentName, FatherName and CourseName from tblStudent and tblCourse tables.
    
SELECT SRNo,StudentName,FatherName,CourseName
FROM TblStudent JOIN TblCourse
ON TblStudent.CourseCode=TblCourse.CourseCode
Note:- You can also use INNER JOIN instead o JOIN. Both JOIN and INNER JOIN always produce the same result set.

Result:- Above query fetch the records that meet the join criteria (TblStudent.CourseCode=TblCourse.CourseCode). As per the records available in the tblStudent table, Students with SRNo "TEMP001" and "TEMP002" have NULL values in CourseCode column. So we can say these students do not associate with any course. So INNER JOIN Query will exclude these 2 records and returns the rest 5 records as per the image.



LEFT JOIN OR LEFT OUTER JOIN:- LEFT OUTER JOIN returns all the matching + non-matching records from LEFT side table (tblStudent) and null for non-matching record based on the join criteria.
      
SELECT SRNo,StudentName,FatherName,CourseName FROM 
TblStudent LEFT JOIN TblCourse 
ON TblStudent.CourseCode=TblCourse.CourseCode

OR 

SELECT SRNo,StudentName,FatherName,CourseName FROM 
TblStudent LEFT OUTER JOIN TblCourse 
ON TblStudent.CourseCode=TblCourse.CourseCode
Result:- 



RIGHT JOIN or RIGHT OUTER JOIN:- RIGHT OUTER JOIN return all the matching + non-matching records from RIGHT side table (tblStudent) and null for non-matching record based on the join criteria.
         
SELECT SRNo,StudentName,FatherName,CourseName FROM 
TblStudent RIGHT JOIN TblCourse 
ON TblStudent.CourseCode=TblCourse.CourseCode

OR

SELECT SRNo,StudentName,FatherName,CourseName FROM 
TblStudent RIGHT OUTER JOIN TblCourse 
ON TblStudent.CourseCode=TblCourse.CourseCode
Result:- 


FULL JOIN or FULL OUTER JOIN:- FULL OUTER JOIN return all records from both table (tblStudent + tblCourse) and null for non-matching record based on the join criteria. 
SELECT SRNo,StudentName,FatherName,CourseName FROM 
TblStudent FULL JOIN TblCourse 
ON TblStudent.CourseCode=TblCourse.CourseCode

OR

SELECT SRNo,StudentName,FatherName,CourseName FROM 
TblStudent FULL OUTER JOIN TblCourse 
ON TblStudent.CourseCode=TblCourse.CourseCode

Result:-

CROSS JOIN:-It returns the Cartesian product of two tables 
SELECT SRNo,StudentName,FatherName,CourseName FROM 
TblStudent CROSS JOIN TblCourse 
ORDER BY SRNo