# Joins on E-Commerce

#### **Setup: Create Tables**

We have three tables:

1. **Customers**: Stores customer details.
2. **Orders**: Stores order details.
3. **Products**: Stores product details.

```sql
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    product_id INT,
    quantity INT
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2)
);

```

Populate some data, 

```sql
INSERT INTO customers (customer_name) VALUES
('Alice'), ('Bob'), ('Charlie');

INSERT INTO products (product_name, price) VALUES
('Laptop', 1000.00),
('Smartphone', 500.00),
('Tablet', 300.00);

INSERT INTO orders (customer_id, product_id, quantity) VALUES
(1, 1, 2), 
(2, 2, 1), 
(3, NULL, 3); 



```

#### **1. INNER JOIN**

**Question:** List all customers who placed orders with the product name and quantity.

```sql
SELECT c.customer_name, p.product_name, o.quantity
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id;
```

<figure><img src="/files/chFta4ipKYLKNrBS1TCg" alt=""><figcaption></figcaption></figure>

#### **2. LEFT JOIN**

**Question:** Show all customers and their orders, including orders without a product.

```sql
SELECT c.customer_name, p.product_name, o.quantity
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN products p ON o.product_id = p.product_id;

```

<figure><img src="/files/tbdRT8WhZxl26D46dPrY" alt=""><figcaption></figcaption></figure>

#### **3. FULL OUTER JOIN**

**Question:** Show all customers, orders, and products, including those with no matches.

```sql
SELECT c.customer_name, o.order_id, p.product_name
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
FULL OUTER JOIN products p ON o.product_id = p.product_id;
```

<figure><img src="/files/N5aR7Fe9bcURdZMpqWn4" alt=""><figcaption></figcaption></figure>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://courses.parottasalna.com/database-engineering/joins-on-e-commerce.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.