WHAT IS SQL JOINS

Introduction

Imagine walking into a car dealership and asking the manager:

“Which customers bought which cars, how much did they spend, and which salesperson handled each sale?”

The information needed to answer this question might already exist in the company's database — but there is a catch.

It is not necessarily stored in one place.

Customer information might be stored in a customers table:

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu

Information about sales might be stored separately in a sales table:

sale_id	customer_id	car_id	amount
S001	101	C10	3,500,000
S002	103	C15	2,800,000
S003	102	C12	4,100,000

And information about the cars might exist in yet another table:

car_id	make	model
C10	Toyota	Hilux
C12	Ford	Ranger
C15	Mazda	CX-5

Each table tells us something useful, but none of them can answer our original question on its own.

The customers table knows who the customers are.

The sales table knows what transactions occurred.

The cars table knows which vehicles were sold.

What connects these pieces of information?

SQL JOINs.

A JOIN allows us to combine related information stored across different tables by identifying columns that connect those tables.

In our example, customer_id connects customers to their sales:

customers                         sales
─────────                         ─────
customer_id ───────────────────► customer_id

Similarly, car_id connects a sale to information about the vehicle:

sales                             cars
─────                             ────
car_id ─────────────────────────► car_id

Once these relationships are understood, SQL can bring the information together and allow us to answer questions such as:

• Which customer purchased each vehicle?
• Which cars generated the most revenue?
• Which customers have never made a purchase?
• Which vehicles have never been sold?
• How much has each customer spent?

This is one of the reasons JOINs are such an important SQL skill. Real-world databases rarely store everything in one giant table. Instead, data is often separated into related tables, and analysts need to know how to bring the right information together.

In this article, we will explore the major types of SQL JOINs — INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN — using practical examples to understand not only how they work, but when and why we use them.

Let's connect the tables.

Before We Join: Understanding Keys and Table Relationships

Before writing our first SQL JOIN, we need to understand one important question:

How does SQL know which rows from one table should be connected to rows in another table?

The answer lies in keys.

Primary Keys

A primary key is a column, or combination of columns, that uniquely identifies each record in a table.

Consider our customers table:

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu

Here, customer_id is the primary key.

Each customer has a unique ID:

James Mwangi  → 101
Faith Chebet  → 102
Mary Atieno   → 103

Even if two customers happen to have the same name, their customer_id values should still be different.

A table can be created with a primary key like this:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    county VARCHAR(50)
);

The PRIMARY KEY constraint tells the database that customer_id should uniquely identify each customer.

---

Foreign Keys

A foreign key is a column in one table that references a primary key, or another unique key, in another table.

Consider our sales table:

sale_id	customer_id	car_id	amount
S001	101	C10	3,500,000
S002	103	C15	2,800,000
S003	102	C12	4,100,000

The customer_id column appears here again.

But this time, it is being used to identify which customer made each purchase.

We can visualize the connection like this:

CUSTOMERS                         SALES

customer_id                      customer_id
───────────                      ───────────
101 James Mwangi  ────────────► 101
102 Faith Chebet  ────────────► 102
103 Mary Atieno   ────────────► 103

In the customers table:

customer_id = Primary Key

In the sales table:

customer_id = Foreign Key

This relationship allows us to connect customer information with sales information.

---

Understanding the Relationship

One customer can make several purchases.

For example:

Customers

customer_id	customer_name
101	James Mwangi
102	Faith Chebet

Sales

sale_id	customer_id	amount
S001	101	3,500,000
S002	101	2,200,000
S003	102	4,100,000

Notice that customer_id 101 appears twice in the sales table.

That is perfectly acceptable because James has made two purchases.

The relationship can therefore be described as:

CUSTOMERS                     SALES

    ONE                        MANY

     101  ────────────────┬── S001
                          │
                          └── S002

This is known as a one-to-many relationship.

One customer can be associated with many sales, while each sale in this example belongs to one customer.

---

The JOIN Condition

Now that we know how the tables are related, we can tell SQL how to connect them.

