RIGHT JOIN

A right join is the same a left join with the tables reversed. In fact, you will get the same results if you used and left join and change the order of the tables. The visual representation of the RIGHT table in a Venn diagram like this:

RIGHT JOIN in ACTION

Let's see how we can use the right join. Suppose we have the following two tables: employees and departments. We are interested in mappying out what state the employee is based on.

employees table

select employee_id, email,  department_id, phone from employees limit 10;

employee_id |              email              | department_id |  phone
------------+---------------------------------+---------------+----------
          1 | [email protected]        |             1 | 555-0101
          2 | [email protected]     |             2 | 555-0102
          3 | [email protected]     |             3 | 555-0103
          4 | [email protected]       |             4 |
          5 | [email protected]      |             1 | 555-0105
          6 | [email protected]     |             6 | 555-0106
          7 | [email protected]     |             3 | 555-0107
          8 | [email protected] |             2 | 555-0108
          9 | [email protected]      |             1 |
         10 | [email protected]  |             5 | 555-0110
(10 rows)

departments table

select * from departments;

department_id | department_name  |  budget   |  location  | manager_employee_id
--------------+------------------+-----------+------------+---------------------
            1 | Sales            | 500000.00 | New York   |
            2 | Marketing        | 350000.00 | California |
            3 | IT               | 750000.00 | Texas      |
            4 | HR               | 200000.00 | New York   |
            5 | Finance          | 300000.00 | Illinois   |
            6 | Customer Service | 180000.00 | Florida    |
            7 | Operations       | 400000.00 |            |
(7 rows)

To answer this question, we of course need to implement a join between the departments and employees table. We can use the department_id as the key

SELECT 
    a.employee_id, 
    a.email, 
    a.phone, 
    b.department_name, 
    b.budget, b.location 
FROM employees a 
RIGHT JOIN departments b 
ON a.department_id = b.department_id 
ORDER BY employee_id 
LIMIT 10;

employee_id |              email              |  phone   | department_name  |  budget   |  location
------------+---------------------------------+----------+------------------+-----------+------------
          1 | [email protected]        | 555-0101 | Sales            | 500000.00 | New York
          2 | [email protected]     | 555-0102 | Marketing        | 350000.00 | California
          3 | [email protected]     | 555-0103 | IT               | 750000.00 | Texas
          4 | [email protected]       |          | HR               | 200000.00 | New York
          5 | [email protected]      | 555-0105 | Sales            | 500000.00 | New York
          6 | [email protected]     | 555-0106 | Customer Service | 180000.00 | Florida
          7 | [email protected]     | 555-0107 | IT               | 750000.00 | Texas
          8 | [email protected] | 555-0108 | Marketing        | 350000.00 | California
          9 | [email protected]      |          | Sales            | 500000.00 | New York
         10 | [email protected]  | 555-0110 | Finance          | 300000.00 | Illinois
(10 rows)

Now we can determine which employee is based where and even more information such as budget and team they are on.