PostgreSQL FULL JOIN

PostgreSQL FULL JOIN Clause

The PostgreSQL FULL JOIN clause is used to combine rows from two or more tables, returning all rows when there is a match in either table. This clause is essential for retrieving all data from both tables, with NULL values for any unmatched rows from either table.

---

Syntax

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

The FULL JOIN clause has the following components:

• columns: The columns to be retrieved from the tables.
• table1: The first table to join.
• table2: The second table to join.
• table1.column = table2.column: The condition to join the tables, typically involving matching columns from each table.

---

Example PostgreSQL FULL JOIN Queries

Let's look at some examples of PostgreSQL FULL JOIN queries:

1. Basic FULL JOIN Example

SELECT customers.customer_name, orders.order_date
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id;

This query retrieves the customer_name from the customers table and the order_date from the orders table where there is a match between customers.customer_id and orders.customer_id. If there is no match, the result will still include the row from either the customers or orders table with NULL values for the columns from the other table.

2. FULL JOIN with Additional Condition

SELECT customers.customer_name, orders.order_date
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id AND orders.amount > 100;

This query retrieves the customer_name from the customers table and the order_date from the orders table where there is a match between customers.customer_id and orders.customer_id and the order amount is greater than 100.

3. FULL JOIN with Aliases

SELECT c.customer_name, o.order_date
FROM customers c
FULL JOIN orders o
ON c.customer_id = o.customer_id;

This query retrieves the customer_name from the customers table and the order_date from the orders table using table aliases c and o for simplicity.

4. FULL JOIN with Multiple Tables

SELECT c.customer_name, o.order_date, p.product_name
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id
FULL JOIN products p ON o.product_id = p.product_id;

This query retrieves the customer_name from the customers table, the order_date from the orders table, and the product_name from the products table where there is a match between c.customer_id and o.customer_id and between o.product_id and p.product_id. If there is no match, the result will still include the row from either table with NULL values for the columns from the unmatched table.

---

Full Example

Let's go through a complete example that includes creating tables, inserting data, and using the FULL JOIN clause to retrieve combined data.

Step 1: Creating Tables

This step involves creating new tables named customers, orders, and products to store data.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    amount NUMERIC(10, 2),
    customer_id INT,
    product_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100)
);

In this example, we create three tables: customers with columns for customer_id and customer_name, orders with columns for order_id, order_date, amount, customer_id, and product_id, and products with columns for product_id and product_name.

Step 2: Inserting Data into the Tables

This step involves inserting some sample data into the customers, orders, and products tables.

INSERT INTO customers (customer_name)
VALUES ('John Doe'), ('Jane Smith'), ('Jim Brown');

INSERT INTO products (product_name)
VALUES ('Product A'), ('Product B'), ('Product C');

INSERT INTO orders (order_date, amount, customer_id, product_id)
VALUES ('2024-01-01', 150, 1, 1),
       ('2024-02-15', 250, 2, 2),
       ('2024-03-10', 300, 1, 3);

Here, we insert data into the customers, products, and orders tables.

Step 3: Using the FULL JOIN Clause

This step involves using the FULL JOIN clause to retrieve combined data from the customers, orders, and products tables.

-- Basic FULL JOIN
SELECT customers.customer_name, orders.order_date
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id;

-- FULL JOIN with Additional Condition
SELECT customers.customer_name, orders.order_date
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id AND orders.amount > 100;

-- FULL JOIN with Aliases
SELECT c.customer_name, o.order_date
FROM customers c
FULL JOIN orders o
ON c.customer_id = o.customer_id;

-- FULL JOIN with Multiple Tables
SELECT c.customer_name, o.order_date, p.product_name
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id
FULL JOIN products p ON o.product_id = p.product_id;

These queries demonstrate how to use the FULL JOIN clause to retrieve combined data from the customers, orders, and products tables, including basic joins, joins with additional conditions, using table aliases, and joining multiple tables.

Conclusion

The PostgreSQL FULL JOIN clause is a fundamental tool for combining rows from two or more tables, ensuring that all data from both tables is included in the result set, even if there are no matches. Understanding how to use the FULL JOIN clause and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.