This is done using the ON condition.

For example:

SELECT *
FROM customers
INNER JOIN sales
    ON customers.customer_id = sales.customer_id;

The most important part for now is:

ON customers.customer_id = sales.customer_id

We are essentially telling SQL:

Match a customer with a sale whenever their customer_id values are equal.

SQL compares the values:

customers.customer_id       sales.customer_id

        101        =               101       ✓
        102        =               102       ✓
        103        =               103       ✓

Once SQL finds the matching values, it can combine information from both tables.

---

Why Not Join Using Customer Names?

You might wonder why we cannot simply write:

ON customers.customer_name = sales.customer_name

Names are not always reliable identifiers.

Two customers could have the same name, names may be misspelled, or a customer's name could change.

IDs are generally more reliable for establishing relationships between tables.

That is why relational databases commonly use primary and foreign keys to connect related records.

---

Our Database Relationship

For the examples in this article, we will work with three tables:

CUSTOMERS
──────────────
customer_id (PK)
customer_name
county
       │
       │ customer_id
       ▼
SALES
──────────────
sale_id (PK)
customer_id (FK)
car_id (FK)
amount
       │
       │ car_id
       ▼
CARS
──────────────
car_id (PK)
make
model

PK represents a Primary Key, while FK represents a Foreign Key.

Now that we understand how the tables are connected, we are ready to start joining them.

The first and perhaps most fundamental type of JOIN we will explore is the INNER JOIN.

1. INNER JOIN — Finding Matching Records

Now that we understand how tables are connected using keys, let's look at our first type of JOIN: the INNER JOIN.

An INNER JOIN returns only the records that have matching values in both tables.

In simple terms:

If a record does not have a match in both tables, it will not appear in the result.

Let's look at an example.

Our Customers Table

Suppose we have the following customers:

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu
104	Brian Otieno	Mombasa

Our Sales Table

Now consider the sales records:

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000

Notice something important.

Customers 101, 102, and 103 have corresponding records in the sales table.

However, customer 104, Brian Otieno, does not have a sales record.

If we use an INNER JOIN, SQL will return only customers who have matching sales records.

INNER JOIN Syntax

We can join the two tables using:

```sql id="e78n4k"
SELECT
customers.customer_name,
customers.county,
sales.sale_id,
sales.amount
FROM customers
INNER JOIN sales
ON customers.customer_id = sales.customer_id;

The most important part is:



```sql id="6a3c2f"
ON customers.customer_id = sales.customer_id

This tells SQL to compare the customer_id column in the customers table with the customer_id column in the sales table.

Conceptually, SQL is looking for matches:

```text id="z4ub62"
CUSTOMERS SALES

101 James Mwangi ────────────── 101 S001 ✓
102 Faith Chebet ────────────── 102 S003 ✓
103 Mary Atieno ────────────── 103 S002 ✓
104 Brian Otieno No match ✗

Because Brian does not have a matching record in `sales`, he will not appear in the result.

### Result

The query produces:

| customer_name | county  | sale_id |    amount |
| ------------- | ------- | ------- | --------: |
| James Mwangi  | Nairobi | S001    | 3,500,000 |
| Faith Chebet  | Nakuru  | S003    | 4,100,000 |
| Mary Atieno   | Kisumu  | S002    | 2,800,000 |

Only records with matches in **both tables** have been returned.

---

### Using Table Aliases

Writing the complete table names repeatedly can make SQL queries unnecessarily long.

Instead of:



```sql id="8m4qu7"
SELECT
    customers.customer_name,
    customers.county,
    sales.sale_id,
    sales.amount
FROM customers
INNER JOIN sales
    ON customers.customer_id = sales.customer_id;

we can assign shorter names called aliases:

```sql id="qk7m9p"
SELECT
c.customer_name,
c.county,
s.sale_id,
s.amount
FROM customers AS c
INNER JOIN sales AS s
ON c.customer_id = s.customer_id;

Here:



```text id="5o8by4"
c → customers
s → sales

