---
title: "Retrieving Data from Multiple Tables Using Various Joins"
params:
  category: 70
  number: 112
  time: 45
  level: beginner
  tags: "sql,joins,inner,outer,natural"
  description: "Explains inner, outer, natural, and anti joins in SQL."
date: "<small>`r Sys.Date()`</small>"
author: "<small>Martin Schedlbauer</small>"
email: "m.schedlbauer@neu.edu"
affilitation: "Northeastern University"
output: 
  bookdown::html_document2:
    toc: true
    toc_float: true
    collapsed: false
    number_sections: false
    code_download: true
    theme: spacelab
    highlight: tango
---

---
title: "<small>`r params$category`.`r params$number`</small><br/><span style='color: #2E4053; font-size: 0.9em'>`r rmarkdown::metadata$title`</span>"
---

```{r code=xfun::read_utf8(paste0(here::here(),'/R/_insert2DB.R')), include = FALSE}
```

## 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.

<iframe style="border: 1px solid #464646;" src="https://northeastern.hosted.panopto.com/Panopto/Pages/Embed.aspx?id=d5fce32d-7885-4e25-8f42-ac9900ec605e&amp;autoplay=false&amp;offerviewer=true&amp;showtitle=false&amp;showbrand=false&amp;start=0&amp;interactivity=all" width="480" height="270" allowfullscreen="allowfullscreen" allow="autoplay" data-external="1">

</iframe>

*Slide Deck for Video:* [SQL Primer - Joins](s-70-112-sql-primer-joins.pptx)

Before proceeding with the remainder of the tutorial, download the [R Notebook for this tutorial](l-70-112.Rmd) 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)

```{r}
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.

```{sql connection=dbcon}
PRAGMA foreign_keys = OFF
```

### Create Tables

```{sql connection=dbcon}
create table office (
 oid integer primary key,
 num text
);
```

```{sql connection=dbcon}
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.

```{sql connection=dbcon}
insert into office values 
 (10,"NI 132F"),
 (20,"WVH 310A"),
 (30,"RY 611"),
 (40,"CH 103"),
 (50,"106A");
```

```{sql connection=dbcon}
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

```{sql connection=dbcon}
select * from office;
```

```{sql connection=dbcon}
select * from empl;
```

## 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.

```{sql connection=dbcon}
select *
  from empl, office;
```

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](CartProd.png)

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.

```{sql connection=dbcon}
select *
  from empl as e, office as o
 where e.oid = o.oid;
```

Join with a selection:

```{sql connection=dbcon}
select e.name as EmployeeName, o.num as OfficeNumber
  from empl e, office o
 where e.oid = o.oid;
```

The keyword *"as"* is optional but good practice to add as it makes the code's intent clearer.

```{sql connection=dbcon}
select e.name as EmployeeName, o.num as OfficeNumber
  from empl as e, office as o
 where e.oid = o.oid;
```

### 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.

```{sql connection=dbcon}
select *
  from empl e inner join office o on (e.oid = o.oid);
```

And, again, with a projection:

```{sql connection=dbcon}
select e.name, o.num
  from empl e inner join office o on (e.oid = o.oid);
```

The example below removes the *inner* keyword.

```{sql connection=dbcon}
select e.name, o.num
  from empl e join office o on (e.oid = o.oid);
```

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

```{sql connection=dbcon}
select e.name, o.num
  from empl e join office o on (e.oid = o.oid)
 where o.num like 'NI%';
```

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

```{sql connection=dbcon}
select e.name, o.num
  from empl e join office o on (e.oid = o.oid)
  order by e.name asc, o.oid desc;
```

## 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.

```{sql connection=dbcon}
select *
  from empl natural join office;
```

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.

```{sql connection=dbcon}
alter table office
  add column name text;
```

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

```{sql connection=dbcon}
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.

```{sql connection=dbcon, eval=F}
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:

```{sql connection=dbcon, eval=F}
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.

```{sql connection=dbcon}
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.

```{sql connection=dbcon}
alter table office
  add column cid text;
```

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

```{sql connection=dbcon}
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');
```

```{sql connection=dbcon}
update office
   set cid = 'BOS'
where oid IN (10,20,30,40);
```

```{sql connection=dbcon}
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.

```{sql connection=dbcon}
select e.name, o.num, c.name
  from empl e, office o, campus c
 where e.oid = o.oid and o.cid = c.cid;
```

```{sql connection=dbcon}
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);
```

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](JoinsVisual.jpg)

### 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.

```{sql connection=dbcon}
select *
  from empl e left join office o on (e.oid = o.oid)
```

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.

```{sql connection=dbcon}
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);
```

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.

```{sql connection=dbcon}
select *
  from empl
 where empl.oid is null;
```

#### 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.

```{sql connection=dbcon}
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;
```

### 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.

```{sql connection=dbcon}
select *
  from office o left join empl e on (e.oid = o.oid)
```

### 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.

```{sql connection=dbcon}
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)
```

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

```{sql connection=dbcon}
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)
```

### 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.

```{sql anti-join, connection=dbcon}
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;
```

## 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.

    ``` sql
    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:

``` sql
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.

<iframe style="border: 1px solid #464646;" src="https://northeastern.hosted.panopto.com/Panopto/Pages/Embed.aspx?id=aa7d0c6b-77b5-4360-a730-ac520105ed19&amp;autoplay=false&amp;offerviewer=true&amp;showtitle=false&amp;showbrand=false&amp;start=0&amp;interactivity=all" width="480" height="270" allowfullscreen="allowfullscreen" allow="autoplay" data-external="1">

</iframe>

### 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

```{r zipFiles, echo=FALSE}
zipName = sprintf("LessonFiles-%s-%s.zip", 
                 params$category,
                 params$number)

textALink = paste0("All Files for Lesson ", 
               params$category,".",params$number)

# downloadFilesLink() is included from _insert2DB.R
knitr::raw_html(downloadFilesLink(".", zipName, textALink))
```

------------------------------------------------------------------------

## References

No references.

## Errata

[Let us know](https://form.jotform.com/212187072784157){target="_blank"}.

