70.112
Retrieving Data from Multiple Tables Using Various Joins

Introduction

Joins are an essential element of most queries that involve data that is stored in multiple tables. This tutorial shows the difference between the three most common types of joins: inner, natural, anti, and outer. Be sure to follow along in R.

The video tutorial below provides an overview and the rest of the lesson goes a bit deeper using a sample database. Watch the video first and follow along with the lesson. Of course, if you prefer to read and follow along with actual code, then you can skip the video tutorial and go straight to the lesson below.

Slide Deck for Video: SQL Primer - Joins

Before proceeding with the remainder of the tutorial, download the R Notebook for this tutorial and open it in R Studio.

Sample Database

The code fragments below create a small database useful for demonstrating the join queries. This SQLite database is created in memory rather than on disk. The database contains two tables: empl representing employees in some organization and office which tracks offices and their locations. The schema is defined as follows:

• empl (eid, name, oid)
• office (oid, num)

library(RSQLite)
dbcon <- dbConnect(RSQLite::SQLite(), ":memory:")

Turn off support for foreign key constraint checking, so we can add an unmatched row in a table for explanatory purposes. This might be done in practice during the loading of external data from a CSV or XML file.

PRAGMA foreign_keys = OFF

Create Tables

create table office (
 oid integer primary key,
 num text
);

create table empl (
 eid integer primary key,
 name text,
 oid integer,
 foreign key (oid) references office(oid)
);

Add Sample Data

Notice that some employees do not have an office and that some office are unoccupied. This will be important later when we demonstrate outer joins.

insert into office values 
 (10,"NI 132F"),
 (20,"WVH 310A"),
 (30,"RY 611"),
 (40,"CH 103"),
 (50,"106A");

insert into empl values 
 (601,"Jeff Goldblum",10),
 (602,"Ann Hathaway",20),
 (603,"Michael Keaton",30),
 (604,"Jennifer Hudson",NULL),
 (605,"Mark Wahlberg",44),
 (609,"Helen Miren", 50);

Show Table Contents

select * from office;

Table 1: 5 records

oid	num
10	NI 132F
20	WVH 310A
30	RY 611
40	CH 103
50	106A

select * from empl;

Table 2: 6 records

eid	name	oid
601	Jeff Goldblum	10
602	Ann Hathaway	20
603	Michael Keaton	30
604	Jennifer Hudson	NA
605	Mark Wahlberg	44
609	Helen Miren	50

Cartesian Product

When a query contains two or more tables in the FROM clause, SQL produces the Cartesian Product of the two tables, i.e., all combinations of rows from all tables. In the example below, if table empl has n rows and table office has m rows, then the Cartesian Product has n × m rows. Note that the foreign key (FK) and primary key (PK) are the same only for a few of the rows – these are the rows that are actually related and must be selected. This is done by an inner join where the FK and PK values are matched.

select *
  from empl, office;

Table 3: Displaying records 1 - 10

eid	name	oid	oid	num
601	Jeff Goldblum	10	10	NI 132F
601	Jeff Goldblum	10	20	WVH 310A
601	Jeff Goldblum	10	30	RY 611
601	Jeff Goldblum	10	40	CH 103
601	Jeff Goldblum	10	50	106A
602	Ann Hathaway	20	10	NI 132F
602	Ann Hathaway	20	20	WVH 310A
602	Ann Hathaway	20	30	RY 611
602	Ann Hathaway	20	40	CH 103
602	Ann Hathaway	20	50	106A

Note that the oid value from the empl table only matches some of the oid values of the office table; those are the ones where there is actually a link. For example, “Jeff Goldblum” is actually assigned to office 10, but he is shown with all rows of the office table. The diagram below makes that more clear.

PK/FK Matches

So, what we want to do is select the rows where the value in the foreign key column of one table (oid from the empl table in the example above) is the same as the primary key value of the related table (oid from the office table in the example above). This is the essence of an inner join.

Inner Join

