In this section, you will learn about several types of JOIN statements. Joins are the primary mechanism for combining data from multiple tables. In order to do this, you define the common attribute(s) between tables in order for them to be combined.

You will be able to:

• Write SQL queries that make use of various types of joins
• Compare and contrast the various types of joins
• Discuss how primary and foreign keys are used in SQL
• Decide and perform whichever type of join is best for retrieving desired data

In almost all industry cases, rather than just working with a single table you will generally 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 relationship management (CRM) database.

As usual, you'll start by connecting to the database.

import sqlite3
import pandas as pd

conn = sqlite3.connect('data.sqlite')

Let's say you 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.

q = """
SELECT *
FROM orderdetails
JOIN products
    ON orderdetails.productCode = products.productCode
LIMIT 10
;
"""
pd.read_sql(q, conn)

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

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

pd.read_sql("""SELECT * FROM orderdetails LIMIT 10;""", conn)

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

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

pd.read_sql("""SELECT * FROM products LIMIT 10;""", conn)

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

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

A more concise way to join the tables, if the column name is identical, is the USING clause. Rather then saying on tableA.column = tableB.column we can simply say USING(column). Again, this only works if the column is identically named for both tables.

q = """
SELECT *
FROM orderdetails
JOIN products
    USING(productCode)
LIMIT 10
;
"""
pd.read_sql(q, conn)

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

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

You can also assign tables an alias by entering an alternative shorthand name. This is slightly different than the previous lesson where we introduced aliases for column names, since now we are aliasing tables.

When aliasing columns the goal is usually to improve readability by giving something a more specific or easier-to-read name. For example, name AS employee_name, AVG(AVG) AS average_batting_average, or COUNT(*) AS num_products.

When aliasing tables the goal is usually to shorten the name, in order to shorten the overall query. So typically you'll see examples that alias a longer table name to a one-character or two-character shorthand. For example, orderdetails AS od or products AS p. (It is also possible to use aliases to clarify what exactly is in a table, like how aliases are used for columns, just less common.)

The following query produces the same result as the previous ones, using aliases od and p for orderdetails and products, respectively:

q = """
SELECT *
FROM orderdetails AS od
JOIN products AS p
    ON od.productCode = p.productCode
LIMIT 10;
"""
pd.read_sql(q, conn)

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

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

Note that just like with column aliases, the AS keyword is optional in SQLite. So, instead of FROM orderdetails AS od you could write FROM orderdetails od with the same outcome.

It is somewhat more common to see AS used with column aliases and skipped with table aliases, but again, you'll want to check the syntax rules of your particular type of SQL as well as style guidelines from your employer to know which syntax to use in a professional setting.

By default a JOIN is an INNER JOIN, or the intersection between two tables. In other words, the JOIN between orders and products is only for productCodes that are in both the orderdetails and products tables. If a product had yet to be ordered (and wasn't in the orderdetails table) then it would also not be in the result of the JOIN.

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.

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

q = """
SELECT *
FROM products
LEFT JOIN orderdetails
    USING(productCode)
;
"""
df = pd.read_sql(q, conn)

print("Number of records returned:", len(df))
print("Number of records where order details are null:", len(df[df.orderNumber.isnull()]))

Number of records returned: 2997
Number of records where order details are null: 1

df[df.orderNumber.isnull()]

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

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

As you can see, it's a rare occurrence, but there is one product that has yet to be ordered.

Another important consideration when performing joins is to think more about the key or column you are joining on. As you'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, you 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 (*).

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

q = """
SELECT *
FROM customers AS c
JOIN employees AS e
    ON c.salesRepEmployeeNumber = e.employeeNumber
ORDER By employeeNumber
;
"""
pd.read_sql(q, conn)

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

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

Notice that this also returned both columns: salesRepEmployeeNumber and employeeNumber. These columns contain identical values so you would probably actually only want to select one or the other.

In this lesson, you investigated joins. This included implementing the ON and USING clauses, aliasing table names, implementing LEFT JOIN, and using primary vs. foreign keys.