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 to be the left table and the second table is considered to be the right table.
SELECT *
FROM representative
LEFT JOIN department ON representative.representative_id = department.manager_id;
The left join starts to select data from the left table. It compares the representative_id from the representative table 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 can 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 can see in the 4th row.
The Venn diagram of the left join looks like the following:
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;
The following Venn diagram illustrates the left outer join:
[MUSIC PLAYING] Here we'll discuss the use of the left, as well as the left outer join. So as part of the FULL OUTER JOIN in this case, it does provide us information with all the rows that match, as well as data that's not in the left table and data that's on the right table. However, we can just run it based on the left outer join to be able to identify the rows within the left table that don't exist or reference the right table by replacing FULL OUTER with just LEFT.
Now, if we run this, we're going to be presented with four rows. In this case here, we have the ones that match up. But we'll also have the row that doesn't match what's in the Department table. We can also do a left outer join completely, just to have that particular one, by adding on a WHERE clause and identifying, in this case here, which is going to be department.manager_id is null. So meaning that it's going to be empty, with no value.
And once we have that, we can identify, in this case here, this is one representative that doesn't link to any Department ID for the Manager ID.
Source: Authored by Vincent Tran