SQL Joins

Joins are used to combine rows from two or more tables based on related columns. They are crucial for querying data across multiple tables in a relational database. Here’s an overview of the common types of joins:

---

1. INNER JOIN

• Definition: Combines rows from both tables where there is a match in the specified columns.
• Use Case: Retrieves only matching rows from both tables.
• Result: Only rows where employees.department_id matches departments.id.

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

---

2. LEFT JOIN (LEFT OUTER JOIN)

• Definition: Returns all rows from the left table and the matching rows from the right table. If no match is found, NULL is returned for columns from the right table.
• Use Case: Retrieves all data from one table, even if there’s no match in the other.
• Result: All employees are shown, with department_name as NULL for unmatched rows.

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

---

3. RIGHT JOIN (RIGHT OUTER JOIN)

• Definition: Returns all rows from the right table and the matching rows from the left table. If no match is found, NULL is returned for columns from the left table.
• Use Case: Retrieves all data from the second (right) table, even if there’s no match in the first.
• Result: All departments are shown, with employees.name as NULL for unmatched rows.

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

---

4. FULL JOIN (FULL OUTER JOIN)

• Definition: Returns rows when there is a match in either table. If there’s no match, NULL is returned for columns of the non-matching table.
• Use Case: Combines results of both LEFT JOIN and RIGHT JOIN.
• Result: All rows from both employees and departments, with NULLs for unmatched rows.

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

---

5. CROSS JOIN

• Definition: Produces the Cartesian product of two tables (all possible combinations of rows).
• Use Case: Rarely used but useful for generating combinations.
• Result: Every employee is paired with every department.

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

---

6. SELF JOIN

• Definition: Joins a table with itself, treating it as two separate tables.
• Use Case: Finding relationships within the same table (e.g., employee-manager hierarchy).
• Result: Matches employees with their managers.

SELECT A.name AS Employee, B.name AS Manager FROM employees A INNER JOIN employees B ON A.manager_id = B.id;