An inner join is the most common form of join: it results in rows from two tables where the PK in one table matches the FK in the other table. There are four different ways to express an inner join. All forms are equivalent and there’s no difference in performance.

Standard WHERE Clause with FK/PK Matching

In this classic approach, the primary key/foreign key match is explicitly expressed in the where clause of the query. While simple and common, it is not always apparent to a novice that an inner join is being performed.

select *
  from empl as e, office as o
 where e.oid = o.oid;

Table 4: 4 records

eid	name	oid	oid	num
601	Jeff Goldblum	10	10	NI 132F
602	Ann Hathaway	20	20	WVH 310A
603	Michael Keaton	30	30	RY 611
609	Helen Miren	50	50	106A

Join with a selection:

select e.name as EmployeeName, o.num as OfficeNumber
  from empl e, office o
 where e.oid = o.oid;

Table 5: 4 records

EmployeeName	OfficeNumber
Jeff Goldblum	NI 132F
Ann Hathaway	WVH 310A
Michael Keaton	RY 611
Helen Miren	106A

The keyword “as” is optional but good practice to add as it makes the code’s intent clearer.

select e.name as EmployeeName, o.num as OfficeNumber
  from empl as e, office as o
 where e.oid = o.oid;

Table 6: 4 records

EmployeeName	OfficeNumber
Jeff Goldblum	NI 132F
Ann Hathaway	WVH 310A
Michael Keaton	RY 611
Helen Miren	106A

INNER JOIN Syntax

This syntax makes it clear that an inner join occurs and what the matching key fields are. It is the preferred way to express an inner join.

select *
  from empl e inner join office o on (e.oid = o.oid);

Table 7: 4 records

eid	name	oid	oid	num
601	Jeff Goldblum	10	10	NI 132F
602	Ann Hathaway	20	20	WVH 310A
603	Michael Keaton	30	30	RY 611
609	Helen Miren	50	50	106A

And, again, with a projection:

select e.name, o.num
  from empl e inner join office o on (e.oid = o.oid);

Table 8: 4 records

name	num
Jeff Goldblum	NI 132F
Ann Hathaway	WVH 310A
Michael Keaton	RY 611
Helen Miren	106A

The example below removes the inner keyword.

select e.name, o.num
  from empl e join office o on (e.oid = o.oid);

Table 9: 4 records

name	num
Jeff Goldblum	NI 132F
Ann Hathaway	WVH 310A
Michael Keaton	RY 611
Helen Miren	106A

The aforementioned inner join syntax separates the join criteria from any other selection criteria in the where clause.

select e.name, o.num
  from empl e join office o on (e.oid = o.oid)
 where o.num like 'NI%';

Table 10: 1 records

name	num
Jeff Goldblum	NI 132F

Of course, it can also be combined with an ORDER BY clause:

select e.name, o.num
  from empl e join office o on (e.oid = o.oid)
  order by e.name asc, o.oid desc;

Table 11: 4 records

name	num
Ann Hathaway	WVH 310A
Helen Miren	106A
Jeff Goldblum	NI 132F
Michael Keaton	RY 611

Natural Join

A natural join is an inner join where the database matches two tables based on common column names. The intent is that PK/FK columns would be the only ones that are named the same. Of course, this is not always true and could result is some pretty wrong queries when two joined tables happen to have to columns with the same name but that are not PK or FK columns intended to link the table. Imagine if the num column on the office table were named name instead. Then the natural join below would only select rows where the oid columns match and where the name columns match. Of course, the name of an office and the name of an employee are two completely different attributes and will never match.

select *
  from empl natural join office;

Table 12: 4 records

eid	name	oid	num
601	Jeff Goldblum	10	NI 132F
602	Ann Hathaway	20	WVH 310A
603	Michael Keaton	30	RY 611
609	Helen Miren	50	106A

Consider the following update to the database where some future database architect adds a name column to the office table so that ‘nicknames’ can be added to offices and meeting rooms. Since not all offices have nicknames, the column allows null values.

