Right Joins

Similar to the left join and left outer join, there is a reversed version of the right join and right outer join. They work exactly in the same way but first start to get data from the right table and compares the right table to the left table. If the values are equal, the right join creates a new row that contains columns from both tables. If they are not equal, the right join will fill the column in the left table with NULL. The structure of the query looks like this:

SELECT <columnlist>
FROM <table1>
RIGHT JOIN <table2> ON <table1>.<table1column1> = <table2>.<table2column1>;

Let us revisit our data set with the representatives and departments again:

CREATE TABLE representative ( representative_id INT PRIMARY KEY, first_name VARCHAR (30) NOT NULL, last_name VARCHAR (30) NOT NULL );

CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR (100) NOT NULL, manager_id INT, constraint fk_manager FOREIGN KEY (manager_id) REFERENCES representative (representative_id) );

INSERT INTO representative (representative_id, first_name, last_name)
VALUES (1, 'Bob','Evans'), (2, 'Tango','Rushmore'), (3, 'Danika','Arkane'), (4, 'Mac','Anderson');

INSERT INTO department (department_id, department_name,manager_id)
VALUES (1, 'Sales', 1), (2, 'Marketing', 3), (3, 'IT', 4), (4, 'Finance', null), (5, 'Support', null);

Let us take a look at what that would look like with the RIGHT JOIN:

SELECT *
FROM representative
RIGHT JOIN department ON representative.representative_id = department.manager_id;

The query itself isn’t that different than the left join other than specifying RIGHT instead of LEFT. Notice that the last two rows are from the department table in which there is not a match with the representative table. The right join starts to select data from the right table. It compares the manager_id from the department table and compares it to the representative_id in the representative table. If those values are equal, the right join creates a row that contains the columns of both tables and adds the new row in the result set. You will see that being the case for the first three rows in the table. If the values are not equal, the right join also creates a row contain the columns from both of the tables but fills the columns on the left side (representative) with null values.

The Venn diagram looks like the following to demonstrate the right join:

In the following example, we can just retrieve the rows from the right table that do not match any of the rows in the left table.

SELECT *
FROM representative
RIGHT JOIN department ON representative.representative_id = department.manager_id
WHERE manager_id IS NULL;

The following Venn diagram illustrates what the right outer join would look like:

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.