Online College Courses for Credit

+
Joins

Joins

Rating:
(0)
Author: Sophia Tutorial
Description:

Use inner and outer joins to link table data together using common attributes.

(more)
See More
Tutorial

what's covered
This tutorial explores the needs to join tables together in various SQL statements in two parts:
  1. Inner Joins
  2. Outer Joins

1. Inner Joins

Up to this point, all the SQL statements we’ve been querying have been using a single table. However, when we can join the data in tables together is where databases really start to shine. Joins are done when you combine data from two or more tables, linking them through common attributes. There are many different types of joins including natural joins, equijoin, outer join, cross join and self joins that we will work through in upcoming tutorials.

Let us first create two tables for representatives and departments. The common attribute between the two tables is the representative_id from the representative table and the manager_id in the manager table. Notice that in this case, the two column names are different from one another but linked through the primary key.


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);

In looking at the data, we have Tango Rushmore who is not a manager, and the Finance and Support departments not having managers yet.

We can represent the data through the use of a Venn diagram where the left circle represents the data from the representative table and the right table contains the data from the department table:

Venn Diagram Graphic

Individually, the data looks like the following:

Query Result Example

Query Result Example

Note that because we do not have the same name for a column in the two tables, we cannot complete a natural join. However, this natural join works in the same way that an inner join does; the difference is that the column name is identified with the inner join. The basic inner join between the two tables should look like the following:


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

We will get into the specifics of the syntax in later tutorials. With this scenario of the inner join, we are finding the data in the representative_id in the representative table that matches the department’s manager_id. There should be three that match:

Query Result Example

The Venn diagram below illustrates the rest from the inner join:

Venn Diagram Graphic

This type of join is the most common type of join as typically we want to be able to identify where data matches between two or more tables.


2. Outer Joins

There are other joins as well including the OUTER join. The OUTER join allows us to get data that may not fully match between the two tables:

Venn Diagram Graphic

Query Result Example

We may want to use a left join to find data that exists in both tables but also data in the left table that does not match. This Venn diagram illustrates this left join.

Venn Diagram Graphic


Query Result Example

We may also want to find data that exists in the left table but does not match with data on the right table. This would be considered a left outer join:

Venn Diagram Graphic

Query Result Example

We can get the data that exists in both tables along with the data that does not match from the right table using a right join:

Venn Diagram Graphic

Query Result Example

We can also do the same to just have data in the right table only using a right outer join:

Venn Diagram Graphic

Query Result Example


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
We can use inner and outer joins to link table data together using common attributes.