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
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 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 that 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:

File:11377-2050-1.png

Individually, the data looks like the following:

File:11378-2050-2.png

File:11379-2050-3.png

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. However, 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 this 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 3 that match:

File:11380-2050-4.png

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

File:11381-2050-5.png

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:

File:11382-2050-6.png

File:11383-2050-7.png

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.

File:11384-2050-8.png


File:11385-2050-9.png

We could 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:

File:11386-2050-10.png

File:11387-2050-11.png

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:

File:11388-2050-12.png

File:11389-2050-13.png

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

File:11390-2050-14.png

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