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