SQL | Join (Cartesian Join & Self Join)

Let's delve into these specific types of SQL joins:

1. Cartesian Join (also known as CROSS JOIN):

A Cartesian Join, or CROSS JOIN, returns the Cartesian product of the sets of records from the two or more joined tables. This means that if table A has "x" rows and table B has "y" rows, a Cartesian join will result in x*y rows. Usually, this is not what you want because it combines each row of the first table with each row of the second table, which often doesn't make logical sense.

However, there are scenarios where it's useful, especially when combined with other operations.

Syntax:

SELECT * 
FROM table1 
CROSS JOIN table2;

Example:

Imagine you have two tables:

colors:
id | color
1 | Red
2 | Blue

sizes:
id | size
1 | Small
2 | Large

A CROSS JOIN between these tables will give:

id | color | id | size
1 | Red | 1 | Small
1 | Red | 2 | Large
2 | Blue | 1 | Small
2 | Blue | 2 | Large

2. Self Join:

A Self Join is used to join a table with itself. This is useful when the data related to one another is in the same table. For example, in an employees table, if each employee has a manager and the manager's ID is stored in the same table, you can use a self join to match each employee with their manager.

Syntax:

SELECT A.column, B.column, ...
FROM table A, table B
WHERE A.column = B.column;

Example:

Consider an employees table:

id | name | manager_id
1 | John | NULL
2 | Alice | 1
3 | Bob | 1

To find out who is the manager for each employee:

SELECT E.name AS Employee_Name, M.name AS Manager_Name
FROM employees E, employees M
WHERE E.manager_id = M.id;

This will return:

Employee_Name | Manager_Name
Alice | John
Bob | John

This output shows that both Alice and Bob have John as their manager.

Note: It's important to use table aliases (like A, B, E, M in the examples) to differentiate between the instances of the table in a self join.

Performing a Cartesian Join in SQL queries:
- Description: A Cartesian Join, also known as a Cartesian Product, combines every row from one table with every row from another table, resulting in a cross-product of the two tables.
- Example Code:
```
SELECT * FROM table1
CROSS JOIN table2;
```
How to use CROSS JOIN in SQL for Cartesian product:
- Description: CROSS JOIN is used to perform a Cartesian Product. It joins each row from the first table with every row from the second table, creating a Cartesian product.
- Example Code:
```
SELECT * FROM table1
CROSS JOIN table2;
```
Self Join vs Inner Join in SQL:
- Description: A Self Join involves joining a table with itself, while an Inner Join combines rows from two tables based on a common condition. Self Join is useful when working with hierarchical data.
- Example Code:
```
-- Inner Join
SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- Self Join
SELECT * FROM table1 t1
INNER JOIN table1 t2 ON t1.column = t2.column;
```