The query performs exactly the same operation, but it is shorter and easier to read.

Aliases become particularly useful when joining several tables.

---

Joining More Than Two Tables

What if we also want to know which car each customer purchased?

Our sales table contains a car_id, which connects it to the cars table.

We can therefore join all three tables:

```sql id="6v8q3f"
SELECT
c.customer_name,
c.county,
ca.make,
ca.model,
s.amount
FROM sales AS s
INNER JOIN customers AS c
ON s.customer_id = c.customer_id
INNER JOIN cars AS ca
ON s.car_id = ca.car_id;

Now SQL follows the relationships:



```text id="79om1b"
CUSTOMERS
    │
    │ customer_id
    ▼
  SALES
    │
    │ car_id
    ▼
   CARS

The result could look like:

customer_name	county	make	model	amount
James Mwangi	Nairobi	Toyota	Hilux	3,500,000
Faith Chebet	Nakuru	Ford	Ranger	4,100,000
Mary Atieno	Kisumu	Mazda	CX-5	2,800,000

We can now answer a much more meaningful business question:

Which customer purchased which car, and how much did they spend?

This demonstrates the real power of SQL JOINs. Information that was separated across multiple tables can now be analysed together.

---

When Would You Use an INNER JOIN?

An INNER JOIN is useful when you are interested only in records that have corresponding information in both tables.

For example:

• Customers who have made purchases
• Employees assigned to departments
• Products that have been ordered
• Students enrolled in courses
• Drivers who have completed trips

A simple way to remember it is:

INNER JOIN = Give me the matches.

But what happens if we want to see all customers, including those who have never purchased anything?

An INNER JOIN cannot give us that complete picture.

For that, we need our next JOIN: the LEFT JOIN.

2. LEFT JOIN — Keeping Everything from the Left Table

In the previous section, we saw that an INNER JOIN returns only records that have matching values in both tables.

But sometimes we want a different answer.

Suppose management asks:

“Show me all our customers, including those who have never made a purchase.”

An INNER JOIN would not work because customers without a matching sales record would be excluded.

This is where a LEFT JOIN becomes useful.

A LEFT JOIN returns:

• All records from the left table
• Matching records from the right table
• NULL where a record from the left table has no corresponding match in the right table

A simple way to remember this is:

LEFT JOIN = Keep everything on the left and bring matching information from the right.

Our Two Tables

Let's continue using the customers and sales tables.

Customers

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu
104	Brian Otieno	Mombasa

Sales

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000

Notice again that Brian Otieno (customer_id = 104) does not have a corresponding record in the sales table.

With an INNER JOIN, Brian was excluded.

Let's see what happens with a LEFT JOIN.

LEFT JOIN Syntax

```sql id="w5w92z"
SELECT
c.customer_id,
c.customer_name,
c.county,
s.sale_id,
s.amount
FROM customers AS c
LEFT JOIN sales AS s
ON c.customer_id = s.customer_id;

Here, `customers` is the **left table** because it appears before `LEFT JOIN`.



```text id="mmx2r8"
customers                     sales
   LEFT                       RIGHT
     │                          │
     └──────── LEFT JOIN ───────┘

SQL keeps every record from customers and then looks for matching sales information.

Conceptually:

```text id="77dtb4"
CUSTOMERS SALES

101 James Mwangi ────────────── 101 S001 ✓
102 Faith Chebet ────────────── 102 S003 ✓
103 Mary Atieno ────────────── 103 S002 ✓
104 Brian Otieno No match

The important difference is that Brian is **not removed** from the result.

### Result

| customer_id | customer_name | county  | sale_id |    amount |
| ----------: | ------------- | ------- | ------- | --------: |
|         101 | James Mwangi  | Nairobi | S001    | 3,500,000 |
|         102 | Faith Chebet  | Nakuru  | S003    | 4,100,000 |
|         103 | Mary Atieno   | Kisumu  | S002    | 2,800,000 |
|         104 | Brian Otieno  | Mombasa | NULL    |      NULL |

Because Brian has no matching sale, SQL cannot provide a `sale_id` or `amount`.

Instead, those values appear as:



```text id="0n3m8g"
NULL

