SELECT ... RIGHT JOIN

---

SELECT Query with RIGHT JOIN

Returns all the records from the right table and matched records from the left table.This is also referred as RIGHT OUTER JOIN

Syntax

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Example

Consider the following two tables,

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ranith  |  32 | Ahmedabad |  2000.00 |
|  2 | Krupa   |  25 | Delhi     |  1500.00 |
|  3 | kaulik  |  23 | Kota      |  2000.00 |
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |
|  5 | Munny   |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now, using the RIGHT JOIN let us join these two tables as follows.

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    3 | krutik   |   3000 | 2009-10-08 00:00:00 |
|    3 | krutik   |   1500 | 2009-10-08 00:00:00 |
|    2 | Krupa    |   1560 | 2009-11-20 00:00:00 |
|    4 | Chaitan  |   2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+