Online College Courses for Credit

+
Left Joins

Left Joins

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify the correct data that is returned from a LEFT 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 left join and left outer join.

In the prior tutorial, we covered the full outer join. We also have the ability to do a left join and a left outer join. The structure of the query looks like this:

SELECT <columnlist>
FROM <table1>
LEFT 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);

The left join clause is used to join the representative table with the department table. The first table listed in the FROM clause is considered the left table and the second table is considered the right table.

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

File:11415-2150-1.png

The left join starts to select data from the left table. It compares the representative_id from the representative table and compares it with the manager_id in the department table. If those values are equal, the left join creates a new row that contains the columns of both tables and adds the new row in the result set as you’ll see in the first three rows returned in the image above. If the values are not equal, the left join also creates a new row containing columns from both tables but fills in the columns of the right table (department) with a null value as you’ll see in the 4th row.

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

File:11416-2150-2.png


We can also do a left outer join where the left table does not have matching rows in the right table with an added WHERE clause to check where the manager_id is set to null. By doing so, this join does not return any rows that have matching data. The query would look like the following:

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

File:11417-2150-3.png

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

File:11418-2150-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 left join and left outer join selects rows from one table that may or may not have rows in the second table.