In SQL, NULL represents a missing or unknown value.

It is not the same as:

```text id="cf1e11"
0

and it is not the same as an empty string.

In this example, the `NULL` values tell us something useful: **Brian has no matching sales record.**

---

## Finding Customers Who Have Never Purchased

LEFT JOIN becomes especially useful when we combine it with `IS NULL`.

Suppose management asks:

> **“Which customers have never made a purchase?”**

We can write:



```sql id="xxo5ox"
SELECT
    c.customer_id,
    c.customer_name,
    c.county
FROM customers AS c
LEFT JOIN sales AS s
    ON c.customer_id = s.customer_id
WHERE s.customer_id IS NULL;

The result would be:

customer_id	customer_name	county
104	Brian Otieno	Mombasa

This technique is extremely useful in real-world analysis.

For example, we could use it to find:

• Customers who have never purchased
• Products that have never been sold
• Employees who have not been assigned to a department
• Vehicles that have never completed a trip
• Students who have not registered for a course

---

What Happens When One Customer Has Multiple Sales?

Suppose James makes another purchase.

Our sales table becomes:

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000
S004	101	2,000,000

When we run our LEFT JOIN, James appears twice:

customer_name	sale_id	amount
James Mwangi	S001	3,500,000
James Mwangi	S004	2,000,000
Faith Chebet	S003	4,100,000
Mary Atieno	S002	2,800,000
Brian Otieno	NULL	NULL

This is not SQL accidentally creating a duplicate.

James has two matching records in the sales table, so SQL returns one result row for each match.

This is an important concept when working with one-to-many relationships.

---

INNER JOIN vs LEFT JOIN

We can now clearly see the difference between the first two JOINs.

INNER JOIN	LEFT JOIN
Returns matching records only	Returns all left-table records
Unmatched left records disappear	Unmatched left records remain
Useful when only matches matter	Useful when missing matches also matter

Consider Brian:

```text id="97cx92"
INNER JOIN LEFT JOIN

Brian Otieno ✗ ✓
Sales record ✗ NULL

So if the question is:

**“Which customers have purchased something?”**

an `INNER JOIN` may be appropriate.

But if the question is:

**“Show me every customer and their purchases, if they have any,”**

a `LEFT JOIN` is more appropriate.

---

## A Common LEFT JOIN Mistake

Suppose we want all customers but only sales above KSh 3,000,000.

Consider:



```sql id="47e0f3"
SELECT
    c.customer_name,
    s.amount
FROM customers AS c
LEFT JOIN sales AS s
    ON c.customer_id = s.customer_id
WHERE s.amount > 3000000;

The WHERE condition removes rows where s.amount is NULL. As a result, customers without matching sales disappear, which can make the result behave more like an INNER JOIN for this condition.

If our intention is to retain all customers while matching only sales above KSh 3,000,000, we can place the condition inside the ON clause:

```sql id="8oy89a"
SELECT
c.customer_name,
s.amount
FROM customers AS c
LEFT JOIN sales AS s
ON c.customer_id = s.customer_id
AND s.amount > 3000000;

This preserves all customers from the left table while restricting which sales records qualify as matches.

Understanding where filtering happens is important when working with outer joins.

---

## When Should You Use a LEFT JOIN?

Use a `LEFT JOIN` when you want to keep **all records from your main table**, regardless of whether corresponding information exists in another table.

For a data analyst, LEFT JOIN is particularly useful when investigating **missing activity**.

For example:



```text id="6dq1k4"
All Customers
      ↓
LEFT JOIN
      ↓
Sales
      ↓
Customers with purchases
+
Customers without purchases

A simple rule to remember is:

INNER JOIN asks: “Which records match?”
LEFT JOIN asks: “Give me everything on the left, whether it matches or not.”

We have now seen what happens when we preserve the table on the left.

