Monday, September 10, 2012

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



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


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
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
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

No comments:

Post a Comment