RIGHT OUTER JOIN

What a different day in my neck of the woods. For starters, early this morning around 02:00 AM, my wife’s tables, her cell phone and my phone in unison woke up both of us. They all broadcasted a flood warning alert for the area in the Twin Cities where we live in. The development we live in is relatively high and surrounded by ponds and lakes that are capable of absorbing large amounts of rain. Nonetheless, shortly after we received the warning it started to pour. The storm had several cells that included lightning and thunder which seems to be amplified when all is quiet at night.

When the rain subsided shortly after 07:00 AM, took the dogs out for their first walk of the day. There was a very light rain at the time but the parkway was very clean and the surrounds were quite green. With the heat we have been experiencing in the last few days and the added rain and humidity all grasses and trees are full and looking quite nice. I will take a rainy and stormy day in spring or summer over a winter day. After such a long and cold winter we all welcome the heat, rain and the associated benefits.

I did think about people that live and own homes that surround Minnehaha Creek in the Twin Cities of Minneapolis and St. Paul. Yesterday morning the news talked about the amount of water running at 330+ cubic feet per second. The creek should be at 100 cubic feet per second. The additional rain we received for several hours this morning might have caused some flooding to houses by the creek.

Let’s see if we can figure out what is in a RIGHT OUTER JOIN. This is the second type of OUTER JOIN. In the previous blog entry we discussed the LEFT OUTER JOIN. In the next blog we will discuss the FULL OUTER JOIN.

As we did in the previous two (2) entries we will continue to use the database tables: a_tbl and b_tbl introduced in the “Database JOINs” blog entry.

Let’s open with the following base query:

mysql> select *
-> from a_tbl RIGHT OUTER JOIN b_tbl
-> on A = B;
+——+——+—–+—–+
| AID | A | BID | B |
+——+——+—–+—–+
| 9 | 105 | 10 | 105 |
| 10 | 105 | 10 | 105 |
| 12 | 109 | 12 | 109 |
| 12 | 109 | 13 | 109 |
| NULL | NULL | 9 | 103 |
| NULL | NULL | 11 | 107 |
| NULL | NULL | 14 | 110 |
| NULL | NULL | 15 | 111 |
+——+——+—–+—–+
8 rows in set (0.00 sec)

Using the keyword RIGHT in the query, the query results and the contents of the b_tbl:

mysql> select *
-> from b_tbl;
+—–+—–+
| BID | B |
+—–+—–+
| 9 | 103 |
| 10 | 105 |
| 11 | 107 |
| 12 | 109 |
| 13 | 109 |
| 14 | 110 |
| 15 | 111 |
+—–+—–+
7 rows in set (0.00 sec)

It appears that this time all the B values from the b_tbl were included in the result table.

Let’s make a simple tweak in the query which should help us visualize this.

mysql> select *
-> from a_tbl RIGHT OUTER JOIN b_tbl
-> on A = B
-> order by B asc;
+——+——+—–+—–+
| AID | A | BID | B |
+——+——+—–+—–+
| NULL | NULL | 9 | 103 |
| 9 | 105 | 10 | 105 |
| 10 | 105 | 10 | 105 |
| NULL | NULL | 11 | 107 |
| 12 | 109 | 12 | 109 |
| 12 | 109 | 13 | 109 |
| NULL | NULL | 14 | 110 |
| NULL | NULL | 15 | 111 |
+——+——+—–+—–+
8 rows in set (0.00 sec)

It seems that 103 from B was not paired with an A value from the a_tbl because it is not available on a_tbl but the B value from b_tbl was included in the results. The 105 from B was paired with two (2) 105 values in the A column. This is obvious by looking at the values in the AID column. The 107 in B did not have a matching 107 in A so as with the 103 value it is included in the results but there is no match from the a_tbl so it shows a NULL. The 109 values is in both database tables so it is included in the resulting table. The 110 in the b_tbl has no match in the a_tbl so it is paired with a NULL value. Finaly the 111 in the b_tbl is also included but since there is no matching value in the a_tbl it is paired with a NULL value.

The previous analysis illustrates that with a RIGHT OUTER JOIN all the values in B are included in the results table whether or not there is a match in the a_tbl.

The next query:

mysql> select A, B
-> from a_tbl RIGHT OUTER JOIN b_tbl
-> on A = B
-> order by B asc;
+——+—–+
| A | B |
+——+—–+
| NULL | 103 |
| 105 | 105 |
| 105 | 105 |
| NULL | 107 |
| 109 | 109 |
| 109 | 109 |
| NULL | 110 |
| NULL | 111 |
+——+—–+
8 rows in set (0.00 sec)

It shows the same values as the previous one without the clutter of including the AID and AIB columns.

If we do not wish to include in the resulting table the NULL values coming from the AID column in the a_tbl we could issue the following query:

mysql> select A, B
-> from a_tbl RIGHT OUTER JOIN b_tbl
-> on A = B
-> where A is not NULL
-> order by B asc;
+——+—–+
| A | B |
+——+—–+
| 105 | 105 |
| 105 | 105 |
| 109 | 109 |
| 109 | 109 |
+——+—–+
4 rows in set (0.03 sec)

The results are the same this time without the NULL values.

In the next blog we will cover the last of the OUTER JOINs.

If you have comments / questions with this or any other blog entry feel free to send me a message.

The Naïve American

Be Sociable, Share!

Leave a comment

Your comment