But what if we want to preserve every record from the table on the right instead?

That brings us to the RIGHT JOIN.

3. RIGHT JOIN — Keeping Everything from the Right Table

A RIGHT JOIN works similarly to a LEFT JOIN, but instead of keeping every record from the table on the left, it keeps every record from the table on the right.

A RIGHT JOIN returns:

• All records from the right table
• Matching records from the left table
• NULL values where a right-table record does not have a corresponding match in the left table

A simple way to remember it is:

RIGHT JOIN = Keep everything on the right and bring matching information from the left.

Let's continue with our customers and sales example.

Customers Table

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu
104	Brian Otieno	Mombasa

Sales Table

This time, imagine our sales table contains:

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000
S004	105	2,500,000

Notice something unusual.

The sale S004 references customer_id = 105, but customer 105 does not appear in our customers table.

This could represent a data-quality problem, imported historical data, or—in a database without an enforced foreign-key constraint—an unmatched record.

Let's see what happens when we use a RIGHT JOIN.

RIGHT JOIN Syntax

```sql id="7d4n23"
SELECT
c.customer_id,
c.customer_name,
c.county,
s.sale_id,
s.amount
FROM customers AS c
RIGHT JOIN sales AS s
ON c.customer_id = s.customer_id;

Here:



```text id="32j8fk"
customers                     sales
   LEFT                       RIGHT
     │                          │
     └──────── RIGHT JOIN ──────┘

Because sales is the table on the right, every sales record will be retained.

SQL looks for matching customers:

```text id="yk39du"
CUSTOMERS SALES

101 James Mwangi ────────────── 101 S001 ✓
102 Faith Chebet ────────────── 102 S003 ✓
103 Mary Atieno ────────────── 103 S002 ✓
105 S004 ✗

The sale for customer `105` does not have a matching customer record.

However, because we are using a `RIGHT JOIN`, the sale is still included.

### Result

| customer_id | customer_name | county  | sale_id |    amount |
| ----------: | ------------- | ------- | ------- | --------: |
|         101 | James Mwangi  | Nairobi | S001    | 3,500,000 |
|         103 | Mary Atieno   | Kisumu  | S002    | 2,800,000 |
|         102 | Faith Chebet  | Nakuru  | S003    | 4,100,000 |
|        NULL | NULL          | NULL    | S004    | 2,500,000 |

Notice the final row.

The sales information exists:



```text id="79hwha"
S004 → KSh 2,500,000

but SQL cannot find corresponding customer information.

Therefore:

```text id="i1a68m"
customer_name → NULL
county → NULL

---

## Finding Sales Without Matching Customers

This can be useful when checking data quality.

For example:



```sql id="dhkqz5"
SELECT
    s.sale_id,
    s.customer_id,
    s.amount
FROM customers AS c
RIGHT JOIN sales AS s
    ON c.customer_id = s.customer_id
WHERE c.customer_id IS NULL;

The result would be:

sale_id	customer_id	amount
S004	105	2,500,000

This tells us that sale S004 references a customer that cannot be found in the customers table.

As a data analyst, this could be something worth investigating before performing further analysis.

---

RIGHT JOIN vs LEFT JOIN

The main difference is simply which table must be preserved.

```text id="3ew49g"
LEFT JOIN

CUSTOMERS ← Keep everything
│
└──────── SALES

Compared with:



```text id="rrk6mm"
RIGHT JOIN

CUSTOMERS ────────┐
                  │
              SALES ← Keep everything

We can summarize them as:

LEFT JOIN	RIGHT JOIN
Keeps all rows from the left table	Keeps all rows from the right table
Matches information from the right	Matches information from the left
Missing right-side values become NULL	Missing left-side values become NULL

---

Can a RIGHT JOIN Be Written as a LEFT JOIN?

Yes.

This is an important observation.

Consider:

```sql id="tg0sgr"
SELECT *
FROM customers AS c
RIGHT JOIN sales AS s
ON c.customer_id = s.customer_id;

We could simply reverse the table order:



```sql id="32frst"
SELECT *
FROM sales AS s
LEFT JOIN customers AS c
    ON s.customer_id = c.customer_id;

