What are the different types of joins in SQL?

In SQL, joins are used to combine rows from two or more tables based on a related column between them. The different types of SQL joins are:

1. INNER JOIN

• Description: Returns rows that have matching values in both tables.
• Use case: When you want to retrieve only the records that have a match in both tables.
• Example:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Result: Only employees with a valid department will be returned.

2. LEFT JOIN (or LEFT OUTER JOIN)

• Description: Returns all rows from the left table, and the matching rows from the right table. If there’s no match, NULL values are returned for columns from the right table.
• Use case: When you want to keep all records from the left table, even if there’s no match in the right table.
• Example:

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

Result: All employees will be shown, and if an employee doesn’t belong to a department, NULL will appear for the department.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

• Description: Returns all rows from the right table, and the matching rows from the left table. If there’s no match, NULL values are returned for columns from the left table.
• Use case: When you want to keep all records from the right table, even if there’s no match in the left table.
• Example:

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Result: All departments will be shown, and if no employees belong to a department, NULL will appear for the employee.

4. FULL JOIN (or FULL OUTER JOIN)

• Description: Returns rows when there is a match in either left or right table. If there’s no match, NULL values will be returned for the missing side.
• Use case: When you want to keep all records from both tables, with NULL where there is no match.
• Example:

SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

Result: All employees and all departments will be shown, with NULL for any unmatched rows.

5. CROSS JOIN

• Description: Returns the Cartesian product of the two tables, i.e., it combines every row from the left table with every row from the right table.
• Use case: When you need every combination of rows from both tables.
• Example:

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

Result: Every employee is paired with every department.

6. SELF JOIN

• Description: A self join is a regular join, but the table is joined with itself.
• Use case: When you want to compare rows within the same table.
• Example:

SELECT a.name AS employee_name, b.name AS manager_name
FROM employees a
JOIN employees b ON a.manager_id = b.employee_id;

Result: Each employee will be listed along with their manager’s name (assuming the manager_id links to employee_id).

Quick Comparison:

Let me know if you need a more detailed example for any specific join!