Join Statements

Introduction

In this section, you will learn about several types of Join statements.

Objectives

You will be able to:

• Compare and contrast the various types of joins
• Understand the structure of Join statements, and the role of foreign and primary keys in them

CRM Schema

In almost all cases, rather then just working with a single table we will typically need data from multiple tables. Doing this requires the use of **joins ** using shared columns from the two tables. For example, here's a diagram of a mock customer relation management database.

Connecting to the Database

import sqlite3
import pandas as pd

conn = sqlite3.connect('data.sqlite', detect_types=sqlite3.PARSE_COLNAMES)
cur = conn.cursor()

Displaying product details along with order details

Let's say we need to generate some report that includes details about products from orders. To do that, we would need to take data from multiple tables in a single statement.

cur.execute("""select * from orderdetails
                        join products
                        on orderdetails.productCode = products.productCode
                        limit 10;
                       """)
df = pd.DataFrame(cur.fetchall()) #Take results and create dataframe
df.columns = [i[0] for i in cur.description]
df.head()

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Compared to the individual tables:

orderdetails

cur.execute("""select * from orderdetails limit 10;""")
df = pd.DataFrame(cur.fetchall()) #Take results and create dataframe
df.columns = [i[0] for i in cur.description]
df.head()

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

products

cur.execute("""select * from products limit 10;""")
df = pd.DataFrame(cur.fetchall()) #Take results and create dataframe
df.columns = [i[0] for i in cur.description]
df.head()

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

the using clause

A more concise way to join the tables if the column name is identical is the using clause.

cur.execute("""select * from orderdetails
                        join products
                        using(productCode)
                        limit 10;
                       """)
df = pd.DataFrame(cur.fetchall()) #Take results and create dataframe
df.columns = [i[0] for i in cur.description]
df.head()

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Aliasing

Alternatively, you can also alias tables by giving them an alternative shorthand name directly after them. Here we use the aliases 'o' and 'p' for orderdetails and products respectively.

cur.execute("""select * from orderdetails o
                        join products p
                        on o.productCode = p.productCode
                        limit 10;
                       """)
df = pd.DataFrame(cur.fetchall()) #Take results and create dataframe
df.columns = [i[0] for i in cur.description]
df.head()

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Left Joins

Above, we have only been doing inner joins which is the intersection of the two tables. There are many other types of joins, displayed below. Of these, sqlite does not support outer joins, but it is good to be aware of as more powerful versions of sql such as postgresql support these additional functions.

For example, the statement

select * from products left join orderdetails;

would return all products, even those that hadn't been ordered. We can imagine that all products in inventory should have a description in the product table, but perhaps not every product is represented in the orderdetails table.

cur.execute("""select * from products
                        left join orderdetails
                        using(productCode);
                       """)
df = pd.DataFrame(cur.fetchall()) #Take results and create dataframe
df.columns = [i[0] for i in cur.description]
print(len(df))
print(len(df[df.orderNumber.isnull()]))
df[df.orderNumber.isnull()].head()

2997
1

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

As you can see, its rare, but there is one product that has yet to be ordered

Primary Versus Foreign Keys

Another important consideration when performing joins is to think more about the key or column you are joining on. As we'll see in upcoming lessons, this can lead to interesting behavior if the join value is not unique in one or both of the tables. In all of the above examples, we joined two tables using the primary key. The primary key(s) of a table are those column(s) which uniquely identify a row. You'll also see this designated in our schema diagram with the asterisk (*).

We can also join tables using foreign keys which are not the primary key for that particular table, but rather another table. For example, employeeNumber is the primary key for the employees table and corresponds to the salesRepEmployeeNumber of the customers table. In the customers table, salesRepEmployeeNumber is only a foreign key, and is unlikely to be a unique identifier, as it is likely that an employee serves multiple customers. As such, in the resulting view, employeeNumber would no longer be a unique field.

cur.execute("""select * from customers c
                        join employees e
                        on c.salesRepEmployeeNumber = e.employeeNumber
                        order by employeeNumber;
                       """)
df = pd.DataFrame(cur.fetchall()) #Take results and create dataframe
df.columns = [i[0] for i in cur.description]
df.head()

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Notice that this also returned both columns: salesRepEmployeeNumber and employeeNumber.

Summary

In this lesson we investigated joins including the on and using clause, aliasing table names, left joins and primary and foreign keys.