As an aside, one way to keep schema independence and confine changes to SQL statements to very few query updates is to use views.

alter table office
  add column name text;

Let’s add a couple of nicknames to some offices.

update office
   set name = 'Fishbowl'
where oid = 40;

Now let’s run the natural join query again. Recall that a natural join matches on common column names. Why are the suddenly no results? Simple: a natural join matches on common values between two table based on common column names.

select *
  from empl natural join office;

Because the two tables empl and office have oid and (now) name in common, the above query is actually equivalent to:

select *
  from empl e, office o
 where e.oid = o.oid
   and e.name = o.name;

So, you are looking for all offices where the office id in empl is equal to the office id in office which makes sense. But, you also only want the offices whose name is the same as the name of the employee – which makes no sense, of course.

So, be careful with natural joins. They might be convenient but can lead to unexpected (and nonsensical) behavior and query results.

Joining Multiple Tables

Joining multiple tables is accomplished by joining pairs of tables. In a join the tables must be somehow connect. They do not all have to be connected to each other, but there has to be a path from every table to every other table, perhaps through another table.

Let’s say that we expand the database with another table. One that tracks the campus and address for each office. So, we’ll need a new table campus in order to keep the database normalized.

create table campus (
 cid text primary key,
 name text not null,
 city text not null,
 state text not null,
 country text not null
);

We will also need to add another column to the office table to track the campus on which an office is located.

alter table office
  add column cid text;

Now, let’s add a few campus locations and then update the offices for their campus location.

insert into campus values 
 ('BOS', 'Main Campus Boston', 'Boston', 'MA', 'USA'),
 ('SV', 'Silicon Valley', 'San Jose', 'CA', 'USA'),
 ('TOR', 'Toronto', 'Toronto', 'ON', 'Canada'),
 ('VAN', 'Vancouver', 'Vancouver', 'BC', 'Canada'),
 ('NCH', 'New College of Humanities', 'London', '', 'UK'),
 ('CLT', 'Charlotte', 'Charlotte', 'NC', 'USA'),
 ('SF', 'San Francisco', 'San Francisco', 'CA', 'USA'),
 ('OL', 'Online', 'Online', '', ''),
 ('BUR', 'Costa Research Center', 'Burlington', 'MA', 'USA'),
 ('NAH', 'Marine Research Center', 'Nahant', 'MA', 'USA'),
 ('RXI', 'Roux Institute', 'Portland', 'ME', 'USA'),
 ('DC', 'Cyber Security Institute', 'Washington', 'VA', 'USA'),
 ('SEA', 'Seattle', 'Seattle', 'WA', 'USA');

update office
   set cid = 'BOS'
where oid IN (10,20,30,40);

update office
   set cid = 'SEA'
where oid IN (50);

So, now that we have three tables, let’s find all employees, their office, and their campus name. Once again, we can accomplish this task with either an explicit join clause or using pair-wise join statements. Both approaches are shown below.

select e.name, o.num, c.name
  from empl e, office o, campus c
 where e.oid = o.oid and o.cid = c.cid;

Table 13: 4 records

name	num	name
Jeff Goldblum	NI 132F	Main Campus Boston
Ann Hathaway	WVH 310A	Main Campus Boston
Michael Keaton	RY 611	Main Campus Boston
Helen Miren	106A	Seattle

select e.name, o.num, c.name
  from empl e join office o on (e.oid = o.oid) join campus c on (o.cid = c.cid);

Table 14: 4 records

name	num	name
Jeff Goldblum	NI 132F	Main Campus Boston
Ann Hathaway	WVH 310A	Main Campus Boston
Michael Keaton	RY 611	Main Campus Boston
Helen Miren	106A	Seattle

Note that the order in which the pair-wise joins are done doesn’t matter as the database query plan does a full Cartesian product first and then selects based on the join clauses.

Outer Joins

