Exploring PostgreSQL Joins: A Comprehensive Guide Using Sales Data and Product Information
Tags: postgres, sql, dba
Published: 2024-03-15 08:26:57

To demonstrate the various types of joins in PostgreSQL using the `sales_data` table from above, let's assume we have another table to join with. For the sake of this example, let's create a second table named `product_info` that contains additional information about each product. We'll then perform different types of joins between `sales_data` and `product_info` to illustrate how each join works.

### Step 1: Creating the Second Table (`product_info`)

First, we need to create a new table that contains product details. This table will have a product name (which will act as a foreign key linking to `sales_data`) and additional columns like `category` and `price`.

```sql
CREATE TABLE product_info (
    product_name VARCHAR(255) PRIMARY KEY,
    category VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

```

### Step 2: Inserting Data into `product_info`

Insert some sample data into `product_info`.

```sql
INSERT INTO product_info (product_name, category, price)
VALUES
('Widget A', 'Electronics', 19.99),
('Widget B', 'Hardware', 29.95),
('Widget C', 'Electronics', 19.90),
('Widget D', 'Decor', 45.00); -- Note: Widget D does not have sales in sales_data

```

### Step 3: Demonstrating Different Types of Joins

### INNER JOIN

Fetches rows that have matching values in both tables.

```sql
SELECT s.transaction_date, s.product_name, p.category, s.quantity_sold, s.sale_amount
FROM sales_data s
INNER JOIN product_info p ON s.product_name = p.product_name;

```

### LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table (`sales_data`), and the matched rows from the right table (`product_info`). The result is NULL from the right side if there is no match.

```sql
SELECT s.transaction_date, s.product_name, p.category, s.quantity_sold, s.sale_amount
FROM sales_data s
LEFT JOIN product_info p ON s.product_name = p.product_name;

```

### RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table (`product_info`), and the matched rows from the left table (`sales_data`). The result is NULL from the left side if there is no match.

```sql
SELECT s.transaction_date, s.product_name, p.category, s.quantity_sold, s.sale_amount
FROM sales_data s
RIGHT JOIN product_info p ON s.product_name = p.product_name;

```

### FULL JOIN (or FULL OUTER JOIN)

Combines the results of both LEFT JOIN and RIGHT JOIN. Returns rows when there is a match in one of the tables. Therefore, it returns all rows from both tables, with matching rows from both sides where available. If there is no match, the missing side will contain NULL.

```sql
SELECT s.transaction_date, s.product_name, p.category, s.quantity_sold, s.sale_amount
FROM sales_data s
FULL JOIN product_info p ON s.product_name = p.product_name;

```

### CROSS JOIN

Returns a Cartesian product of the two tables, i.e., it returns rows combining each row from the first table with each row from the second table.

```sql
SELECT s.transaction_date, s.product_name as sales_product_name, p.product_name as info_product_name, p.category
FROM sales_data s
CROSS JOIN product_info p;

```

### Additional Join: SELF JOIN

Though not specifically requested, a SELF JOIN could be used to compare rows within the same table. Since our example doesn't naturally lend itself to a SELF JOIN without a more complex scenario, we'll skip a specific example here. But typically, a SELF JOIN can be used for operations like finding duplicate entries or comparing rows within the same table based on certain conditions.

These SQL join types allow you to combine rows from two or more tables based on a related column between them, facilitating a wide range of data analysis and reporting tasks in PostgreSQL.


[![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sf8qszawuy835vxhpxrf.png)](https://minervadb.xyz/postgresql-consulting/)