Both queries express the same basic requirement:

Keep every sales record and bring in customer information where a matching customer exists.

Because of this, many SQL developers and analysts prefer LEFT JOIN for readability and consistency and simply change which table appears first.

Nevertheless, understanding RIGHT JOIN is important because you will encounter it in existing SQL queries and technical assessments.

---

When Should You Use a RIGHT JOIN?

A RIGHT JOIN can be useful when:

• Every record from the right table must be retained.
• You want to identify records on the right that have no corresponding match on the left.
• The structure of an existing query makes keeping the right table convenient.

A simple way to remember the first three joins is:

INNER JOIN: Keep the matches.
LEFT JOIN: Keep everything on the left.
RIGHT JOIN: Keep everything on the right.

But there is still one question left:

What if we want to keep everything from both tables — whether the records match or not?

For that, we use a FULL OUTER JOIN.

4. FULL OUTER JOIN — Keeping Everything from Both Tables

So far, we have seen that:

• INNER JOIN keeps only matching records.
• LEFT JOIN keeps all records from the left table.
• RIGHT JOIN keeps all records from the right table.

But what happens when we want to keep all records from both tables, regardless of whether they have a match?

This is where the FULL OUTER JOIN comes in.

A FULL OUTER JOIN returns:

• Records that match in both tables
• Unmatched records from the left table
• Unmatched records from the right table

Where no corresponding record exists, SQL returns NULL for the missing values.

A simple way to remember it is:

FULL OUTER JOIN = Give me everything from both tables and match what you can.

Our Customers Table

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu
104	Brian Otieno	Mombasa

Our Sales Table

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000
S004	105	2,500,000

There are two important unmatched records:

```text id="m7n3f8"
Customer 104 → No sale
Sale S004 → Customer 105 is not in customers

A `FULL OUTER JOIN` allows us to see both.

### FULL OUTER JOIN Syntax



```sql id="w4j2nv"
SELECT
    c.customer_id AS customer_record_id,
    c.customer_name,
    c.county,
    s.sale_id,
    s.customer_id AS sales_customer_id,
    s.amount
FROM customers AS c
FULL OUTER JOIN sales AS s
    ON c.customer_id = s.customer_id;

SQL first matches records where the customer_id exists in both tables:

```text id="zt28oc"
CUSTOMERS SALES

101 James Mwangi ────────────── 101 S001 ✓
102 Faith Chebet ────────────── 102 S003 ✓
103 Mary Atieno ────────────── 103 S002 ✓

104 Brian Otieno No match
105 S004
No match

Unlike an `INNER JOIN`, neither unmatched record is discarded.

### Result

| customer_record_id | customer_name | county  | sale_id | sales_customer_id |    amount |
| -----------------: | ------------- | ------- | ------- | ----------------: | --------: |
|                101 | James Mwangi  | Nairobi | S001    |               101 | 3,500,000 |
|                102 | Faith Chebet  | Nakuru  | S003    |               102 | 4,100,000 |
|                103 | Mary Atieno   | Kisumu  | S002    |               103 | 2,800,000 |
|                104 | Brian Otieno  | Mombasa | NULL    |              NULL |      NULL |
|               NULL | NULL          | NULL    | S004    |               105 | 2,500,000 |

The result gives us three different situations.

### 1. Matching Records

For customers `101`, `102`, and `103`, SQL found matching records in both tables.

Their customer and sales information therefore appears together.

### 2. Customer Without a Sale

Brian Otieno (`customer_id = 104`) exists in the `customers` table but has no corresponding sale.

His sales information therefore appears as:



```text id="9k1qxr"
sale_id → NULL
amount  → NULL

3. Sale Without a Customer

Sale S004 references customer_id = 105, but that customer cannot be found in the customers table.

Therefore, the customer information appears as NULL.

