> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.
# Understanding Joins in SQL
> Uniting Data: Understanding Joins in SQL Server
You’ve learned to [query](/sql/read-data-in-sql) a single table like a pro. But what if you want to combine data from multiple tables? That’s where **joins** come in. Joins allow you to connect related tables based on a common column, giving you the complete picture.
Let’s dive into **joins** step by step using a scenario and examples, sticking to SQL Server syntax for consistency.
### Scenario
Imagine your online store now has two tables:
**Products Table:**
| product\_id | product\_name | category | price | stock |
| ----------- | ----------------- | ----------- | ----- | ----- |
| 1 | Wireless Mouse | Electronics | 1200 | 25 |
| 2 | Yoga Mat | Fitness | 800 | 50 |
| 3 | Bluetooth Speaker | Electronics | 2500 | 15 |
| 4 | Water Bottle | Fitness | 300 | 100 |
| 5 | Smartwatch | Electronics | 5000 | 10 |
**Orders Table:**
| order\_id | product\_id | order\_date | quantity |
| --------- | ----------- | ----------- | -------- |
| 101 | 1 | 2025-01-01 | 2 |
| 102 | 2 | 2025-01-02 | 5 |
| 103 | 3 | 2025-01-02 | 1 |
| 104 | 5 | 2025-01-03 | 2 |
| 105 | 4 | 2025-01-03 | 10 |
We’ll use these tables to explore **joins**.
### What is a Join?
A **join** connects two or more tables by matching rows using a common column, typically a primary key in one table and a foreign key in the other.
### Types of Joins
SQL Server supports several types of joins:
1. **INNER JOIN**: Returns rows with matching values in both tables.
2. **LEFT JOIN (or LEFT OUTER JOIN)**: Returns all rows from the left table and matching rows from the right table. Missing matches are filled with `NULL`.
3. **RIGHT JOIN (or RIGHT OUTER JOIN)**: Returns all rows from the right table and matching rows from the left table. Missing matches are filled with `NULL`.
4. **FULL JOIN (or FULL OUTER JOIN)**: Returns all rows when there’s a match in either table. Missing values are filled with `NULL`.
5. **CROSS JOIN**: Returns the Cartesian product of both tables (every combination of rows).
Let’s explore these step by step.
### INNER JOIN: Matching Rows Only
Suppose you want to find out the products that have been ordered along with the quantity.
```sql theme={"system"}
SELECT p.product_name, o.quantity, o.order_date
FROM products p
INNER JOIN orders o
ON p.product_id = o.product_id;
```
**Result:**
| product\_name | quantity | order\_date |
| ----------------- | -------- | ----------- |
| Wireless Mouse | 2 | 2025-01-01 |
| Yoga Mat | 5 | 2025-01-02 |
| Bluetooth Speaker | 1 | 2025-01-02 |
| Smartwatch | 2 | 2025-01-03 |
| Water Bottle | 10 | 2025-01-03 |
Here, only rows with matching `product_id` values in both tables are returned.
### LEFT JOIN: Include All Rows from the Left Table
What if you want to see all products, even those that haven’t been ordered? Use a **LEFT JOIN**.
```sql theme={"system"}
SELECT p.product_name, o.quantity, o.order_date
FROM products p
LEFT JOIN orders o
ON p.product_id = o.product_id;
```
**Result:**
| product\_name | quantity | order\_date |
| ----------------- | -------- | ----------- |
| Wireless Mouse | 2 | 2025-01-01 |
| Yoga Mat | 5 | 2025-01-02 |
| Bluetooth Speaker | 1 | 2025-01-02 |
| Water Bottle | 10 | 2025-01-03 |
| Smartwatch | 2 | 2025-01-03 |
This includes rows from `products` even if there’s no matching `product_id` in `orders`.
### RIGHT JOIN: Include All Rows from the Right Table
Similarly, if you want to see all orders, even if the products are missing in the `products` table, use a **RIGHT JOIN**.
```sql theme={"system"}
SELECT p.product_name, o.quantity, o.order_date
FROM products p
RIGHT JOIN orders o
ON p.product_id = o.product_id;
```
**Result:**
| product\_name | quantity | order\_date |
| ----------------- | -------- | ----------- |
| Wireless Mouse | 2 | 2025-01-01 |
| Yoga Mat | 5 | 2025-01-02 |
| Bluetooth Speaker | 1 | 2025-01-02 |
| Smartwatch | 2 | 2025-01-03 |
| Water Bottle | 10 | 2025-01-03 |
### FULL JOIN: All Rows from Both Tables
To get all rows from both tables, use a **FULL JOIN**.
```sql theme={"system"}
SELECT p.product_name, o.quantity, o.order_date
FROM products p
FULL JOIN orders o
ON p.product_id = o.product_id;
```
**Result:**
| product\_name | quantity | order\_date |
| ----------------- | -------- | ----------- |
| Wireless Mouse | 2 | 2025-01-01 |
| Yoga Mat | 5 | 2025-01-02 |
| Bluetooth Speaker | 1 | 2025-01-02 |
| Smartwatch | 2 | 2025-01-03 |
| Water Bottle | 10 | 2025-01-03 |
This will include rows that have no match in either table, filling in `NULL` values where necessary.
### CROSS JOIN: Every Combination
A **CROSS JOIN** produces a combination of every row in the first table with every row in the second table. We have 5 rows in products table and orders table. So we will get the result with 25 rows.
```sql theme={"system"}
SELECT p.product_name, o.order_date
FROM products p
CROSS JOIN orders o;
```
**Result (Truncated for brevity):**
| product\_name | order\_date |
| -------------- | ----------- |
| Wireless Mouse | 2025-01-01 |
| Wireless Mouse | 2025-01-02 |
| Wireless Mouse | 2025-01-03 |
| ... | |
Use **CROSS JOIN** sparingly, as the result grows exponentially.
### Summary
Joins are the cornerstone of working with relational databases. Whether you’re matching rows with **INNER JOIN**, including unmatched data with **LEFT JOIN** or **RIGHT JOIN**, or diving deep with **FULL JOIN**, mastering these concepts will unlock the full potential of SQL.
In the next article, we’ll cover [subqueries](/sql/subqueries-in-sql)—queries within queries—to extract even more nuanced insights from your data. Keep practicing!
[^1]: 5 \* 5 = 25 rows