PostgreSQL - SELF JOIN

A SELF JOIN in PostgreSQL (or any other SQL database) is a regular join that relates a table to itself. It's not a built-in feature per se, but a technique wherein you join a table with itself to find related records in the same table.

A common use case for a SELF JOIN is when you have hierarchical data in a table, such as when an employee's manager is also listed as an employee in the same table.

Example:

Consider an employees table structured as follows:

| id | name       | manager_id |
|----|------------|------------|
| 1  | Anna      | NULL       |
| 2  | Bob       | 1          |
| 3  | Charlie   | 1          |
| 4  | David     | 2          |

In this table, Anna is a manager with no manager herself. Bob and Charlie report to Anna, while David reports to Bob.

To get a list of employees and their managers, you could use a SELF JOIN:

SELECT e1.name AS employee_name, 
       e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

The result would look like:

| employee_name | manager_name |
|---------------|--------------|
| Anna          | NULL         |
| Bob           | Anna         |
| Charlie       | Anna         |
| David         | Bob          |

Points to Remember:

In a SELF JOIN, aliases are crucial. Without using aliases (e.g., e1, e2), it would be impossible to distinguish between the two uses of the employees table.
In the example above, a LEFT JOIN was used to include employees who might not have a manager (like Anna). This ensures that all employees are listed, even if they don't have a manager.
The SELF JOIN concept isn't limited to hierarchical data. Any time you need to compare rows within the same table or find relations within the same table, a SELF JOIN can be a solution.

In essence, a SELF JOIN in PostgreSQL allows you to create a relation between two instances of the same table. It's a powerful tool when working with hierarchical or comparative data within a single table.

How to perform a SELF JOIN in PostgreSQL: A self-join is a regular join, but it involves joining a table with itself.
```
SELECT t1.column, t2.column
FROM your_table t1
INNER JOIN your_table t2 ON t1.common_column = t2.common_column;
```

Using aliases in SELF JOINs in PostgreSQL:

SELECT e1.employee_name AS employee1, e2.employee_name AS employee2
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

SELF JOIN with specific columns in PostgreSQL:

SELECT t1.column1, t1.column2, t2.column3
FROM your_table t1
INNER JOIN your_table t2 ON t1.common_column = t2.common_column;

Handling NULL values in SELF JOINs in PostgreSQL:

SELECT t1.column, t2.column
FROM your_table t1
INNER JOIN your_table t2 ON t1.common_column = t2.common_column
WHERE t1.column IS NOT NULL AND t2.column IS NOT NULL;

Filtering results with WHERE clause in SELF JOINs in PostgreSQL:

SELECT t1.column1, t2.column2
FROM your_table t1
INNER JOIN your_table t2 ON t1.common_column = t2.common_column
WHERE t1.condition = 'value' AND t2.condition = 'value';

Aggregating data in SELF JOINs in PostgreSQL:

SELECT t1.category, AVG(t2.value)
FROM your_table t1
INNER JOIN your_table t2 ON t1.common_column = t2.common_column
GROUP BY t1.category;

SELF JOIN and multiple conditions in PostgreSQL:

SELECT t1.column1, t2.column2
FROM your_table t1
INNER JOIN your_table t2 ON t1.common_column1 = t2.common_column1 AND t1.common_column2 = t2.common_column2;

Using INNER JOIN for SELF JOIN in PostgreSQL: Both INNER JOIN and SELF JOIN can be used interchangeably.
LEFT JOIN vs. INNER JOIN for SELF JOIN in PostgreSQL: Use LEFT JOIN to include unmatched rows from the left table.
SELF JOIN and recursive queries in PostgreSQL: Recursive queries involve a table joining with itself through a specific relationship.

SELF JOIN with ORDER BY in PostgreSQL:

SELECT t1.column, t2.column
FROM your_table t1
INNER JOIN your_table t2 ON t1.common_column = t2.common_column
ORDER BY t1.column;

SELF JOIN and LIMIT/OFFSET in PostgreSQL:

SELECT t1.column, t2.column
FROM your_table t1
INNER JOIN your_table t2 ON t1.common_column = t2.common_column
LIMIT 10 OFFSET 5;

SELF JOIN with GROUP BY and HAVING in PostgreSQL:

SELECT t1.category, AVG(t2.value)
FROM your_table t1
INNER JOIN your_table t2 ON t1.common_column = t2.common_column
GROUP BY t1.category
HAVING AVG(t2.value) > 50;

SELF JOIN and subqueries in PostgreSQL:

SELECT t1.column, (SELECT MAX(t2.value) FROM your_table t2 WHERE t2.common_column = t1.common_column)
FROM your_table t1;

Handling circular references with SELF JOIN in PostgreSQL: Use appropriate conditions in the join to avoid infinite loops.