There are three flavors of outer joins: left, right, and full. While in an inner join, you get only matching rows, the various outer joins also add unmatched rows. The visual below shows the differences between the joins.

Joins Explained Visually

Left Outer Join

A left outer join select all rows in common between two tables, i.e., those linked by a PK/FK relationship, plus all unmatched rows from the left table in the join specification.

select *
  from empl e left join office o on (e.oid = o.oid)

Table 15: 6 records

eid	name	oid	oid	num	name	cid
601	Jeff Goldblum	10	10	NI 132F	NA	BOS
602	Ann Hathaway	20	20	WVH 310A	NA	BOS
603	Michael Keaton	30	30	RY 611	NA	BOS
604	Jennifer Hudson	NA	NA	NA	NA	NA
605	Mark Wahlberg	44	NA	NA	NA	NA
609	Helen Miren	50	50	106A	NA	SEA

The example below removes the matched rows to only show unmatched rows. This can be useful to find those rows where there is a referential integrity issue that may have gone undetected, i.e., there’s an FK that doesn’t have a matching PK in the related table. While this should not happen, it could happen when low-quality data is imported.

select *
  from empl e left join office o on (e.oid = o.oid)
except
select *
  from empl e inner join office o on (e.oid = o.oid);

Table 16: 2 records

eid	name	oid	oid	num	name	cid
604	Jennifer Hudson	NA	NA	NA	NA	NA
605	Mark Wahlberg	44	NA	NA	NA	NA

The query below shows only those rows where there’s a missing FK; it is similar to above, although it finds only those employees who do not have an office, while a left outer join would also show employees who have been assigned an office that does not exist.

select *
  from empl
 where empl.oid is null;

Table 17: 1 records

eid	name	oid
604	Jennifer Hudson	NA

Find Unmatched Foreign Keys

To find all of the unmatched foreign keys, i.e., where a foreign key has a value that does not correspond to a primary key, use the query below. This can be useful to detect FK/PK mismatches that may have occurred during a bulk loading of the database when referential integrity checking may have been temporarily suspended for performance reasons. Naturally, it is important to find such mismatches as this might otherwise influence queries and analytics results.

select e.name, e.oid
  from empl e left join office o on (e.oid = o.oid)
except
select e.name, e.oid
  from empl e inner join office o on (e.oid = o.oid)
except
select e.name, e.oid
  from empl e
 where e.oid is null;

Table 18: 1 records

name	oid
Mark Wahlberg	44

Right Outer Join

A right outer join is the same as a left outer join except that the unmatched rows come from the table on the right in the join specification. SQLite does not support an explicit right outer join; it only support left outer join. But, one needs to simply reverse the tables from a left outer join to get a right outer join.

select *
  from office o left join empl e on (e.oid = o.oid)

Table 19: 5 records

oid	num	name	cid	eid	name	oid
10	NI 132F	NA	BOS	601	Jeff Goldblum	10
20	WVH 310A	NA	BOS	602	Ann Hathaway	20
30	RY 611	NA	BOS	603	Michael Keaton	30
40	CH 103	Fishbowl	BOS	NA	NA	NA
50	106A	NA	SEA	609	Helen Miren	50

Full Outer Join

A full outer join is simply the union of a left outer join and a right outer join. It shows all matching rows (i.e., an inner join), plus unmatched rows from the right a left table. SQLite does not support a full outer join directly. However, it is simply the union of a left and right outer join.

But for this to work you need to specify the order of the columns; using * does not work as the two queries return the columns in a different order and the data is mangled – it does not return an error, it just doesn’t return a meaningful result. Note how the first column lists both office names and employee names – which is nonsense.

select *
  from office o left join empl e on (e.oid = o.oid)
union
select *
  from empl e left join office o on (e.oid = o.oid)

Table 20: Displaying records 1 - 10

