Quick review of SQL Joins

TYPE OF JOIN	SQL QUERY	RESULT	REMARK
	SELECT * FROM [HKS].[dbo].[MERIT_LIST]	Rank Marks RollNo 1 95 2002 2 95 1001 3 91 3002 4 89 2004 5 88 2001	Note that Roll No - 1002, 1004 don't exist in this table.
	SELECT * FROM [HKS].[dbo].[STUDENT_RECORD]	RollNo Name Gender Class 1001 ANITA F 9TH 1002 MIKE M 9TH 2001 ROBIN M 10TH 3002 SMITH M 12TH 1004 SUE F 9TH 2002 RAMSE M 10TH	Note that Roll No 2004 doesn't exist in this table.
INNER JOIN	SELECT LIST.RANK, LIST.MARKS, RECORD.NAME, RECORD.GENDER FROM STUDENT_RECORD AS RECORD INNER JOIN MERIT_LIST AS LIST ON LIST.RollNo = RECORD.RollNo ORDER BY LIST.RANK	RANK MARKS NAME GENDER 1 95 RAMSE M 2 95 ANITA F 3 91 SMITH M 5 88 ROBIN M	At least one match in both tables
LEFT JOIN OR LEFT OUTER JOIN	SELECT LIST.RANK, LIST.MARKS, RECORD.NAME, RECORD.GENDER FROM STUDENT_RECORD AS RECORD LEFT JOIN MERIT_LIST AS LIST ON LIST.RollNo = RECORD.RollNo ORDER BY LIST.RANK	RANK MARKS NAME GENDER NULL NULL MIKE M NULL NULL SUE F 1 95 RAMSE M 2 95 ANITA F 3 91 SMITH M 5 88 ROBIN M	Returns all rows from the left table, even if there are no matches in the right table Here LEFT TABLE - STUDENT_RECORD RIGHT TABLE - MERIT_LIST So Roll No 1002 (Mike) and 1004 (Sue) exist here with null marks.
RIGHT JOIN OR RIGHT OUTER JOIN	SELECT LIST.RANK, LIST.MARKS, RECORD.NAME, RECORD.GENDER FROM STUDENT_RECORD AS RECORD RIGHT JOIN MERIT_LIST AS LIST ON LIST.RollNo = RECORD.RollNo ORDER BY LIST.RANK	RANK MARKS NAME GENDER 1 95 RAMSE M 2 95 ANITA F 3 91 SMITH M 4 89 NULL NULL 5 88 ROBIN M	Returns all rows from the right table, even if there are no matches in the left table Here LEFT TABLE - STUDENT_RECORD RIGHT TABLE - MERIT_LIST So Roll No 2004 exists here without name and gender information.
FULL JOIN	SELECT LIST.RANK, LIST.MARKS, RECORD.NAME, RECORD.GENDER FROM STUDENT_RECORD AS RECORD FULL JOIN MERIT_LIST AS LIST ON LIST.RollNo = RECORD.RollNo ORDER BY LIST.RANK	RANK MARKS NAME GENDER NULL NULL MIKE M NULL NULL SUE F 1 95 RAMSE M 2 95 ANITA F 3 91 SMITH M 4 89 NULL NULL 5 88 ROBIN M	If there is a match in one of the tables