4. FULL OUTER JOIN

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table. Records that do not match in either table are returned with NULL values.

Syntax:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Example:

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id;

Example Table Creation and Joins

1. Create the employees table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

2. Create the departments table:

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

3. Insert sample data into the employees table:

INSERT INTO employees (first_name, last_name, department_id)
VALUES 
('John', 'Doe', 1),
('Jane', 'Smith', 2),
('Alice', 'Johnson', 1),
('Bob', 'Brown', 3);

4. Insert sample data into the departments table:

INSERT INTO departments (department_name)
VALUES 
('Engineering'),
('Marketing'),
('HR');

Performing Joins

FULL OUTER JOIN Example:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;