RIGHT JOIN

Right outer join is also called as right join. Right join return all the rows in the right table, even there is no matching rows in the left table .

I am going to use following data to demonstrate the example.

CREATE TABLE employee(

  id int PRIMARY KEY,

  firstName VARCHAR(20),

  lastName VARCHAR(20),

  salary DECIMAL(10, 2)

);

INSERT INTO employee VALUES (1, "Hari Krishna", "Gurram", 12345.67);

INSERT INTO employee VALUES (2, "Rama Devi", "Gurram", 1234578.67);

INSERT INTO employee VALUES (3, "Lakshmana", "Rao", 9876543.67);

INSERT INTO employee VALUES (4, "Rama", "Krishna", 1234587.67);

INSERT INTO employee VALUES (5, "Sowmya", "asd", 1238745.67);

INSERT INTO employee VALUES (6, "Jyotsna", "PS", 76543.67);

INSERT INTO employee VALUES (7, "Gireesh", "Amara", 87698);

INSERT INTO employee VALUES (8, "Sravani", "Nidamanuri", 987654);

INSERT INTO employee VALUES (9, "Saranya", "Amara", 1987654);

CREATE TABLE employee_info(

  id int PRIMARY KEY,

  city VARCHAR(20),

  state VARCHAR(20),

  country VARCHAR(20)

);

INSERT INTO employee_info VALUES (1, "Bangalore", "Karnataka", "India");

INSERT INTO employee_info VALUES (2, "Chirala", "Andhra Pradesh", "India");

INSERT INTO employee_info VALUES (3, "Punuru", "Andhra Pradesh", "India");

INSERT INTO employee_info VALUES (4, "Bangalore", "Karnataka", "India");

INSERT INTO employee_info VALUES (5, "Bangalore", "Karnataka", "India");

INSERT INTO employee_info VALUES (6, "Nellore", "Andhra Pradesh", "India");

INSERT INTO employee_info VALUES (7, "Chennai", "Tamilnadu", "India");

INSERT INTO employee_info VALUES (17, "Bhuvaneswar", "Orissa", "India");

INSERT INTO employee_info VALUES (27, "Mumbai", "Maharastra", "India");

INSERT INTO employee_info VALUES (37, "Delhi", "Delhi", "India");

Let me get all employees and addresses using JOIN clause.

SELECT *

FROM employee AS emp

JOIN employee_info AS empInfo

ON emp.id = empInfo.id;

mysql> SELECT *
    -> FROM employee AS emp
    -> JOIN employee_info AS empInfo
    -> ON emp.id = empInfo.id;
+----+--------------+----------+------------+----+-----------+----------------+---------+
| id | firstName    | lastName | salary     | id | city      | state          | country |
+----+--------------+----------+------------+----+-----------+----------------+---------+
|  1 | Hari Krishna | Gurram   |   12345.67 |  1 | Bangalore | Karnataka      | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  4 | Bangalore | Karnataka      | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  5 | Bangalore | Karnataka      | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  7 | Chennai   | Tamilnadu      | India   |
+----+--------------+----------+------------+----+-----------+----------------+---------+
7 rows in set (0.00 sec)

As you see, it don’t give information about employee ids 17, 27, and 37, it is because they don’t have any records associated with these ids. If you want to get the information for ids 17, 27 and 37 you should use RIGHT JOIN.

mysql> SELECT *
    -> FROM employee AS emp
    -> RIGHT JOIN employee_info AS empInfo
    -> ON emp.id = empInfo.id;
+------+--------------+----------+------------+----+-------------+----------------+---------+
| id   | firstName    | lastName | salary     | id | city        | state          | country |
+------+--------------+----------+------------+----+-------------+----------------+---------+
|    1 | Hari Krishna | Gurram   |   12345.67 |  1 | Bangalore   | Karnataka      | India   |
|    2 | Rama Devi    | Gurram   | 1234578.67 |  2 | Chirala     | Andhra Pradesh | India   |
|    3 | Lakshmana    | Rao      | 9876543.67 |  3 | Punuru      | Andhra Pradesh | India   |
|    4 | Rama         | Krishna  | 1234587.67 |  4 | Bangalore   | Karnataka      | India   |
|    5 | Sowmya       | asd      | 1238745.67 |  5 | Bangalore   | Karnataka      | India   |
|    6 | Jyotsna      | PS       |   76543.67 |  6 | Nellore     | Andhra Pradesh | India   |
|    7 | Gireesh      | Amara    |   87698.00 |  7 | Chennai     | Tamilnadu      | India   |
| NULL | NULL         | NULL     |       NULL | 17 | Bhuvaneswar | Orissa         | India   |
| NULL | NULL         | NULL     |       NULL | 27 | Mumbai      | Maharastra     | India   |
| NULL | NULL         | NULL     |       NULL | 37 | Delhi       | Delhi          | India   |
+------+--------------+----------+------------+----+-------------+----------------+---------+
10 rows in set (0.00 sec)

Previous                                                 Next                                                 Home