MySQL Right Join.
MySQL right join fetches matching columns on table 2 with table 1 and print the corresponding row.
To illustrate, I am using 2 tables foods and company.
mysql> select * from foods;
+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1 | Chex Mix | Pcs | 16 |
| 6 | Cheez-It | Pcs | 15 |
| 2 | BN Biscuit | Pcs | 15 |
| 3 | Mighty Munch | Pcs | 17 |
| 4 | Pot Rice | Pcs | 15 |
| 5 | Jaffa Cakes | Pcs | 18 |
| 7 | Salt n Shake | Pcs | 0 |
+---------+--------------+-----------+------------+
7 rows in set (0.01 sec)
mysql> select * from company;
+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME | COMPANY_CITY |
+------------+---------------+--------------+
| 18 | Order All | Boston |
| 15 | Jack Hill Ltd | London |
| 16 | Akas Foods | Delhi |
| 17 | Foodies | London |
| 19 | sip-n-Bite | New York |
+------------+---------------+--------------+
5 rows in set (0.01 sec)
mysql>
I am combing food table to company table. To narrow down the output, I am printing the following tables only:
food. COMPANY_ID,company.COMPANY_ID,company.COMPANY_CITY
mysql> select f.COMPANY_ID,c.COMPANY_ID,c.COMPANY_CITY from foods f,company c;
+------------+------------+--------------+
| COMPANY_ID | COMPANY_ID | COMPANY_CITY |
+------------+------------+--------------+
| 16 | 18 | Boston |
| 16 | 15 | London |
| 16 | 16 | Delhi | <==========
| 16 | 17 | London |
| 16 | 19 | New York |
| 15 | 18 | Boston |
| 15 | 15 | London | <==========
| 15 | 16 | Delhi |
| 15 | 17 | London |
| 15 | 19 | New York |
| 15 | 18 | Boston |
| 15 | 15 | London | <==========
| 15 | 16 | Delhi |
| 15 | 17 | London |
| 15 | 19 | New York |
| 17 | 18 | Boston |
| 17 | 15 | London |
| 17 | 16 | Delhi |
| 17 | 17 | London | <==========
| 17 | 19 | New York |
| 15 | 18 | Boston |
| 15 | 15 | London | <==========
| 15 | 16 | Delhi |
| 15 | 17 | London |
| 15 | 19 | New York |
| 18 | 18 | Boston | <==========
| 18 | 15 | London |
| 18 | 16 | Delhi |
| 18 | 17 | London |
| 18 | 19 | New York |
| 0 | 18 | Boston |
| 0 | 15 | London |
| 0 | 16 | Delhi |
| 0 | 17 | London |
| 0 | 19 | New York |
+------------+------------+--------------+
35 rows in set (0.01 sec)
mysql>
Right join join matching columns in right to that of left and produce null output for missing entries. Once the right join command is executed as below, it will print only the matching columns which is highlighted as above.
So the output is:
mysql> select c.COMPANY_ID,c.COMPANY_CITY,f.COMPANY_ID from company c RIGHT JOIN foods f ON c.COMPANY_ID=f.COMPANY_ID;
+------------+--------------+------------+
| COMPANY_ID | COMPANY_CITY | COMPANY_ID |
+------------+--------------+------------+
| 16 | Delhi | 16 |
| 15 | London | 15 |
| 15 | London | 15 |
| 17 | London | 17 |
| 15 | London | 15 |
| 18 | Boston | 18 |
| NULL | NULL | 0 |
+------------+--------------+------------+
7 rows in set (0.01 sec)
mysql>
Refer the following URL to know more about this:
http://www.w3resource.com/sql/joins/perform-a-right-join.php
MySQL right join fetches matching columns on table 2 with table 1 and print the corresponding row.
To illustrate, I am using 2 tables foods and company.
mysql> select * from foods;
+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1 | Chex Mix | Pcs | 16 |
| 6 | Cheez-It | Pcs | 15 |
| 2 | BN Biscuit | Pcs | 15 |
| 3 | Mighty Munch | Pcs | 17 |
| 4 | Pot Rice | Pcs | 15 |
| 5 | Jaffa Cakes | Pcs | 18 |
| 7 | Salt n Shake | Pcs | 0 |
+---------+--------------+-----------+------------+
7 rows in set (0.01 sec)
mysql> select * from company;
+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME | COMPANY_CITY |
+------------+---------------+--------------+
| 18 | Order All | Boston |
| 15 | Jack Hill Ltd | London |
| 16 | Akas Foods | Delhi |
| 17 | Foodies | London |
| 19 | sip-n-Bite | New York |
+------------+---------------+--------------+
5 rows in set (0.01 sec)
mysql>
I am combing food table to company table. To narrow down the output, I am printing the following tables only:
food. COMPANY_ID,company.COMPANY_ID,company.COMPANY_CITY
mysql> select f.COMPANY_ID,c.COMPANY_ID,c.COMPANY_CITY from foods f,company c;
+------------+------------+--------------+
| COMPANY_ID | COMPANY_ID | COMPANY_CITY |
+------------+------------+--------------+
| 16 | 18 | Boston |
| 16 | 15 | London |
| 16 | 16 | Delhi | <==========
| 16 | 17 | London |
| 16 | 19 | New York |
| 15 | 18 | Boston |
| 15 | 15 | London | <==========
| 15 | 16 | Delhi |
| 15 | 17 | London |
| 15 | 19 | New York |
| 15 | 18 | Boston |
| 15 | 15 | London | <==========
| 15 | 16 | Delhi |
| 15 | 17 | London |
| 15 | 19 | New York |
| 17 | 18 | Boston |
| 17 | 15 | London |
| 17 | 16 | Delhi |
| 17 | 17 | London | <==========
| 17 | 19 | New York |
| 15 | 18 | Boston |
| 15 | 15 | London | <==========
| 15 | 16 | Delhi |
| 15 | 17 | London |
| 15 | 19 | New York |
| 18 | 18 | Boston | <==========
| 18 | 15 | London |
| 18 | 16 | Delhi |
| 18 | 17 | London |
| 18 | 19 | New York |
| 0 | 18 | Boston |
| 0 | 15 | London |
| 0 | 16 | Delhi |
| 0 | 17 | London |
| 0 | 19 | New York |
+------------+------------+--------------+
35 rows in set (0.01 sec)
mysql>
Right join join matching columns in right to that of left and produce null output for missing entries. Once the right join command is executed as below, it will print only the matching columns which is highlighted as above.
So the output is:
mysql> select c.COMPANY_ID,c.COMPANY_CITY,f.COMPANY_ID from company c RIGHT JOIN foods f ON c.COMPANY_ID=f.COMPANY_ID;
+------------+--------------+------------+
| COMPANY_ID | COMPANY_CITY | COMPANY_ID |
+------------+--------------+------------+
| 16 | Delhi | 16 |
| 15 | London | 15 |
| 15 | London | 15 |
| 17 | London | 17 |
| 15 | London | 15 |
| 18 | Boston | 18 |
| NULL | NULL | 0 |
+------------+--------------+------------+
7 rows in set (0.01 sec)
mysql>
Refer the following URL to know more about this:
http://www.w3resource.com/sql/joins/perform-a-right-join.php