Online College Courses for Credit

+
Right Joins

Right Joins

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify the correct data that is returned from a RIGHT OUTER JOIN between two tables.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the RIGHT JOIN and RIGHT OUTER JOIN to query data from tables.

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;

File:11419-2160-1.png

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:

File:11420-2160-2.png

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;

File:11421-2160-3.png

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

File:11422-2160-4.png


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.

summary
The right join and right outer join will query data from two tables and return the matching data as well as the non-matching data from the right table.