Learn Data Science/

...

/

Mix and Match Data

Mix and Match Data

Learn how data scientists use SQL joins to combine relational data for deeper insights.

We'll cover the following...

Raw information rarely comes in one neat table when working as a data scientist. Instead, it’s distributed across multiple related tables—customers in one, transactions in another, products in a third. To uncover meaningful patterns, we need to connect the dots. That’s where SQL joins come in.

But before we join tables, we need to understand how they’re structured and related. We’ll start by creating tables, then move on to how they’re linked using primary and foreign keys, and finally, we’ll dive into the most essential SQL joins we’ll use to analyze real-world data.

Creating tables in SQL

In SQL, a table is like a dataset: rows represent records, and columns represent fields (attributes). As data scientists, we may not always create these tables ourselves, but understanding how they’re built helps us query them effectively.

Here’s a simple syntax to define a table:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);
Define a table

For example, a table of customers:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);
Example table

In this example, CustomerID is marked as a PRIMARY KEY, meaning it will uniquely identify each customer—this is essential when linking this table to others.

Understanding primary keys

A primary key uniquely identifies each row in a table. For example, no two customers should share the same CustomerID. The following are key characteristics:

  • Unique: No duplicates allowed.

  • Not null: Every row must have a value.

  • Stable: Should not change frequently.

In data science workflows, primary keys help ensure data integrity during joins, filters, and feature engineering.

Understanding foreign keys

Now, let’s talk about the foreign key. If the primary key is like a unique ID for each book in a library, then a foreign key is like a reference to a specific book in the account of a user who has borrowed it. It ...