This makes FULL OUTER JOIN particularly useful when comparing datasets and looking for discrepancies.

---

Finding Only the Unmatched Records

Sometimes we are not interested in the matches at all.

Instead, we want to ask:

Which records exist in one table but do not have a corresponding record in the other?

We can combine FULL OUTER JOIN with a WHERE condition:

```sql id="h6v4np"
SELECT
c.customer_id,
c.customer_name,
s.sale_id,
s.customer_id AS sales_customer_id
FROM customers AS c
FULL OUTER JOIN sales AS s
ON c.customer_id = s.customer_id
WHERE c.customer_id IS NULL
OR s.customer_id IS NULL;

This would identify:



```text id="7d2u9b"
Brian Otieno → Customer without a sale
S004         → Sale without a matching customer

This type of query can be very useful during data validation, reconciliation, and data-quality checks.

---

Comparing the Four Main JOINs

We can now bring together everything we have learned.

JOIN	Matching Rows	Unmatched Left Rows	Unmatched Right Rows
INNER JOIN	✓	✗	✗
LEFT JOIN	✓	✓	✗
RIGHT JOIN	✓	✗	✓
FULL OUTER JOIN	✓	✓	✓

Another simple way to remember them is:

```text id="21a5hx"
INNER JOIN
→ Keep only matches

LEFT JOIN
→ Keep matches + everything from the left

RIGHT JOIN
→ Keep matches + everything from the right

FULL OUTER JOIN
→ Keep everything from both sides

The JOIN you choose should therefore depend on the **question you are trying to answer**, not simply on which syntax you remember.

For example:

**“Which customers have made purchases?”**

→ `INNER JOIN`

**“Show me all customers, including those who have never purchased.”**

→ `LEFT JOIN`

**“Keep every sales transaction, even if customer information is missing.”**

→ `RIGHT JOIN` — or equivalently restructure the query using a `LEFT JOIN`.

**“Show me all records from both datasets and highlight where information does not match.”**

→ `FULL OUTER JOIN`

Understanding these four JOINs gives you the foundation for combining related tables in SQL.

## Conclusion

SQL JOINs are an essential skill when working with relational databases because real-world data is often distributed across multiple related tables. Instead of storing customers, sales, products, employees, and other information in one large table, relational databases organize this information into separate tables that can be connected when needed.

In this article, we explored four important types of SQL JOINs:

* **INNER JOIN** — returns records that have matching values in both tables.
* **LEFT JOIN** — returns all records from the left table and matching records from the right table.
* **RIGHT JOIN** — returns all records from the right table and matching records from the left table.
* **FULL OUTER JOIN** — returns all records from both tables, whether they have a match or not.

The key to choosing the correct JOIN is to first understand the **question you are trying to answer**.

If you only need customers who have made purchases, an `INNER JOIN` may be appropriate. If you need all customers, including those who have never purchased anything, a `LEFT JOIN` would be more suitable. If you need to compare two datasets and identify both matching and unmatched records, a `FULL OUTER JOIN` can provide a more complete picture.

A simple way to remember the four JOINs is:



```text
INNER JOIN      → Keep the matches

LEFT JOIN       → Keep everything on the left

RIGHT JOIN      → Keep everything on the right

FULL OUTER JOIN → Keep everything from both sides

However, understanding JOIN syntax is only part of the skill. It is equally important to understand primary keys, foreign keys, table relationships, NULL values, and the level of detail in each table. These concepts help ensure that tables are joined correctly and that the results of an analysis are reliable.

For a data analyst, JOINs are particularly valuable because they allow data from different parts of a business to be brought together. A sales transaction can be connected to a customer, a customer to a location, a sale to a product, or an employee to a department. Once these connections are made, seemingly separate pieces of information can become meaningful business insights.

The best way to become comfortable with SQL JOINs is through practice. Create a few related tables, experiment with each JOIN, examine the results, and ask yourself why certain records appeared while others did not.

Ultimately, SQL JOINs are about one simple idea:

Connecting related pieces of data to reveal a more complete story.