What is a SQL join?
A SQL join is a Structured Query Language (SQL) instruction to combine data from two sets of data (i.e. two tables). Before we dive into the details of a SQL join, let’s briefly discuss what SQL is, and why someone would want to perform a SQL join.
SQL is a special-purpose programming language designed for managing information in a relational database management system (RDBMS). The word relational here is key; it specifies that the database management system is organized in such a way that there are clear relations defined between different sets of data.
Typically, you need to extract, transform, and load data into your RDBMS before you’re able to manage it using SQL, which you can accomplish by using a tool like Stitch.
Want to learn about setting the data strategy for your organization?
Signup for a free 30-day course to learn what you need in order to succeed with data. We've worked with more than 800 companies of all sizes and helped them build their data infrastructure, run analytics, and make data-driven decisions. Learn how the data landscape has changed and what that means for your company.
We will never share your email
Relational Database Example
Imagine you’re running a store and would like to record information about your customers and their orders. By using a relational database, you can save this information as two tables that represent two distinct entities: customers and orders.
Customers
customer_id | first_name | last_name | address | city | state | zip | |
---|---|---|---|---|---|---|---|
1 | George | Washington | gwashington@usa.gov | 3200 Mt Vernon Hwy | Mount Vernon | VA | 22121 |
2 | John | Adams | jadams@usa.gov | 1250 Hancock St | Quincy | MA | 02169 |
3 | Thomas | Jefferson | tjefferson@usa.gov | 931 Thomas Jefferson Pkwy | Charlottesville | VA | 22902 |
4 | James | Madison | jmadison@usa.gov | 11350 Constitution Hwy | Orange | VA | 22960 |
5 | James | Monroe | jmonroe@usa.gov | 2050 James Monroe Pkwy | Charlottesville | VA | 22902 |
Here, information about each customer is stored in its own row, with columns specifying different bits of information, including their first name, last name, and email address. Additionally, we associate a unique customer number, or primary key, with each customer record.
Orders
order_id | order_date | amount | customer_id |
---|---|---|---|
1 | 07/04/1776 | $234.56 | 1 |
2 | 03/14/1760 | $78.50 | 3 |
3 | 05/23/1784 | $124.00 | 2 |
4 | 09/03/1790 | $65.50 | 3 |
5 | 07/21/1795 | $25.50 | 10 |
6 | 11/27/1787 | $14.40 | 9 |
Again, each row contains information about a specific order. Each order has its own unique identification key – order_id
for this table – assigned to it as well.
Relational Model
You’ve probably noticed that these two examples share similar information. You can see these simple relations diagrammed below:
Note that the orders table contains two keys: one for the order and one for the customer who placed that order. In scenarios when there are multiple keys in a table, the key that refers to the entity being described in that table is called the primary key (PK) and other key is called a foreign key (FK).
In our example, order_id
is a primary key in the orders table, while customer_id
is both a primary key in the customers table and a foreign key in the orders table. Primary and foreign keys are essential to describing relations between the tables, and in performing SQL joins.
SQL Join Example
Let’s say we want to find all orders placed by a particular customer. We can do this by joining the customers and orders tables together using the relationship established by the customer_id
key:
Here, we’re joining the two tables using the join
keyword, and specifying what key to use when joining the tables in the on customers.customer_id = orders.customer_id
line following the join statement. Here is the result of the above SQL query, which includes two orders placed by Thomas Jefferson (customer_id = 3):
order_id | order_date | order_amount |
---|---|---|
2 | 3/14/1760 | $78.50 |
4 | 9/03/1790 | $65.50 |
This particular join is an example of an “inner” join. Depending on the kind of analysis you’d like to perform, you may want to use a different method. There are actually a number of different ways to join the two tables together, depending on your application. The next section will explain inner, left, right, and full joins, and provide examples using the data tables used above.