SQL Types of Joining

Create Two Table

1) Department Table

CREATE TABLE
    department (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(100)
    );

SELECT * from department;

2) Employee Table

CREATE TABLE
    employee(
        employee_id INT PRIMARY KEY,
        full_name VARCHAR(100),
        department_id INT,
        job_role VARCHAR(100),
        manager_id INT,
        FOREIGN KEY (department_id) REFERENCES department(department_id)
    );

SELECT * FROM employee;

We can join tables on six types.

• Inner Join
• Left Join
• Right Join
• Full Join
• Natural Join
• Cross Join

1 > Inner Join

SELECT *
FROM employee
    INNER JOIN department ON department.department_id = employee.department_id;

2 > Left Join

SELECT *
FROM employee
    LEFT JOIN department ON department.department_id = employee.department_id;

3 > Left Join

SELECT *
FROM employee
    RIGHT JOIN department ON department.department_id = employee.department_id;

4 > Full Join

SELECT *
FROM employee
    FULL JOIN department ON department.department_id = employee.department_id;

5 > Natural Join

SELECT * FROM employee NATURAL JOIN department;

6 > Natural Join

SELECT * FROM employee CROSS JOIN department;