> For the complete documentation index, see [llms.txt](https://courses.parottasalna.com/database-engineering/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://courses.parottasalna.com/database-engineering/joins-on-e-commerce.md).

# 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>