Examples of Joins

 

Examples of Inner Join, Left Outer Join, Right Outer Join & Full Join

An Example of INNER JOIN

 The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

 The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

 Now we want to list all the persons with any orders.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

INNER JOIN Orders

ON Persons.P_Id=Orders.P_Id

ORDER BY Persons.LastName

 The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

 [Via]

An Example of LEFT OUTER JOIN

 XXTable:
 

XX	ID
xx1	1
xx2	2
xx3	3
xx4	4
xx5	5
xx7	7

YYTable:

ID	YY
1	yy1
2	yy2
3	yy3
4	yy4
6	yy6
7	yy7

SELECT xx, xxtable.id, yytable.id, yy

FROM xxtable

LEFT OUTER JOIN yytable

ON xxtable.id=yytable.id;

XX	XXtable.id	YYtable.id	YY
xx1	1	1	yy1
xx2	2	2	yy2
xx3	3	3	yy3
xx4	4	4	yy4
xx5	5	-
xx7	7	7	yy7

 [Via]

An Example of RIGHT OUTER JOIN

(taking same XXtable & YYtable tables as above)

 Now, let's say we wanted all of the rows in the table 2 (yy table, the "right" table) in our result, regardless of whether they matched with rows in table 1 (the xx table, the "left" table), we could perform a RIGHT OUTER JOIN between tables xxtable and yytable.

 The following SQL syntax:

 SELECT xx, xxtable.id, yytable.id, yy

 FROM xxtable

 RIGHT OUTER JOIN yytable

 ON xxtable.id=yytable.id;

 would give the following result:

XX	XXtable.id	YYtable.id	YY
xx1	1	1	yy1
xx2	2	2	yy2
xx3	3	3	yy3
xx4	4	4	yy4
-	-	6	yy6
xx7	7	7	yy7

 In this example, there is a NULL value for the fields xxtable.id and xx where yytable.id=6 since xxtable does not have a record with xxtable.id=6. Note that all of the records of the right table in a RIGHT OUTER JOIN get displayed, regardless of whether or not they match rows in the left table.

An Example of FULL JOIN

 Table P

p_id	LastName	FirstName	City
1	Dhall	Sachin	Delhi
2	Gupta	Pankaj	Bangalore
3	Kumar	Sanjeev	Chandigarh

 Table O

o_id	OrderNo	p_id
1	111	3
2	222	3
3	333	14
4	444	2
5	555	2

 SELECT P.LastName, P.FirstName, O.OrderNo

 FROM P

 FULL JOIN O

 ON P.P_Id=O.P_Id

 ORDER BY P.LastName

LastName	FirstName	OrderNo
NULL	NULL	333
Dhall	Sachin	NULL
Gupta	Pankaj	444
Gupta	Pankaj	555
Kumar	Sanjeev	111
Kumar	Sanjeev	222