RIGHT OUTER JOIN SQL

The RIGHT JOIN is used to bring together results from two tables. Generally a PRIMARY KEY column from table2 is compared with a column in table1 containing similar data to determine the rows' output. A RIGHT JOIN is an OUTER JOIN. The results of the query will contain all of the rows in table2. In some applications this will be called a RIGHT OUTER JOIN. The syntax is as follows:

SELECT table(1 or 2).column(s)
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column

To see this JOIN in action. Imagine a Monopoly game. We have a transaction table logging property purchases and we have a property table with a list of the properties available. We want to find which properties are owned by whom.

trans_idowner_nameprop_idturn
1Mary61
2Mary96
3Tom58
4Joe214
5Joe137


prop_idnamepricegroup
1Baltic Ave60purple
2Mediterranean Ave60purple
3Reading Railroad200rr
4Oriental Ave100light blue
5Vermont Ave100light blue
6Connecticut Ave120light blue

To get the results of our RIGHT JOIN query we would do the following:

SELECT property.prop_id, property.name, transactions.owner_name
FROM transactions
RIGHT JOIN property
ON transactions.prop_id = property.prop_id
ORDER BY property.prop_id

Note in the table below that the owner names of the properties not currently owned are left blank. This is the desired result in a RIGHT JOIN where all the rows of the right table are listed, but only corresponding information from the left table. In this way we can virtually make one table the extension of another.

prop_idnameowner_name
1Baltic AveJoe
2MediterraneanJoe
3Reading Railroad
4Oriental Ave
5Vermont AveTom
6Connecticut AveMary

< LEFT JOIN SQL | FULL JOIN SQL >


Send notes in disappearing ink!

Interesting Pages