RSS Feed

Examples of Joins

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

An Example of INNER JOIN

The "Persons" table:
P_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes
2SvendsonToveBorgvn 23Sandnes
3PettersenKariStorgt 20Stavanger

The "Orders" table:

O_IdOrderNoP_Id
1778953
2446783
3224561
4245621
53476415

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:

LastNameFirstNameOrderNo
HansenOla 22456
Hansen Ola24562
Pettersen Kari77895
Pettersen Kari44678

[Via]


An Example of LEFT OUTER JOIN

XXTable:

XXID
xx11
xx2 2
xx3 3
xx4 4
xx5 5
xx7 7

YYTable:

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

-

XXXXtable.idYYtable.idYY
xx111yy1
xx222yy2
xx333yy3
xx444yy4
xx55-
xx777yy7

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

XXXXtable.idYYtable.idYY
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_idLastNameFirstNameCity
1DhallSachinDelhi
2GuptaPankajBangalore
3KumarSanjeevChandigarh

Table O

o_idOrderNop_id
11113
22223
333314
44442
55552


SELECT P.LastName, P.FirstName, O.OrderNo
FROM P
FULL JOIN O
ON P.P_Id=O.P_Id
ORDER BY P.LastName

LastNameFirstNameOrderNo
NULLNULL333
DhallSachinNULL
GuptaPankaj444
GuptaPankaj555
KumarSanjeev111
KumarSanjeev222

No comments:

Post a Comment