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_id | owner_name | prop_id | turn |
|---|---|---|---|
| 1 | Mary | 6 | 1 |
| 2 | Mary | 9 | 6 |
| 3 | Tom | 5 | 8 |
| 4 | Joe | 2 | 14 |
| 5 | Joe | 1 | 37 |
| prop_id | name | price | group |
|---|---|---|---|
| 1 | Baltic Ave | 60 | purple |
| 2 | Mediterranean Ave | 60 | purple |
| 3 | Reading Railroad | 200 | rr |
| 4 | Oriental Ave | 100 | light blue |
| 5 | Vermont Ave | 100 | light blue |
| 6 | Connecticut Ave | 120 | light 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_id | name | owner_name |
|---|---|---|
| 1 | Baltic Ave | Joe |
| 2 | Mediterranean | Joe |
| 3 | Reading Railroad | |
| 4 | Oriental Ave | |
| 5 | Vermont Ave | Tom |
| 6 | Connecticut Ave | Mary |
< LEFT JOIN SQL | FULL JOIN SQL >
