MySQL JOIN - Combining Data from Multiple Tables

JOIN is an SQL command that allows combining data from two or more tables into one result. MySQL supports different types of JOIN commands:

INNER JOIN: Returns only records that match in both tables
1. Returns only rows where values match in both tables
2. Most commonly used type of JOIN
3. Example: List of employees with their departments
LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matching records from the right
1. Returns all records from the left table and matching records from the right
2. If no match, values from the right are NULL
3. Useful for displaying all records from the main table
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matching records from the left
1. Returns all records from the right table and matching records from the left
2. Works similar to LEFT JOIN, but keeps all records from the right table
3. Less commonly used, usually LEFT JOIN can be used instead
FULL JOIN: Returns all records from both tables (simulated with UNION in MySQL)

JOIN commands are used with ON or USING clause to specify join conditions.

Using JOIN commands:
1. Identify tables you want to join
2. Choose appropriate JOIN type based on needs
3. Define join condition with ON or USING
Best practices
1. Always use table aliases
2. Clearly define join conditions
3. Be careful with NULL values in outer joins

EXAMPLE

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    supervisor_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments VALUES (1, "IT");
INSERT INTO departments VALUES (2, "Marketing");
INSERT INTO departments VALUES (3, "Sales");
INSERT INTO departments VALUES (4, "Development");

INSERT INTO employees VALUES (1, "John Smith", 1, 5);
INSERT INTO employees VALUES (2, "Anna Brown", 1, 1);
INSERT INTO employees VALUES (3, "Mary Wilson", 2, 1);
INSERT INTO employees VALUES (4, "Peter King", 3, 3);
INSERT INTO employees VALUES (5, "Nina White", 2, 3);

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

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

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

SELECT e.name AS employee, d.department_name 
FROM employees e 
CROSS JOIN departments d;

SELECT e1.name AS employee, e2.name AS supervisor
FROM employees e1
LEFT JOIN employees e2 ON e1.supervisor_id = e2.employee_id
ORDER BY e1.name;

SELECT * FROM employees;
SELECT * FROM departments;

RESULT

In this example, we have two tables:
1. departments: contains data about departments (dept_id, dept_name)
2. employees: contains data about employees (emp_id, name, dept_id, manager_id)
The tables are linked via the dept_id field. Below is an example of an INNER JOIN:
```
SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id;            
```

INNER JOIN displays only those employees and departments where a match exists:

SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id;

LEFT JOIN displays all employees, even if they do not have a department assigned:

SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id;

RIGHT JOIN displays all departments, even those without any employees:

SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;

CROSS JOIN displays all possible combinations of employees and departments:

SELECT e.name AS employee, d.dept_name FROM employees e CROSS JOIN departments d;

Self JOIN on the employees table allows us to display managers:

SELECT e1.name AS employee, e2.name AS manager FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id ORDER BY e1.name;