oid	num	name	cid	eid	name	oid
10	NI 132F	NA	BOS	601	Jeff Goldblum	10
20	WVH 310A	NA	BOS	602	Ann Hathaway	20
30	RY 611	NA	BOS	603	Michael Keaton	30
40	CH 103	Fishbowl	BOS	NA	NA	NA
50	106A	NA	SEA	609	Helen Miren	50
601	Jeff Goldblum	10	10	0	NA	0
602	Ann Hathaway	20	20	0	NA	0
603	Michael Keaton	30	30	0	NA	0
604	Jennifer Hudson	NA	NA	NA	NA	NA
605	Mark Wahlberg	44	NA	NA	NA	NA

Here is the corrected version that explicitly specifies the column order:

select e.name, o.num
  from office o left join empl e on (e.oid = o.oid)
union
select e.name, o.num
  from empl e left join office o on (e.oid = o.oid)

Table 21: 7 records

name	num
NA	CH 103
Ann Hathaway	WVH 310A
Helen Miren	106A
Jeff Goldblum	NI 132F
Jennifer Hudson	NA
Mark Wahlberg	NA
Michael Keaton	RY 611

Anti-Join

In an anti-join we want to find all rows that are in one table but not the other. For example, we might want to find all office which are not occupied, i.e., in our schema from this tutorial, all offices in the office table which are not linked to from the empl table.

To find all the rows which are in office that are not in empl we first do a left join on the tables and then filter out those where the FK is NULL.

select o.oid, o.name, o.cid 
  from office as o
  left join empl as e on (o.oid = e.oid)
 where e.oid is null;

Table 22: 1 records

oid	name	cid
40	Fishbowl	BOS

Self Joins

Self joins (or, reflexive joins) in SQL are a unique type of join where a table is joined with itself. This might sound a bit unusual at first, but self joins are quite useful in certain scenarios.

1. Same Table, Different Aliases: In a self join, the same table appears twice in the query, but it’s represented by different aliases. This allows you to compare rows within the same table.

2. Syntax: It follows the same syntax as a regular join. The difference lies in that both the left and right side of the join are the same table, just with different aliases.

   SELECT A.column_name, B.column_name
   FROM table_name AS A
   JOIN table_name AS B
   ON A.common_field = B.common_field;

3. Types of Joins: You can use any type of join (INNER, LEFT, RIGHT, FULL) as a self join, depending on the requirement.

Use Cases for Self Joins

1. Hierarchical Data: Useful in scenarios where the table has a hierarchical structure. For example, an employee table where each employee has a manager, and both employees and managers are in the same table.

2. Comparisons within a Table: To compare rows within the same table. For instance, finding pairs of customers who live in the same city.

3. Time-based Analysis: In tables where you have time-series data, you might want to compare a row with its preceding or succeeding row.

4. Detecting Duplicates: To find duplicate entries based on certain criteria without using GROUP BY.

5. Path Finding: In network or graph-based data, to find paths or connections between nodes stored in a single table.

Example

Imagine an Employees table with columns EmployeeID, Name, and ManagerID, where ManagerID is also an EmployeeID in the same table. To list each employee with their manager’s name, you’d use a self join:

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees AS E1
JOIN Employees AS E2 ON E1.ManagerID = E2.EmployeeID;

In this query, E1 and E2 are aliases for the same Employees table. E1 represents the employees, and E2 represents their managers.

Demonstration

In this advanced code walk and demonstration, Khoury Boston’s Dr. Dan Feinberg provides use cases for self joins and shows how to build them in MySQL, although the same syntax and principles apply to most other databases, including SQLite.

Conclusion

Self joins are a powerful tool in SQL, especially for handling hierarchical data, complex comparisons, and data analysis within the same table. Understanding when and how to use them can greatly enhance the efficiency and capability of your SQL queries.

Summary

This tutorial explained the several common types of joins: inner, natural, outer, anti, and reflexive. It did omit two types of (uncommon) joins though: the cross join and the theta join.

---

Files & Resources

All Files for Lesson 70.112

---

References

No references.

Errata

Let us know.