what are the joins in dbms? Define its types with examples

In Database Management Systems (DBMS), joins are used to combine rows from two or more tables based on a related column between them. Joins facilitate querying and retrieving data in a relational database. Here are the main types of joins, along with definitions and examples:

### 1. **INNER JOIN**
An INNER JOIN returns only the rows that have matching values in both tables.

**Example:**
Consider two tables, `Employees` and `Departments`.

**Employees Table:**
| EmployeeID | Name     | DepartmentID |
|------------|----------|--------------|
| 1          | Alice    | 10           |
| 2          | Bob      | 20           |
| 3          | Charlie  | 10           |

**Departments Table:**
| DepartmentID | DepartmentName |
|--------------|----------------|
| 10           | HR             |
| 20           | Finance        |
| 30           | IT             |

**Query:**
```sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
```

**Result:**
| Name    | DepartmentName |
|---------|----------------|
| Alice   | HR             |
| Bob     | Finance        |
| Charlie | HR             |

### 2. **LEFT JOIN (or LEFT OUTER JOIN)**
A LEFT JOIN returns all the rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

**Query:**
```sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
```

**Result:**
| Name    | DepartmentName |
|---------|----------------|
| Alice   | HR             |
| Bob     | Finance        |
| Charlie | HR             |

### 3. **RIGHT JOIN (or RIGHT OUTER JOIN)**
A RIGHT JOIN returns all the rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

**Query:**
```sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
```

**Result:**
| Name    | DepartmentName |
|---------|----------------|
| Alice   | HR             |
| Bob     | Finance        |
| Charlie | HR             |
| NULL    | IT             |

### 4. **FULL JOIN (or FULL OUTER JOIN)**
A FULL JOIN returns all rows when there is a match in either left or right table records. It returns NULL where there is no match.

**Query:**
```sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
```

**Result:**
| Name    | DepartmentName |
|---------|----------------|
| Alice   | HR             |
| Bob     | Finance        |
| Charlie | HR             |
| NULL    | IT             |

### 5. **CROSS JOIN**
A CROSS JOIN returns the Cartesian product of the two tables. It combines all rows from the first table with all rows from the second table.

**Query:**
```sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;
```

**Result:**
| Name    | DepartmentName |
|---------|----------------|
| Alice   | HR             |
| Alice   | Finance        |
| Alice   | IT             |
| Bob     | HR             |
| Bob     | Finance        |
| Bob     | IT             |
| Charlie | HR             |
| Charlie | Finance        |
| Charlie | IT             |

### 6. **SELF JOIN**
A SELF JOIN is a regular join but the table is joined with itself.

**Example:**
```sql
SELECT A.Name AS EmployeeName, B.Name AS ManagerName
FROM Employees A, Employees B
WHERE A.ManagerID = B.EmployeeID;
```

In this query, `Employees` is joined with itself to find out which employee works under which manager.

### Summary
- **INNER JOIN**: Returns rows with matching values in both tables.
- **LEFT JOIN**: Returns all rows from the left table and matched rows from the right table.
- **RIGHT JOIN**: Returns all rows from the right table and matched rows from the left table.
- **FULL JOIN**: Returns all rows when there is a match in either table.
- **CROSS JOIN**: Returns the Cartesian product of the two tables.
- **SELF JOIN**: Joins a table with itself.

Each type of join serves different purposes in querying relational databases and helps in effective data retrieval.